question

footndale avatar image
footndale asked

Star schema naming conventions

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

databasenaming-convention
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered

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!

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.