Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
racing_couple
ID (PRIMARY)

breeding_couple
ID (PRIMARY)

egg
IDpar(FOREIGN KEY)

I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) I have IDpar which is foreign key and references ID in racing_couple and breeding_couple. Tables represents racing and breeding couple of pigeons, table "egg" represents egg of racing or breeding couple. And I'm using IDpar to identify which egg belongs to which couple.

Note There are other fields in both database but they are mostly varchar and not so relevant to this problem.

If I have something like this in both databases. How to know which IDpar has value from racing_couple and which IDpar has value from breeding_couple. I think I made mistake by making my database like this, but is there any way to make that work?

ID(racing_couple)
1
2

ID(breeding_couple)
1
2

IDpar(egg)
1 (ID racing_couple)
1 (ID breeding_couple)
2 (ID racing_couple)
2 (ID breeding_couple)
share|improve this question
    
You say "other fields in both database" / "something like this in both databases" - which tables are where? –  Thomas Rushton Jul 6 '13 at 19:01
    
See the answer by @gbn here: NULLs in a composite primary key - SQL Server Replace Cat and Dog with racing_couple and breeding_couple and for Pet you'll have to add a new couple table. Then the foreign key from egg would be to that couple table. –  ypercube Jul 6 '13 at 22:35
add comment

2 Answers

I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
              |
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.

share|improve this answer
add comment

Yes. Mistake.

One way of making this design work is to have the ID ranges not overlap - eg, have the Racing couples IDs start at 10000 (assuming you'll never get anywhere near that with the Breeding Couple IDs, of course)

Alternatively, can you add a flag to the egg table to indicate which couple table to use?

share|improve this answer
    
How will you define the foreign keys? –  ypercube Jul 6 '13 at 21:25
    
Good question, and one that I see the OP did ask and I didn't answer. TBH: I wouldn't be attempting this anyway. Not like this. I would be in SQL Server, and (if I had to do it with this schema) I would probably end up enforcing integrity using triggers. Or check constraints. –  Thomas Rushton Jul 6 '13 at 21:32
    
I found solution, thank you –  FosAvance Jul 6 '13 at 22:33
2  
Could you share it with us? –  hmayag Jul 6 '13 at 22:41
    
Sure. The best way to correct that is to make 2 new columns in "egg" table. first column for ID from "racing_couple" and other column for "breeding_couple" so it can't mix –  FosAvance Jul 7 '13 at 12:18
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.