locked
Basic Guideline when start designing the Database. RRS feed

  • Question

  • Hi,

    I am new to DB part and need to know the basic guidelines when design the databases for application .

    i am basiclyy working sql server 2008

    Wednesday, March 6, 2013 12:55 PM

Answers

  • Dr SQL (Louis Davidson) has many book on Relational Database Design (Bing - Louis Davidson) DrSQL.org. I see him responding to posts here as well.

    I received a lot of experience by reading articles on SQLServerCentral.com over many year.

    I have a 6 part blog about database design standards you can read - http://thesmilingdba.blogspot.com/2011/09/database-standards-links-to-past-blogs.html

    Thomas


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Wednesday, March 6, 2013 2:38 PM
  • In addition to the above, take a look at the sample tables at this page:

    http://www.sqlusa.com/bestpractices2005/bankdatabase/

    Also take a look the AdventureWorks2012 design & tables:

    http://msdn.microsoft.com/en-us/library/ms124825(v=sql.100).aspx

    SSMS has a diagram tool which is excellent for discovering the sample databases:

    The following design is based Northwind Orders & Order Details tables:

    CREATE TABLE OrderMaster(
    	OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    	CustomerID nchar(5) NULL REFERENCES Customers ,
    	EmployeeID int NULL REFERENCES Employees,
    	OrderDate datetime NULL,
    	RequiredDate datetime NULL,
    	ShippedDate datetime NULL,
    	ShipVia int NULL REFERENCES Shippers (ShipperID),
    	Freight money NULL default 0.0,
    	ShipName nvarchar(40) NULL,
    	ShipAddress nvarchar(60) NULL,
    	ShipCity nvarchar(15) NULL,
    	ShipRegion nvarchar(15) NULL,
    	ShipPostalCode nvarchar(10) NULL,
    	ShipCountry nvarchar(15) NULL,
    	ModifiedDate datetime default getdate(),
    	UNIQUE CLUSTERED (CustomerID, OrderDate) );
    
    GO
    
    
    
    CREATE TABLE OrderDetail(
    	OrderID int NOT NULL REFERENCES Orders,
    	ProductID int NOT NULL REFERENCES Products,
    	UnitPrice money NOT NULL  DEFAULT ((0.0)) CHECK  ((UnitPrice>=(0))),
    	Quantity smallint NOT NULL  DEFAULT ((0)) CHECK  ((Quantity>(0))),
    	Discount real NOT NULL  DEFAULT ((0.0))  CHECK  ((Discount>=(0) AND Discount<=(1))),
    	ModifiedDate datetime default getdate()
        PRIMARY KEY CLUSTERED (	OrderID ASC,	ProductID ASC));
    
    


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design



    • Edited by Kalman Toth Wednesday, March 6, 2013 4:07 PM
    • Marked as answer by Fanny Liu Thursday, March 14, 2013 7:06 AM
    Wednesday, March 6, 2013 3:51 PM

All replies

  • Dr SQL (Louis Davidson) has many book on Relational Database Design (Bing - Louis Davidson) DrSQL.org. I see him responding to posts here as well.

    I received a lot of experience by reading articles on SQLServerCentral.com over many year.

    I have a 6 part blog about database design standards you can read - http://thesmilingdba.blogspot.com/2011/09/database-standards-links-to-past-blogs.html

    Thomas


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Wednesday, March 6, 2013 2:38 PM
  • In addition to the above, take a look at the sample tables at this page:

    http://www.sqlusa.com/bestpractices2005/bankdatabase/

    Also take a look the AdventureWorks2012 design & tables:

    http://msdn.microsoft.com/en-us/library/ms124825(v=sql.100).aspx

    SSMS has a diagram tool which is excellent for discovering the sample databases:

    The following design is based Northwind Orders & Order Details tables:

    CREATE TABLE OrderMaster(
    	OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    	CustomerID nchar(5) NULL REFERENCES Customers ,
    	EmployeeID int NULL REFERENCES Employees,
    	OrderDate datetime NULL,
    	RequiredDate datetime NULL,
    	ShippedDate datetime NULL,
    	ShipVia int NULL REFERENCES Shippers (ShipperID),
    	Freight money NULL default 0.0,
    	ShipName nvarchar(40) NULL,
    	ShipAddress nvarchar(60) NULL,
    	ShipCity nvarchar(15) NULL,
    	ShipRegion nvarchar(15) NULL,
    	ShipPostalCode nvarchar(10) NULL,
    	ShipCountry nvarchar(15) NULL,
    	ModifiedDate datetime default getdate(),
    	UNIQUE CLUSTERED (CustomerID, OrderDate) );
    
    GO
    
    
    
    CREATE TABLE OrderDetail(
    	OrderID int NOT NULL REFERENCES Orders,
    	ProductID int NOT NULL REFERENCES Products,
    	UnitPrice money NOT NULL  DEFAULT ((0.0)) CHECK  ((UnitPrice>=(0))),
    	Quantity smallint NOT NULL  DEFAULT ((0)) CHECK  ((Quantity>(0))),
    	Discount real NOT NULL  DEFAULT ((0.0))  CHECK  ((Discount>=(0) AND Discount<=(1))),
    	ModifiedDate datetime default getdate()
        PRIMARY KEY CLUSTERED (	OrderID ASC,	ProductID ASC));
    
    


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design



    • Edited by Kalman Toth Wednesday, March 6, 2013 4:07 PM
    • Marked as answer by Fanny Liu Thursday, March 14, 2013 7:06 AM
    Wednesday, March 6, 2013 3:51 PM