1
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)?
dragan.stepanovic
  • 2,955
  • 8
  • 37
  • 66
  • What database will you be using? it may change how you would approach this. – Tom Neyland Sep 23 '09 at 16:53
  • Also, is OFFICE SCHEDULE in a 1:M (or M:M) relationship with OFFICE? If it is a 1:1 relationship then OFFICE SCHEDULE should be part of the OFFICE table – Tom Neyland Sep 23 '09 at 17:00
  • MS SQL I'm a bit confused when having to separate many to many relationship (doctor-office) into a separate table, but I think it office - office schedule should be 1:M – dragan.stepanovic Sep 23 '09 at 17:11
  • 1
    Tnay, Power designer lets you do a conceptual model in which you model entities & relationships together with attributes and domains but independently of which database you are going to implement on. Later, you can pick a DBMS (or more than one!) and convert to a physical model (or more than one!). This turns out to be enormously powerful, in some situations. – Walter Mitty Sep 23 '09 at 17:47
  • true. I'm doing conversion to physical model afterwards, and I'll be using using MS SQL 2000 – dragan.stepanovic Sep 23 '09 at 18:01

3 Answers3

1

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.

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
0

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

recursive
  • 83,943
  • 34
  • 151
  • 241
0

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • but then a doctor could be in two offices for the same shift. You need two unique constraints for the office schedule table – mmmmmm Sep 24 '09 at 08:49