0

If a DB is designed as:

Table A
a (type x)
a is (PK)

Table B
a (type x)
a is (PK)

Table C
a (type x)
a is (FK) references A(a)
a is (FK) references B(a)

Is this correct design? If not, what's the best design for this case?

Welbog
  • 59,154
  • 9
  • 110
  • 123
Moro
  • 2,088
  • 6
  • 25
  • 34
  • Could you elaborate on what you are trying to accomplish with this design, maybe even include what kind of DBMS you are using? –  Mar 20 '09 at 14:11
  • Do you mean that table C has a single field which is an FK to both A and B? – j_random_hacker Mar 20 '09 at 15:01

4 Answers4

1

So, you want only be able to insert a row in Table C when a corresponding row exists in Table B and A. The rows must have the same id value. Why not merging Table A and B. Splitting cause of too much columns? Either do that or remove fk on table B in table C and add a reference to Table A in Table B. Though I'm not entirely sure what you want to achieve wit this design.

Sascha
  • 10,231
  • 4
  • 41
  • 65
1

I believe the situation you are referring to is where C could be a child of A or B.

for example

if A is pages

B is pictures

and C is comments

C could be a comment for a picture or a comment for a page.

in this case I would adjust C to be a dual key as follows

Table C:
a (type x)
b (type CHAR) //<- defines which table a is the key of
INDEX(b,a)

SQL = SELECT * from C where b = TABLE_A_name and C.a = A.a 
or
SQL = SELECT * from C where b = TABLE_B_name AND C.a = B.a

You'll then have to impliment data integrity from the application side.

So it's fuzzy design in terms of foreign key implementation and data integrity. The question is if the application functionality is worth the chaos and if it will be in a well managed environment.

Fire Crow
  • 7,499
  • 4
  • 36
  • 35
  • +0. That *might* be what he/she's trying to do, but how would we know? :/ – j_random_hacker Mar 21 '09 at 06:45
  • If I waited to know in everything situation I'd never do anything. This particular technique has done wonders for me so I'm sharing what could help.and be honest, it could be what he/she is going for. – Fire Crow Mar 21 '09 at 14:45
-1

That is what is called a Star Schema Design which is a correct design for a set of problems. Unless you define your problem can't tell you if it is a good design or not for the problem.

Community
  • 1
  • 1
Robert MacLean
  • 38,975
  • 25
  • 98
  • 152
-1

looks like you have a ternary relationship. Without knowing what A, B, and C are it's impossible to tell if you have the "correct" design.

JMM
  • 536
  • 2
  • 8
  • -1. Not quite, a ternary (many-to-many) relationship would require C to have 2 separate fields, one a FK to A and the other a FK to B. In the asker's design there is only a single field with FKs to both A and B. – j_random_hacker Mar 20 '09 at 15:05
  • oops, you're right, I miss-read the schema. I thought table C had 3 field. – JMM Mar 20 '09 at 16:23
  • @JMM: Easy to do. I can't think what the asker's actually trying to do with this arrangement... – j_random_hacker Mar 21 '09 at 06:46