quote:

---

Originally posted by coolerbob

Thanks for answering Jeff.
Tell me if this is not 3rd normal form:
I have a history table that stores records for things that took place.
Those 5 ID's represent 5 different objects that were involved in that event eg if I dream a few entities/tables up for you: Companies, People, Contracts, Locations,...

What other way is there to do this? Whats wrong with it?

---

EVENT  A    B  C  D  E
e1     a0   b  c  d  e
e1     a1   b  c  d  e

EVENT A
e1    a0
e1    a1

EVENT B
e1    b

EVENT C
e1    c

EVENT D
e1    d

EVENT E
e1    e

EVENT  A    B  C  D  E
e1     a0   b  c  d  e
e1     a1   b  c  d  e

EVENT  A    B  C  D  E
e2     NULL b  c  d  e

EVENT  A    B    C    D     E
e3     NULL b    c    NULL  e
e3     a    NULL c    d     NULL

-- "denormalized" event table
CREATE TABLE Event3NF
(
	EventID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT(NEWID())
	,EventDate DATETIME DEFAULT GETDATE()
	,AID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,BID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,CID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,DID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,EID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,FID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,GID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
	,HID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())
)

-- "fully normalized" event tables
CREATE TABLE Event4NF
(
	EventID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT(NEWID())
	,EventDate DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE TABLE EventA4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),AID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,AID) )
CREATE TABLE EventB4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),BID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,BID) )
CREATE TABLE EventC4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),CID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,CID) )
CREATE TABLE EventD4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),DID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,DID) )
CREATE TABLE EventE4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),EID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,EID) )
CREATE TABLE EventF4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),FID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,FID) )
CREATE TABLE EventG4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),GID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,GID) )
CREATE TABLE EventH4NF( EventID UNIQUEIDENTIFIER REFERENCES Event4NF(EventID),HID UNIQUEIDENTIFIER DEFAULT(NEWID()), PRIMARY KEY(EventID,HID) )
GO

-- insert test data
-- create 200000 random events...
PRINT 'Generating 100000 random events, (same info in "broad" table and in fully normalized schema) ...'; PRINT ''
INSERT Event3NF(EventID) SELECT TOP 100000 NEWID() FROM master..spt_values t1 CROSS JOIN master..spt_values t2
SET NOCOUNT ON
GO
INSERT Event4NF(EventID) SELECT EventID FROM Event3NF
INSERT EventA4NF(EventID,AID) SELECT EventID,AID FROM Event3NF
INSERT EventB4NF(EventID,BID) SELECT EventID,AID FROM Event3NF
INSERT EventC4NF(EventID,CID) SELECT EventID,AID FROM Event3NF
INSERT EventD4NF(EventID,DID) SELECT EventID,AID FROM Event3NF
INSERT EventE4NF(EventID,EID) SELECT EventID,AID FROM Event3NF
INSERT EventF4NF(EventID,FID) SELECT EventID,AID FROM Event3NF
INSERT EventG4NF(EventID,GID) SELECT EventID,AID FROM Event3NF
INSERT EventH4NF(EventID,HID) SELECT EventID,AID FROM Event3NF
GO

-- create view over the "fully normalized" schema, to mimic the "denormalized" schema
-- this is just to make retrieval easy to present
CREATE VIEW vEvent4NF WITH SCHEMABINDING AS
SELECT
	nf4.EventID
	,nf4.EventDate
	,anf4.AID
	,bnf4.BID
	,cnf4.CID
	,dnf4.DID
	,enf4.EID
	,fnf4.FID
	,gnf4.GID
	,hnf4.HID
FROM
	dbo.Event4NF nf4
	JOIN dbo.EventA4NF anf4 ON nf4.EventID = anf4.EventID
	JOIN dbo.EventB4NF bnf4 ON nf4.EventID = bnf4.EventID
	JOIN dbo.EventC4NF cnf4 ON nf4.EventID = cnf4.EventID
	JOIN dbo.EventD4NF dnf4 ON nf4.EventID = dnf4.EventID
	JOIN dbo.EventE4NF enf4 ON nf4.EventID = enf4.EventID
	JOIN dbo.EventF4NF fnf4 ON nf4.EventID = fnf4.EventID
	JOIN dbo.EventG4NF gnf4 ON nf4.EventID = gnf4.EventID
	JOIN dbo.Eventh4NF hnf4 ON nf4.EventID = hnf4.EventID
GO

-- create small search table to retrieve data for 10 randomly chosen EventID's
-- search these 10 random events from the 3NF, 4Nf tables;
CREATE TABLE #searchevents(EventID UNIQUEIDENTIFIER)
INSERT #searchevents(EventID) SELECT TOP 10 EventId FROM Event3NF ORDER BY NEWID()
GO

-- Start performance testing
-- Over the "denormalized" table and the "fully normalized" tables
--CREATE UNIQUE CLUSTERED INDEX ixXXX ON vEvent4NF(EventID,EventDate,AID,BID,CID,DID,EID,FID,GID,HID)
DECLARE @d DATETIME

DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; PRINT '' -- Clear cached data
PRINT 'Counting total no of events from "broad" table...'
SET @d = GETDATE()
SELECT COUNT(*) AS CntEvents3Nf FROM Event3NF
SELECT DATEDIFF(ms,@d,GETDATE()) AS elapsed_time3nf_Count

/*DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE -- Clear cached data
SET @d = GETDATE()
SELECT COUNT(*) AS CntEvents4Nf FROM vEvent4NF
SELECT DATEDIFF(ms,@d,GETDATE()) AS elapsed_time4nf_Count*/

DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; PRINT '' -- Clear cached data
PRINT 'Counting total no of events from "4nf" table...'
SET @d = GETDATE()
SELECT COUNT(*) AS CntEvents4Nfb FROM Event4NF
SELECT DATEDIFF(ms,@d,GETDATE()) AS elapsed_time4nf_Count

DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; PRINT '' -- Clear cached data
PRINT 'Retrieving 10 random events from "broad" table...'
SET @d = GETDATE()
SELECT * FROM Event3NF WHERE EventID IN(SELECT EventId FROM #searchevents) ORDER BY 1,2
SELECT DATEDIFF(ms,@d,GETDATE()) AS elapsed_time3nf_Select10

DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; PRINT '' -- Clear cached data
PRINT 'Retrieving 10 random events from 9-table join of 4nf normalized tables table...'
SET @d = GETDATE()
SELECT * FROM vEvent4NF WHERE EventID IN(SELECT EventId FROM #searchevents) ORDER BY 1,2
SELECT DATEDIFF(ms,@d,GETDATE()) AS elapsed_time4nf_Select10
GO

/*
-- Clean up
DROP TABLE #searchevents
DROP VIEW vEvent4NF
DROP TABLE EventA4NF
DROP TABLE EventB4NF
DROP TABLE EventC4NF
DROP TABLE EventD4NF
DROP TABLE EventE4NF
DROP TABLE EventF4NF
DROP TABLE EventG4NF
DROP TABLE EventH4NF
DROP TABLE Event3NF
DROP TABLE Event4NF
*/
GO

quote:

---

Originally posted by jsmith8858

I know I am badly paraphrases a common quote or saying, but it goes something like this: premature optimization is just about the worst thing a developer or database modeller can do.
- Jeff

---