Let’s say I have a database of persons who have some properties. For the sake of this problem let’s say all of these properties relate as N-N.
I can either create two tables for each property (one for different values and one for cross-referencing them to the main object).
Or, I can make the following structure:
Basically having just one huge table of values.
Are there any advantages to this approach? Also, does this approach or pattern have a name that I can look up?
UPDATE:
For example, we make classifier pets with properties cat, dog and fish. So one person may have a cat and a dog (or one pet, or none). And we make a classifier hobby with values skiing, skating, football and TV. So Joe has properties cat, dog, and TV. Then cat and dog are pets and TV is hobby.
The question is, is it worthwhile to put this data using this diagram, or is it a better idea to just create pets, pets_xref, hobby, hobby_xref tables?
3
Unless you fully understand and accept the consequences, doing this is generally not advisable if it can be avoided.
People are often tempted to use this model. In theory you can create any database with just four tables: objects, properties, values, links. While this kind of generic data model is very flexible, it is also very inefficient to query, and any complex queries you have to write against it will be very ugly.
Basically, you are depriving yourself of many of the benefits the RDMS is designed to give you.
Here’s a post which discusses it in more detail: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:10678084117056
1
I have to follow Igby, this design you tend to do, is not very suggested. If you ever worked with OR mapper you can understand the problem even further.
Because on your design you would have “multiple entities” (in the mind context) behind “one n:n relationship”.
Rather do the work and build up a new table, relation table and the relation for every “property” (like you call it) of any entity.
You cannot integrate 1:n in your design either. Then you would create new tables for 1:n and none for n:n on the person entity, that is dirty.
1
This answer expands on Igby’s answer, with an example of the kind of query that can be constructed.
There are definitely trade-offs between the two approaches. However, keeping separate tables for different categories can be queried just as easily in SQL.
SELECT UNIQUE pid FROM
(SELECT UNIQUE hobbyref.personid AS pid FROM hobby, hobbyref
WHERE hobbyref.hobbyid = hobby.id AND hobby.type = 'tv')
UNION
(SELECT UNIQUE petref.personid AS pid FROM pet, petref
WHERE petref.petid = pet.id AND pet.type = 'cat'
)
(Pardon any clumsiness in the SQL. It could probably be expressed more concisely.)
You are basically inventing a database on top of a database. A relational database already have a system to represent properties for entities – it is called tables and columns. By creating your own system on top of this, you are introduction a layer of complexity and at the same time loosing many of the benefits of a relational database. For example this will be much slower than querying a regular database, and you loose all integrity constraints.
That said, they may be particular reasons for choosing such an architecture. For example, if you wants users to be able to dynamically add properties through a UI, it might be easier to achieve. But you should only do this if you really need it and are aware of the drawbacks.
2