Star-schema naming conventions

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.

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.