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