×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Database Design Question

Database Design Question

Database Design Question

(OP)
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

RE: Database Design Question

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.

RE: Database Design Question

(OP)
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

RE: Database Design Question

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

CODE

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.

RE: Database Design Question

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: Suggestions for Getting Quick and Appropriate Answers

RE: Database Design Question

(OP)
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

RE: Database Design Question

(OP)

Quote:


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close