"Mutually exclusive" relationships

[Niek]

Hello,

I am having the following situation:
* a table tblType1Deals with info on "type 1 deals". The primary key of this table is Type1id
* a table tblType2Deals with info on "type 2 deals". The primary key of this table is Type2id
* A deal can either be type 1 or type 2 (but not both), but both type 1 and type 2 deals have "subdeals". Therefore, I have a table tblSubdeals, with (among other fields) on field linking to tblType1Deals through Type1id and one field linking to tblType2Deals through Type2id

The problem is that I want to enforce somehow that a subdeal can only be part of / linked to a Type 1 deal OR a Type 2 deal, and never to both. I guess I could enforce this with some basic programming in the input forms, but this does not really seem like an elegant solution and it seems bound to cause trouble sooner or later. Anyone any suggestions as to how to solve this in the best way?

Many thanks in advance!

 

[plog]

My guess is you need to merge tblType1Deals and tblType2Deals into one table: tblDeals that has a field called 'DealType'.

Whats the structure of the 3 tables you've mentioned?

 

[Niek]

I've thought about this, but Type 1 and Type 2 deals have entirely different properties, so that would result in a lot of empty fields. I guess this is not really desirable/optimal...

 

[Pat Hartman]

You need a Deal table that has a type code. It will contain all the common attributes. Then you would have two tables that contain the specific attributes for each deal type. These tables are related 1-1 with tblDeal. The subdeal table is related 1-m with the tblDeal so DealID is stored in the subdeal table. This schema lets a subdeal belong only to a single deal.

If a subdeal can actually be related to multiple deals, you will need a junction table. You will also need to keep the deal type in the subdeal table and create the relationship to include DealType as well as DealID. That way you will be able to relate to multiple deals but they all must be the same dealtype defined in the subdeal table.

 

[Niek]

Perfect, thank you very much!