Keeping track of table structures, its all in the name!

When developing table structures for datawarhousing, datamarts or any other purpose where you need an relational data model, it is often that we lose track of the combinations that are made. Especially when the number of tables in our database grows. To manage this problem we must pay attention naming the tables, views, cubes, etc.

First give your table the name of the project you are working on. For example: I am working on a product database project where I manage my production. I start with prefix prd_ or even better, I create a scheme called "Production". Then I describe the table purpose and the data it contains. In the productcase i'm loading data about products to my datawarehouse (DWH), so I use the following name: "Production.Product".

My attributes get similar names using short prefix which identifies the attribute.

Last I identify my foreign key and give it the name of the foreign table and then the name, so when I’m in a visual program working with the data, I still know which relations there are. Also it improves the management of the database and its applications. See the following example:

CREATE TABLE [Production].[DWH_Product](

[pk_Product_ID] [int] IDENTITY(1,1) NOT NULL,

[Product_Name] [dbo].[Name] NOT NULL,

[Product_Number] [nvarchar](25) NOT NULL,

[Product_SubcategoryID] [int] NULL,

[Product_ModifiedDate] [datetime] NOT NULL

)

CREATE TABLE [Production].[DHW_Productcategory](

[pk_ProductSubcategory_ID] [int] IDENTITY(1,1) NOT NULL,

[fk_DWH_Product_ProductID] [int] NOT NULL,

[Productcategory_Name] [dbo].[Name] NOT NULL,

[Productcategory_ModifiedDate] [datetime] NOT NULL

)