Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

database design help please Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 3:58 AM
Points: 2, Visits: 11
Hi I am having some trouble on how to normalize some tricky tables.

Basically it is a recurrence event table that can either be daily, weekly, monthly or yearly.

I have made it so each recurrence row in the header table can have one id for either daily, weekly, monthly or yearly. This id corresponds to the appropriate table. Each table has different structures.

So here is an example of what I have.

Table: Recurrence
Columns: RecurrenceId (PK), RecurrenceDailyId(FK), RecurrenceWeeklyId (FK),
RecurrenceMonthlyId (FK), RecurrenceYearlyId (FK)

Table: RecurrenceDailyId
Columns: RecurrenceDailyId(PK), EveryDays, DayOfWeek etc

Table: RecurrenceWeeklyId
Columns: RecurrenceWeeklyId(PK), EveryWeeks, TimesAWeek etc

etc

What is the best way to create this structure so it is normalized? am I on the right path?
Post #1410874
Posted Thursday, January 24, 2013 8:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 15,662, Visits: 28,055
Nah, I'd say you're digging a bit of a hole. Instead how about something like

Recurrence ... whatever columns are unique to the recurrence
RecurrenceSchedule ... just a listing of each of the types, daily, monthly, etc., and any unique values to them
RecurrenceScheduleType ... a join between the schedule and the recurrance. Probably make the primary key the two tables keys, but put a unique constraint on the Recurrence key in addition so that a recurrence can only have one type.

The other method involves a lot of null values and joins to tables without data. This will ensure you always get data back.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1411161
Posted Sunday, January 27, 2013 9:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 3:58 AM
Points: 2, Visits: 11
Thanks
Post #1412174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse