Using a single ID column to reference multiple tables

Question

In MySQL, is it bad to have a table structure like the following...

Table stuffA
ID

Table stuffB
ID

Table superStuff
ID
type
stuffID

...where depending on the value of superStuff(type), superStuff(stuffID) could reference either stuffA(ID) or stuffB(ID) ?

Thanks, in advance, for your help

Why do u hav two tables StuffA,StuffB if they both are of same kind.. ? — Teja, Mar 17 '12 at 21:53

score 3 · Accepted Answer · edited May 23 '17 at 12:23

I think you mean the other way around:

Table stuffA
ID
superStuffID

Table stuffB
ID
superStuffID

Table superStuff
ID

Besides you don't need the type attribute because it is implicit when you query one table or the other.

NOTE: I'm assuming you also have other fields on the tables and that you've ommited them in order to make a minimal example. If this is not the case, probably you wouldn't need a hierachy there.

This answer might be helpful in understanding this.

score 2 · Answer 2 · answered Mar 17 '12 at 22:12

Your solution is correct if standing on Superstuff you want to know who is father of the row.

One option might be this, but do not meet proposed in your solution.

Considerations:

The id is unique Superstuff indicated throughout the database, on tables stuffB and stuffA the ID is the ID of Superstuff. Id on stuffB and stuffA are foreign key to Superstuff.

Table Superstuff
ID

Table stuffA
ID (exist on superStuff)

Table stuffB
ID (exist on superStuff)

Using a single ID column to reference multiple tables

2 Answers2