October Is Scary Schema Month

Wednesday, October 10, 2007

Ayende kicked the month off with "Schema to wince by…", then "CRM Horror".

Great timing, Ayende! With Halloween right around the corner, let's make October scary schema month!

Here is one that recently made my hair stand on end:

ALTER TABLE [dbo].[EmergencyPatientAssesments]
   
ADD CONSTRAINT [mspk550470] PRIMARY KEY CLUSTERED
(
   
[BaseID] ASC,
   
[PatientVisitID] ASC,
   
[TreatmentID] ASC,
   
[DateTime] ASC,
   
[ActualDateTime] ASC,
   
[AssessmentSequenceID] ASC,
   
[QuestionID] ASC
) ON [PRIMARY]

This healthcare schema defines a composite primary key using 5 varchar fields and 2 datetime fields. If you think the index is scary, you should see the joins…

Do you have a scary schema to share?


Comments
Sahil Malik Wednesday, October 10, 2007
EEEeeeeeeeeeeeeeeeeeeeeeeeek!!
Simon Wednesday, October 10, 2007
After discussing this with team mates the feeling was that this is fine for a fact table in a data warehouse using a star schema.

Was a new one on me but..

http://en.wikipedia.org/wiki/Star_schema
scott Wednesday, October 10, 2007
Technically correct, but still scary ;)
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!