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.