3

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: enter image description here

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?

Improve this question
3
  • I'm not clear on your distinction between Property and Classifier. Commented Jul 23, 2014 at 20:20
  • I've updated the question with an example. Thanks for looking! Commented Jul 23, 2014 at 21:08
  • This is a variation on the Entity Attributed Value (EAV) model. There are heaps of posts out there on both sides of this topic. My experience is that it can work, as long as you define "work" appropriately. Commented Jul 24, 2014 at 10:02

4 Answers 4

Reset to default
2

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

Improve this answer
1
  • Thank you for your input. I've checked the article and I think they're not really being fair. Basically what they're saying is "it's much easier to get data from columns than have a N-N relation". For example, how would it be easier to get all persons with pet cat OR hobby TV if these are stored in separate tables vs. the proposed structure? Also, what benefits of relation DBMS am I missing? It seems that structural integrity is maintained and no redundant data is stored. Commented Jul 24, 2014 at 11:53
3

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.

Improve this answer
1
  • You're right, this design does have issues with ORM. Thanks! Commented Jul 24, 2014 at 11:50
0

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.)

Improve this answer
0

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.

Improve this answer
2
  • Thanks. As you see, the post is 2 years old, and since then I've learned to avoid EAV model if possible. Commented Jun 15, 2016 at 12:51
  • Ha - didn't notice the date Commented Jun 15, 2016 at 21:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.