Database Design: simplifying many to many

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        |开发者_如何学C 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?)

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

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.

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.

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