Take the 2-minute tour ×

Say I have the following tables:

  • person (person_id, name)
  • ethnicity (ethnicity_id, name)
  • person_ethnicity (person_id, ethnicity_id)

This would allow me to define a person to have 0 or more ethnicity AND an ethnicity to have 0 or more person through the person_ethnicity table.

Now, let's say I have a LOT of these "ethnicity" type tables where I have to make the same many to many relationship with the person table. The number of my tables are going to grow pretty fast.

Is it a good idea to have a table like this instead:

  • foo (person_id, other_table_name, other_table_pk)

An example:

| person_id | other_table_name | other_table_pk |
| 1         | ethnicity        | 1              |

I lose referencial integrity this way, but would make modeling much easier I think. Is this approach a good idea or a horrible, horrible idea?

(Also, is there a proper name for the approach I described above?)

share|improve this question
Are you planning on having multiple other relationships to a person other than ethnicity? I ask because of your second solution seems like you plan on having multiple tables/ids united. –  Khez Apr 9 '11 at 12:09
@Khez - yes, as described in my initial post. –  StackOverflowNewbie Apr 9 '11 at 12:12
You described one person and one relationship ethnicity. Yet your second solution implies having other relationships, like nationality. If this is not the case, your second solution is a very bad idea then. –  Khez Apr 9 '11 at 12:14
@Khez - I also described that I have a LOT of these ethnicity type tables that I need to model. –  StackOverflowNewbie Apr 9 '11 at 12:20
I wasn't the only one to miss it... I blame internet ghosts. –  Khez Apr 9 '11 at 12:23
show 1 more comment

4 Answers

up vote 1 down vote accepted

I see no need. A lot of tables is no trouble, and you're breaking a lot of 'rules' by doing it like this. Just go with the many-to-many, if you need them.

Using it this way you should do all sorts of tricky things. Also, you can't do anything with foreign keys (constraints), and tons of other problems. And for what? "less tables". I don't see any advantage in that :D

Just don't would be my advice :D

share|improve this answer
add comment

Your solution would pretty fast blow up your "foo" table.

imagine you have 1000 persons. then every person has a minimum of 2 ethnicities.

then you have a nationality table and each person also has at least 1.5 nationalities.

then a gender where every person has a minimum of 1 gender what already sums up to 4500 entries.

now lets say your persons table grows to about 50000 persons.

this will make already 225000 entries in your "foo" table.

now that you have your tables filled up you make a query joining persons with foo and only ethnicity to get the ethnicities of all persons and your server will work verry verry long because you join a 50000 table with a 225000 table and in the end join a small table.

so i hope i made it clear why it is no good idea to make such a layout

share|improve this answer
add comment

Since you're asking the question from a theoretical point of view, I'll give you an answer in the same regard.

Your situation is very similar to a tag system. And lucky for you the MySQL Community offers an excellent wiki article about TagSchema via Forge.

Also you should consider searching SO for similar questions, since it's been asked and asked and asked. Some of them actually provide interesting insight into the matter. Especially What tag schema(s) are the most efficient/effective? and the response on making Collection Tags that hold Tag Sets.

share|improve this answer
add comment

If you have lots of many-to-many relationships in your model, like your ethnicity example, you have no choice but to model them properly the way the relational idiom requires.

I don't think your design is a good idea. Binding to table and column names isn't done in Java; I don't know about other languages.

Get a shovel; get to work. Model your problem as it exists using the relational idiom or find something else. Maybe a NoSQL solution like an object or graph database is a better idea in your case.

share|improve this answer
I won't have many ethnicity tables. I have many ethnicity-like tables (meaning, requiring many to many relationship with person table). I don't understand your reference to Java since I am asking the question from a data modeling perspective. –  StackOverflowNewbie Apr 9 '11 at 12:14
@StackOverflowNewbie ... your initial post didn't imply this, your solution did. –  Khez Apr 9 '11 at 12:15
Sorry, missed the "ethnicity-like" bit. I'll amend. And I understand that this is about relational modeling, but eventually I'm assuming that you'll write an application that will have to access your schema. When you do, you'll have to deal with the language of choice. AFAIK, you don't bind table and column names, only values. –  duffymo Apr 9 '11 at 12:16
add comment

Your Answer


