Star-schema naming conventions

Question

Is it common practice in a star schema to prefix table names as a dimension or fact table? Is it also common practice to have column names prefixed with the table name?

In my normal OLTP databases, I don't do this, but I'm seeing examples of this type of naming in star schemas.

Does it make sense to have a different set of naming standards for data warehouse schemas vs OLTP schemas?

Thanks Dwight

Answer 1

Table Names:

• I like this convention: [type][subject][name]
• where type is 'dim' or 'fact' (or 'facts' for aggregates)
• where subject is the subject area within the warehouse ('comm' for common, 'fw' for firewall, 'ids', etc)
• where name is ideally a single word name, or abbreviations of dimensions in the case of an aggregate table
• ex: dim_comm_org for the organizational dimension
• ex: fact_scan for the scan fact table
• ex: facts_scan_org_sev_daily - fact scan summary table grouped at the org, sev & day level

Column Names:

• don't prefix with the entire table name - that's way too long
• do prefix with just a meaningful part of it - this helps tremendously when writing or reading queries.

Warehouse vs OLTP Naming:

• the two are very different. Warehouse table & column names often end up in metadata, on reports, being read by both developers and users. Not so much with OLTP.
• I think table prefixes are still useful in OLTP - but there I think it's best if it's something meaningful about that subset of the model rather than a fact/dimension distinction.

Answer 2

It is common in DWs to name columns with "long names" because those columns end up as column headers in reports (query results) and are supposed to be business-user friendly. So instead of having Product.Name and Customer.Name which would both show up as "Name" (unless alias is used) it is common to use Product.ProductName and Customer.CustomerName so they show up as "ProductName" and "CustomerName" in top row of a report (query) once the star is flattened via joins. Underscores are frequently used instead of camel-case and blanks, if allowed by the DB. Prefixes dim and fact are recommended in large DWs when table's role in the schema may not be obvious; I actually like them.