One column maps to mutiple columns from different table

Question

Just wondering if there is any problem in this design or has it ever broke any rules in database normalization.

The major table has a column that mapped to two(or even more) different tables, so data in reference column may mix with primary key from multiple tables.

is it a better design to create different association tables (major -> A and major -> B)?

Table Major

item id | reference
-----------------------
 id 1  |  a1 pk from tb A
 id 2  |  b1 pk from tb B
 id 3  |  a2 pk from tb A
 id 4  |  b2 pk from tb B
 id 5  |  a3 pk from tb A
 id 6  |  a4 pk from tb A
 id 7  |  b3 pk from tb B
 id 8  |  b4 pk from 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  |  .....
 ......

Answer 1

As long as you keep the number of tables you are referencing to a minimum it isn't too bad of an idea, in the Table Major you would want to add an extra field that would identify which table it belonged to.

It depends on how central this Table Major would be to your project. If its at the center of your universe, I would say maybe (leaning more towards no), if not then you would probably be fine.

ORMs do what you are suggesting, but I would look at other options before implementing it.

Answer 2

Your Table Major violates Boyce-Codd Normal Form (3NF+), because you don't know which table is being referenced by your key. It's a semantic difference, and each column should describe exactly one thing (and in this case, the context differs because each key goes to a different table).

Since you have Table A & B as "sub item", you want to have Tables A & B reference Table Major's primary key as their parent, depending upon what other content is in there. You've got the parent/child relationship inverted: a parent (Major) doesn't have to refer to its child tables (A & B), the child references the parent by its PK, and this is ideally enforced by a foreign key. Then you can just select from the child and join to fetch all related records from the parent, without selecting irrelevant records from the other child and having to filter them out in your where clause.

So, they might look as follows:

Table Major:

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

Table A:

sub item id | MajorID (FK to Table Major) | [other columns]
-----------------------
id 1  | 1 (PK from Table Major) | .....
....

Table B:

sub item id | MajorID (FK to Table Major) | [other columns]
-----------------------
id 1  | 2 (PK from Table Major) | .....
....

Then you can select all B's using this query, and not have to worry about A's:

SELECT *
FROM  [Table B] as b INNER JOIN
  [Table Major] as maj ON b.MajorID = maj.ID

What differentiates Table A from Table B? If they have the same content, you might just need to add a column to distinguish them from one another as sub types/sub items?

Answer 3

I don't know what are you trying to do. In my opinion there is no point in doing that. If you want to make a relation between 2 other tables indeed you could create a table with both the keys of those table that define that relation and you can generalize by adding two other fields that identifies the tables in the relationship. But the table described (Major - with the mixed keys per column) can be obtained with a simple union.

Answer 4

I'd define separate fields to refer to tables A and B, as in:

TABLE MAJOR
  ID    | REFERENCE_A | REFERENCE_B
  id 1  | a1          | NULL
  id 2  | NULL        | b1
  id 3  | a2          | NULL
  id 4  | NULL        | b2
  id 5  | a3          | NULL
  id 6  | a4          | NULL
  id 7  | NULL        | b3 pk from tb B
  id 8  | NULL        | b4 pk from tb B

Done this way it's easier to understand what each field refers to, and you can define foreign key relationships between table MAJOR and the other tables to help ensure that your data is consistent. It also allows MAJOR to refer to both an A and a B, if needed - and if this isn't needed a simple CHECK constraint can be used to ensure that at most one of (REFERENCE_A, REFERENCE_B) are not NULL.