Re: design question

From: Heinz Huber <XhhuberX_at_no-racon-linz.at-no>
Date: Wed, 22 Jan 2003 15:14:34 +0100
Message-ID: <3e2ea74b$0$18612$91cee783_at_newsreader01.highway.telekom.at>

stu wrote:
> "Heinz Huber" <XhhuberX_at_no-racon-linz.at-no> wrote in message
> news:3e2e4b3f$0$20418$91cee783_at_newsreader02.highway.telekom.at...
>

>>As soon as you have a supertype, you don't have to create new surrogate
>>keys for the subtypes. If teacher/student ID are generated values, you
>>can simply use Users.ID since that value is unique anyway.

>
> But think about what happens if a student also teaches a class. The User ID
> on its own is no longer unique.

The id is still unique in each one of the following three fields: Among all teachers.
Among all students.
Among all users.

But you can't distinguish any more whether the person acted as a teacher or as a student.

On the other hand, why would you want to do this via id? If you care about users (the only field where teachers AND students are concerned), you would use the user id anyway. If it is interesting whether it the person was acting as a teacher or as a student, you'd have to record this fact anyway.

Unless you have to users if a person is a teacher and a student. Then the user id is unique again!

About teaching a class:
CREATE TABLE Classes (

     classId INTEGER NOT NULL,
     teacherId INTEGER NOT NULL REFERENCES Teachers(userId),
     PRIMARY KEY (classId),
     ....);
CREATE TABLE ClassAttendents (
     classId INTEGER NOT NULL REFERENCES Classes(classId),
     attendentId INTEGER NOT NULL REFERENCES Students(userId),
     PRIMARY KEY (classId, attendentId),
     ....);

You implicitly know whether the value in Classes.teacherId was a teacher or a student. It can be only the former. Simular argument holds for Classes.Attendents.attendentId.

I'm not even sure, if in proper subtyping, a user is allowed to be a teacher AND a student.

Heinz Received on Wed Jan 22 2003 - 15:14:34 CET