2

Is there a name for a type of Star Schema in which there is a single Fact table which has a single column for value, and the type of value (the measure) is defined by a dimension?

In other words, a table like this:

Dim1ID     Dim2ID       MeasureID      Value
---------  -----------  -------------  ------------
543        44           1              234.3
543        45           1              256.3
544        44           1              245.3
544        45           1              264.5
543        44           2              10
543        45           2              8
544        44           2              9
544        45           2              10

With one value column that represents different measures via a foreign key.

Is there a name for this pattern?

codeulike
  • 22,514
  • 29
  • 120
  • 167

3 Answers3

2

Entity-Attribute-Value Model perhaps?

Editorial: Some people consider this to be an anti-pattern (in SQL), though in Column Based stores, this is the Usual Behaviour (BigTable, Cassandra).

Gregg Lind
  • 20,690
  • 15
  • 67
  • 81
  • Thanks, EAV and also Open-Schema seems to be the names. Now I know the name, I can read about the theory : ) ... That wikipedia link is very good, also these two SO questions ... http://stackoverflow.com/questions/2854312/database-with-open-schema-good-or-bad-idea http://stackoverflow.com/questions/192892/performance-of-large-eav-open-schema-systems-on-sql-server – codeulike Nov 12 '10 at 07:52
0

Seems to me that, as MeasureID would reference a table listing all possible measures, you just have a star scheam with three dimensions where one dimension is named "Measure".

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Yeah I guess so, but reading around on star-schemas it seems the usual thing is to have foreign keys for dimensions but store each type of measure in its own column or table – codeulike Nov 12 '10 at 07:43
0

I would just call it a 4D table: three key attributes and one non-key. I don't think it needs a special name.

I worked with a nearly identical model some time ago. We had upwards of 8000 measures and several billion rows. In the DBMS we were using it was totally impractical (and unnecessary) to create tables with 1000s of columns. The "wide" row version would have had no values at all for most of the measures on most of its rows. So we would either have had to generate nulls or dummy values for data where none existed or we would have had to create hundreds of "narrower" tables with nearly arbitrary sets of measures in them. The "vertical" model makes far more sense and will play quite well with the right DBMS.

I disagree with the suggestion that your design is not properly normalized. As long as all the measures are of the same data type then it is a legitimate design and is in at least 5th Normal Form (assuming the dimension and measure identifiers form the key). An alternative design with lots of columns certainly wouldn't be normalized if it forced you to use nulls.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • I see that you can just think of 'MeasureID' as another attribute, but I think its significant that `MeasureID` changes the meaning of `value`, rather than just describing some dimension of it. For example, in the table above using DB-level validation or constraints on `value` would not be very easy because validation rules would depend on `MeasureID`. So I wondered if this pattern had a name (and hence I can go looking for theory about it....). Thanks for your comments. – codeulike Nov 12 '10 at 07:47
  • @codeulike: Good point about the complexity of constraints (and other logic) on different types of value. If you need to treat different measures very differently then that might be a good reason to have multiple columns / tables of measures. – nvogel Nov 12 '10 at 08:29