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
I don't know about star schema, but in our OLTP we prefix columns with (abbreviation of) table name.
All our column names are unique (within the database)
All our application variables (including @Paramas to Sprocs) use same name as column they refer to (not all @Params, nor application variables, refer to a column, of course!)
Global Find & Replace when column properties, or name, change is made much easier.
Some "Hungarian notation" style visual validation is created:
FROM Table1
JOIN Table2
ON tab2_tab1_ID = tab1_ID -- Visible validation is OK
FROM Table1
JOIN Table2
ON tab2_ID = tab1_ID -- Visible validation is **not** OK
AND tab3_Col = tab1_Col -- Also "illogical"
In the second example the ID local to Table2 is being joined to the ID local to Table1, rather than the referencing key
But given that you already have a naming convention for your OLTP I doubt that is much help, sorry!
