sql - One column maps to mutiple columns from different table -

just wondering if there problem in design or has ever broke rules in database normalization.

the major table has column mapped two(or more) different tables, data in reference column may mix primary key multiple tables.

is better design create different association tables (major -> , major -> b)?

table major

item id | reference -----------------------  id 1  |  a1 pk tb  id 2  |  b1 pk tb b  id 3  |  a2 pk tb  id 4  |  b2 pk tb b  id 5  |  a3 pk tb  id 6  |  a4 pk tb  id 7  |  b3 pk tb b  id 8  |  b4 pk tb b  ...... 

table a

sub item id |  -----------------------  id a1  |  .....  id a2  |  .....  id a3  |  .....  id a4  |  .....  ...... 

table b

sub item id |  -----------------------  id b1  |  .....  id b2  |  .....  id b3  |  .....  id b4  |  .....  ...... 

your table major violates boyce-codd normal form (3nf+), because don't know table being referenced key. it's semantic difference, , each column should describe 1 thing (and in case, context differs because each key goes different table).

since have table & b "sub item", want have tables & b reference table major's primary key parent, depending upon other content in there. you've got parent/child relationship inverted: parent (major) doesn't have refer child tables (a & b), the child references parent pk, , ideally enforced foreign key. can select child , join fetch related records parent, without selecting irrelevant records other child , having filter them out in clause.

so, might follows:

table major:

item id | [more columns] -----------------------  1  |  [foo]  2  |  [bar] .... 

table a:

sub item id | majorid (fk table major) | [other columns] ----------------------- id 1  | 1 (pk table major) | ..... .... 

table b:

sub item id | majorid (fk table major) | [other columns] ----------------------- id 1  | 2 (pk table major) | ..... .... 

then can select b's using query, , not have worry a's:

select *  [table b] b inner join   [table major] maj on b.majorid = maj.id 

what differentiates table table b? if have same content, might need add column distinguish them 1 sub types/sub items?