CODEWITHSUNDEEP
phpmysqldatabase-design
user1031947
user1031947

Reputation: 6674

Using a single ID column to reference multiple tables

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

Upvotes: 1

Views: 312

Answers (2)

Jhonathan
Jhonathan

Reputation: 1601

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)

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

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.

Upvotes: 3

Related Questions