Database design issue

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

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.