Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

recently I have noticed a new approach to data modeling vs what I have used so far. This seems to be a way to address modeling of an entity that has a very large set of fields and quite often many of them are empty.

First approach - the one I used previously:

  • Main table for the main set of fields (a,b=1,c=2)
  • Additional table with a subset of fields (a,e='foo',f='bar',...) - linked to Main one by a 1-1 relation
  • Yet another Additional table with a subset of fields (a,j=3,k='baz',...) - linked to Main one by a 1-1 relation
  • And one more with the rest of the fields (a,n=12.4, o='foobarbar', ...) - linked to main one by a 1-1 relation

The one I have seen recently is:

  • Main table with almost no data except for an ID (a)
  • Table that lists all the fields possible (b,'this is parameter b')
  • a table that links the Main one with all the fields it has (a,b,1) and (a,c,2) and (a,e,'foo') and so on.

I understand you can query same results from both models, but I do not understand what is the rationale for the second one - are there clear cases where one of those should be used vs other? Thanks for your advice.

PS. The second one is being used on Oracle 11 - my last real life experiences with SQL were in the times of Postgres 7.x might be things changed meanwhile :)

share|improve this question
2  
Your second approach is called EAV Enter the term in google for exhaustive discussion of the EAV design model. –  Andomar Dec 14 '11 at 20:02
1  
en.wikipedia.org/wiki/Entity-attribute-value_model it is. Thank you! –  Piotr Dec 14 '11 at 20:13
    
one more thing - to query this model efficiently for all attributes for entity 'a' I return all rows for the entity I look for and manage this in code rather than using a lot of joins, right? –  Piotr Dec 14 '11 at 20:29
    
EAV tables are not an Oracle specific concept. It's just a much more highly normalised way of representing the data - with many benefits and issues. As in the other comments, there is much on-line that discusses EAV. It's something that is initially very attractive to generalists, but it really does have its costs too. –  MatBailie Dec 14 '11 at 20:37
    
Here's a case study of an EAV implementation: simple-talk.com/opinion/opinion-pieces/bad-carma –  Mark Bannister Dec 15 '11 at 13:35
add comment

migrated from stackoverflow.com Dec 15 '11 at 12:07

This question came from our site for professional and enthusiast programmers.

2 Answers

The second format you're outlining is a fairly common Data Warehouse strategy.

It's used to de-normalise data when you have an extremely large (or complex) set that needs to be stored over a long period of time, during which there may be a number of schema changes, or if you want to warehouse an entire schema into a much smaller set of tables.

In the wild it's a fairly rare to come across during application development as it is extremely slow to query compared to a standard relational model, you don't have the luxury of indexes or the query optimiser so it becomes difficult to select a sub-set of data. This is obviously only a problem if query speed is a primary concern, so certain domains will benefit massively from a de-normalised data format (medical record storage and insurance company data are two examples I can think of off the top of my head).

If I were dealing with a sparse-matrix-style dataset (which I don't often have to at my current job, but have done in the past) I'd probably go with the google-style BigTable if you're aware of the possible column set before the DDL is created, or can do column additions as a long-running batch during application downtime. That way you still have the ability to index and query effectively, which, given that you've said this is Oracle related, you really want to be able to do!

share|improve this answer
    
It's fairly rare common? –  Mike Partridge Dec 15 '11 at 13:14
1  
-1 @Mike Woodcock: Either you have misunderstood the question, or you have seen some pretty weird datawarehouses - since you talk about denormalisation, I think it's the former. The OP is not talking about denormalisation, but about EAVs. –  Mark Bannister Dec 15 '11 at 13:33
    
@Mark apparently you've misunderstood my name. Plus, the pattern the OP outlined is a form of de-normalisation, and I work with an application that has the exact pattern used in its DW for the reasons outlined. –  Ed Woodcock Dec 15 '11 at 13:51
    
@Mike, yeah, that was a fairly bad typo! I'll edit. –  Ed Woodcock Dec 15 '11 at 13:52
    
@Ed, yes, I mangled your name, but no, it's not a form of denormalisation - EAV is pretty close to a mangled form of 6NF. –  Mark Bannister Dec 15 '11 at 13:54
show 3 more comments

If I am reading it right the second format looks like something you would use if you don't know what are all the fields for the main entity. This would be the case if you want the user to specify properties of the main entity.

share|improve this answer
    
Extendible numbers of properties is definitely one property. But it also removes the need for a NULL value - Simply don't have the value in the EAV table. There are many more properties (positive and negative) than a SO answer (or comment) could list, it really is something to check on the web rather than here. –  MatBailie Dec 14 '11 at 20:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.