Using FK or char key? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using FK or char key?

Author

Topic

sriramkanala
Starting Member

2 Posts

Posted - 2004-03-10 : 00:09:14

Hi,

My application has the following entities:
- Programme Year
- Semesters
- Periods
- Weeks
- Classes

Each programme year consists of multiple semesters
Each semeser consists of periods
Each period consists of weeks
Each week consists of classes

APPROACH 1:
I have followed the approach of placing ProgrammeYearID i

The db design is as follows:
Table 1: ProgrammeYear; PK - ProgrammeYearID
Table 2: Semester; PK - SemesterID; FK - ProgrammeYearID
Table 3: Period; PK - PeriodID; FK - SemesterID
Table 4: Week; PK - WeekID; FK - PeriodID
Table 5: Class; PK - ClassID; FK - WeekID.

Now, when I need to know the ProgrammeYear of a Class, I need to perform 4 joins.

Is there any alternative for this design?

APPROACH 2:
Another suggstion I recevied is as follows:
Assuming there are max 9999 programme years, max. 999 semesters in a programme year, max. 999 periods in a semester, max. 999 weeks in a semester, max. 999 classes in a week.
I design the tables as follows:

ProgrammeYear: PK - char(4+3+3+3+3)i.e., char(16)
For first programme year, it contains 0001000000000
First 4 digits refer to programme year
Second 4 digits refer to semester (since this table refers to programme year, these 3 digits would be zero)
Next 3 digits efer to period (zeros once again, as this is programme year table)..and so on.

In "Semester" table, for programme year one, the semeter PK will contain char(16) - 0001001000000
First 4 digits - programme year - 0001,
next 3 digits - semester - 001
next 3 digits - period - 000
next 3 digits - week - 000
last 3 digits - class - 000.

If we continue this way, the CLASS table's PK will look like the following:
(For programme year - 1, semester - 1, period - 1, week - 1, class - 1)
0001 001 001 001 001

With this, if we look at first 4 digits of the PK of class tahle, we can say which programme year it belongs to;
and if we look at next 3 digits, we can say to which period it belongs to.

This way, we can avoid multiple joins for obtaining ProgrammeYearID based on ClassID.

I would like to know which approach is better in terms of peformance; and which one is a better design.

Kindly advise.

Regards,
Sriram

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-10 : 00:18:51

Design 1 is a result of Identity Fever.. An afflication caused by an excessive use of surrogate keys.

Design 2 is a result of Anti-Codd fever.. An afflication caused by the breaking of 1NF and thus relational useless....

So in summary they both suck!

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

sriramkanala
Starting Member

2 Posts

Posted - 2004-03-10 : 01:43:13

David,

Can you please suggest a suitable design for this scenario?

Sriram

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-10 : 03:45:50

Sriram,

Find the natural keys and use those...

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 07:56:49

Remember -- more than 1 column can participate in a Primary Key !!!!!!!! and more than column can participate in a Foreign Key constraint !! don't go concatenating columns together to try to "generate" primary keys when you don't need to.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources