Star-schema naming conventions

Question

Is it common practice in a star schema to prefix tables as a dimension or fact table? Is is 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 schema?

Thanks Dwight

Answer 1

The tablename_column name convention is used to ensure that all fields within a database are unique, although it is somewhat excessive it can be used for when there is a standard / requirement for unique naming (Which some client IT departments demand.)

Product.Name => Product.Product_Name
Part.Name => Part.Part_Name

It removes any ambiguity over where Name would come from.

I prefer not to name tables with a prefex at all (assuming that does not break the local standards of a company), since whilst it might be a table today, it could be re-implemented as a view or partitioned view tomorrow but expose the same schema, and I would then have to accept objects prefixed incorrectly or update everyones reference to the new name / create a synonym.

Having consistency though tends to be the winner, if every DBA / Dev implemented their own version it would be chaos, so I would tend to find the company standards and apply them.

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.

Answer 3

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.