|
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
*/
|
