Database Design Question 1

[litton1]

Firstly I apologize if this is so simple that I should know but it is a recurring design flow that is seen in many databases and I don’t want this one to be the same…

Table1 has a field that is a possible link to Table2 but Table1 will not always need/have the FK from Table2 and therefore cannot have a foreign key constraint. How should this be handled?

My thoughts… have a Boolean (bit) that could be true/false and tested, but if this is correct then what, do I include the FK or not?
Any help appreciated.


Age is a consequence of experience

 

[LNBruno]

What about setting up a relational 3rd table that contains the PK from Table A and the PK from Table B?

< M!ke >
I am not a hamster and life is not a wheel.

 

[litton1]

Hi thanks for your reply,
I wondered myself if that would work… So if the bool value was true I could then look for the PK in the 3rd table (is that what you meant?).

Age is a consequence of experience

 

[LNBruno]

No, not exactly. No bit on TableA. Just search TableC for the value(s) you want:

TableA
PKid
SomeDescription
OtherColumns

TableB
PKid
SomeDescription
OtherColumns

TableC
PKidA
PKidB

Does that make sense?

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLSister]

If your database supports triggers, you can put a trigger on the table that will not insert the record unless the record exists in the first table. Data integrity issues should be handled in constraints or triggers as much as possible.

Questions about posting. See faq183-874

 

[litton1]

Yes that makes sense, not sure how I would write the sql though (that’s for another day anyhow), thanks both of you.

Maybe I just need to explain the tables a bit to ensure I have the answers I need… two examples:

There is a table called TestTracker. This table can have 0-n number of notes attached should i do as above? and also
how do I ensure others can understand the table structure if I don’t show a direct relationship… There is no FK.

TestTracker can also have tests in it that can be from a DB or software. If it is a DB test then there is no entry but it is software then the TestTracker is linked to the software table, as in this test was performed on this software on this version (version is linked to software).

Thanks again.


Age is a consequence of experience

 

[litton1]

TestTracker can also have tests in it that can be from a DB or software. If it is a DB test then there is no entry but it is software then the TestTracker is linked to the software table, as in this test was performed on this software on this version (version is linked to software).

solved this part... Databases will all have a version from now.

The notes I have done as LNBruno suggested Thx again.

Age is a consequence of experience