help with database design of room assignment schedules of nurses?

Question

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

current design:
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

problem:
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!

i don't think BlueFeather's solution will work. 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

Answer 1

Hi,
I am really sorry but i don't understand what you are saying ?? I know how hard it is to write a detailed problem out. please I.M me back and i will try to help you.

Answer 2

Maybe I don't understand the problem correctly, but I think two tables (in a One-To-Many relationship) ought to suffice:

ASSIGN
assign_id (primary key)
and non-key fields as needed

NURSE
assign_id (foreign key)
nurse_id
room_id
(where the Primary Key is composed of assign_id, nurse_id, room_id)
and non-key fields as needed

With this configuration, a single assignment would consist of a group of nurses, each assigned to a specific room for a specific time period.

Each assignment (in the ASSIGN table) would apply to a shift, day, or week (whatever is applicable).

§

Answer 3

I dont think creating a solution is a problem, more to do with fully understanding the task.

Answer 4

Does not look easy. May be you can contact a database expert at websites like http://askexpert.info/