[cvbayot]

hello. i'm a newbie in database and stuff. i just want to ask for help in designing a database.

i'm developing a web-based system that will allow users to view and update patient records. the thing is, nurses can only update patient records assigned to them (room assignment) in a specific date and time.

a group of nurse will be assigned to a group of rooms.
example:
for room 1-20 nurses A and B will be assigned today from 6am-2pm
for room 21-40 nurses C and D will be assigned today from 2pm-12am
for room 1-20 nurses B and C will be assigned the next day from 6am-2pm
for room 21-40 nurses A and D will be assigned the next day from 2pm-12am

my current database design is this..
i have a table for assign, anurse, and aroom. they have the following attributes:
assign (asid_pk, adate, stime, etime); asid_pk-primary key, adate-schedule date, stime-start of shift, edate-end of shift
aroom(asid_fk, rno_fk); asid_fk-foreign key referencing asid_pk from assign, rno_fk-foreign key referencing rno_pk from the table for room
anurse(asid_fk, nid_fk); asid_fk-fk from assign, nid_fk-fk from the table for nurse

my problem is..
i have to duplicate entries in assign table to assign different nurses in different rooms. there will be 12 entires in the assign table with the same date and time and different primary keys. 12 because there are 12 groups of rooms and in each group 1-4 nurses will be assigned.

does anyone have a suggestion in improving my current database design? thanks!