linking to one table OR another

[jeffh]

Hello,



I'm trying to figure out the best way to set up tables to handle a one-or-the-other relationship. I have a table of Inventory items. I have a table of Purchase Orders and a table of Leases. So each Inventory item is related to EITHER a PO or Lease, under a 1-to-many relationship from the PO or Lease to Inventory (one PO could be related to many items). Each PO or Lease has a contract associated with it that I'd like to be able to link to Inventory items via query.

So, I'm trying to find best way to set up relationship of these 3 tables in best way which hopefully forces/guides a one-or-the-other relationship between the individual item and either a PO or Lease, but hopefully prevent linking to both, which would be a fault (meaning an item cannot be both on PO and Lease). I thought an intermediate table might be best, but I don't have it square in my head..

Any suggestions?

[CJ_London]

From your description, your inventory table should have both POFK and LeaseFK fields. You would then set business rules in your form so that only one can be populated. e.g. you might disable the POFK field in the afterupdate event of the LeaseFK field and visa versa.

[June7]

Are PO and Lease fields really so different need separate tables? Or one table with a field for PO/Lease indicator?

What are you selling/leasing?

[jeffh]

Thanks. I suspected I might need to implement some business rules as a possible path. This works.

[jeffh]

Thanks June7. I already had a the two separate tables, and it seemed to make sense to keep the PO's in a separate listing. I might integrate with a discriminator field as you suggest if current path gets too tangled.