CODEWITHSUNDEEP
database-designsybasepowerdesigner
dragan.stepanovic
dragan.stepanovic

Reputation: 3005

how to model this database relationship? (sybase powerdesigner)

I'm doing a conceptual model in Sybase PowerDesigner.
The restriction is following:
One doctor can work in only one office at a time during his working time (shift).

I guess Doctor - Office relationship should be many-many, but what about 
time restriction ("during his working time")?
Should it be a new table SHIFT?

So I guess I should have four tables (DOCTOR, OFFICE, SHIFT and OFFICE SCHEDULE).
OFFICE SCHEDULE should be a table connecting all 3 other entities 
and should have composite primary key (id_doctor, id_office, id_shift)?

Upvotes: 1

Views: 4510

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

The primary key of OFFICE SCHEDULE should be (id_office, id_shift). id_doctor should be in the table, but not part of the primary key. This will enforce the rule that given an office and given a shift there can be at most one doctor in that office.

Of course, all three of these are foreign keys in addition to the two that form the primary key.

Upvotes: 0

recursive
recursive

Reputation: 86134

I would change your primary key to only include office and shift, but also add a separate unique contraint on doctor and shift together.

Upvotes: 0

mmmmmm
mmmmmm

Reputation: 32710

That key (id_doctor, id_office, id_shift) would allow a doctor to work in many offices in one shift. Try a key for that table of doctor and shift - office being a dependent field. However this would allow an office to have multiple doctors in a shift and I am not certain if that is allowed.

Upvotes: 1

Related Questions