DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to make developers love 3NF normalized databases?

As a DBA, define a view on each primary table where the foreign keys are expanded to names and descriptions to help developers with easy access to meaningful data.

Example the Product table has a column ProductTypeID that is a foreign key.

In the vProduct view instead of ProductTypeID, you put ProductTypeName from the ProductType table.

The result is that select * from vProduct will produce a readable output just like a denormalized table would. The convention is easy to remember: just place the v prefix in front of the table name.

A frequent complaint of the developers is too many joins in a 3NF database.

A database architect can address this issue by combining lookup tables into LookupEntity(LookupMaster) and Lookup(LookupDetail) tables.

As a DBA / Database Designer, it is your duty to educate developers. Point out that a properly designed database is critical to the success of business applications.

Normalized database designs are much easier to code against, and they make it much easier to accommodate the business requirements of the user community. Normalized database means high development productivity.

View example from AdventureWorks2008:

USE AdventureWorks2008

GO

 

CREATE VIEW Production.vProductAndDesc

AS

SELECT

    p.ProductID

    ,p.Name

    ,pm.Name AS ProductModel

    ,p.ProductNumber

    ,pmx.CultureID

    ,pd.Description

FROM Production.Product p

    INNER JOIN Production.ProductModel pm

    ON p.ProductModelID = pm.ProductModelID

    INNER JOIN Production.ProductModelProductDescriptionCulture pmx

    ON pm.ProductModelID = pmx.ProductModelID

    INNER JOIN Production.ProductDescription pd

    ON pmx.ProductDescriptionID = pd.ProductDescriptionID;

GO

-- Test view

SELECT TOP (5) * FROM Production.vProductAndDesc

ORDER BY NEWID()

GO

/* Partial results

PID   Name                    ProductModel      ProductNumber     CultureID

960   Touring-3000 Blue, 62   Touring-3000      BK-T18U-62        th   

940   HL Road Pedal           HL Road Pedal     PD-R853           en   

919   LL Mt Fr Silver, 48     LL Mountain Frame FR-M21S-48        zh-cht

719   HL Road FrRed, 48 HL    Road Frame        FR-R92R-48        en   

932   ML Road Tire      ML    Road Tire         TI-R628           th   

*/

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE