Term SCD - can it be used for normalized dimensions?

Hi,

I am familiar with the term SCD and that there are three types in general. We are defining what stereotypes of tables exist in either 3NF model or star schemas and a colleague of us is using SCD stereotypes also for tables in normalized model. Is that OK?

Can a normalized dimension in 3NF model be called SCD? I have been thinking that the term SCD is relevant only for denormalized dimensions in star schemas.

Thanks,
Marian

If the question is about terminology, there is no such thing as a dimension in ER modeling. As such there would also be no such thing as as SCD.

If you are attempting a dimensional model and are thinking about normalization, you are not doing dimensional modeling. If you are attempting an 3NF ER model and are thinking about dimensions, you are not doing ER modeling. The language of ER modeling is keys, entities, attributes, tables, and columns. The language of dimensional modeling is dimension, facts, attributes, and measures. They mean different things.

It's the dreaded hybrid dimensional ER model! Kimball and Inmon all mashed up into the same database. As Ngalemmo said, it is not OK. If you have effective and end dates in your ER model, you are simply tracking history within the one table as opposed to a separate history table. That doesn't make it a dimension though. Typical types of entities in the ER model are domain entities (code tables), associative entities, independent entities, dependent entities. I've never used the term stereotype in ER modeling, so I may be a little confused as to what you mean.

thanks a lot.. I was also thinking that neither SCD nor dimensions are relevant for 3NF models!! .. thanks for confirmation! ..

in general, there are these types of tables in 3NF: lookup tables, primary entities (reference tables), secondary entities (relational tables), event tables .. and in dimensional model: dimensions (SCD1,2,3) and fact tables (transactional, periodic snapshot, accumulative snapshot) ..

Technically, in an ER model, tables do not have 'types'.  How a table is used has no bearing on how it is modeled.  Every table is the same in the eyes of normalization rules.  It is a matter of the key and the relationship of the columns in the table to the key.

Classifying tables is purely a means of communicating intent to developers (if they bother to listen).  It is not a part of ER modeling.

For example, you are creating a 3NF model and the table includes an address.  For the address you have a state code and the name of the state.  Since the name of the state is dependent on the state code, normalization rules require you create a state table with the state code as the key and the name of the state as a dependent column.  In the address table, the state code would be a foreign key to the state table.  It is simply another table that is necessary to achieve 3NF. However, there is a term 'associative entity' used to refer to an entity that contains only foreign keys.

Dimensional modeling, on the other hand, does not use normalization.  Where you put the state name is a judgement call.  To avoid snowflaking, you would usually include it in the dimension table with the address, unless there is some other need to have a state dimension.

Agreed. I don't think of types in ER modeling. I think more of patterns that are common in ER models. Dave Hay wrote a book on it and most ER modeling training contains patterns, including mine. Common patterns are domain, many-to-many, role based relationships, hierarchies, and network patterns.