Hello,
(First post so be gentle
)
I've split the following table:
Code:
CREATE TABLE WORK_ITEM
(
WORK_ITEM_ID INT NOT NULL AUTO_INCREMENT,
SUBMITTED_AT DATETIME NOT NULL,
DEADLINE DATETIME,
ASSIGNED_USER INT,
ASSIGNED_AT DATETIME,
PRIMARY KEY(WORK_ITEM_ID),
FOREIGN KEY(ASSIGNED_USER) REFERENCES USER(USER_ID)
);
into the following two tables:
Code:
CREATE TABLE WORK_ITEM
(
WORK_ITEM_ID INT NOT NULL AUTO_INCREMENT,
SUBMITTED_AT DATETIME NOT NULL,
DEADLINE DATETIME,
PRIMARY KEY(WORK_ITEM_ID)
);
CREATE TABLE ASSIGNED_WORK_ITEM
(
WORK_ITEM_ID INT NOT NULL,
ASSIGNED_USER INT NOT NULL,
ASSIGNED_AT DATETIME NOT NULL,
PRIMARY KEY(WORK_ITEM_ID),
FOREIGN KEY(WORK_ITEM_ID) REFERENCES WORK_ITEM(WORK_ITEM_ID),
FOREIGN KEY(ASSIGNED_USER) REFERENCES USER(USER_ID)
);
because an item of work may not be assigned to a user. If there's no record in ASSIGNED_WORK_ITEM then the work isn't assigned. (Work can only be assigned to a maximum of one user.) I thought this was better than having the optional ASSIGNED_USER and ASSIGNED_AT attributes on WORK_ITEM because there was no way for the database to enforce all NULL or all not-NULL.
Is my approach sensible? I'm fairly new to database design so
any criticism is very welcome!
Thanks,
PUK
dBforums
>
Moving 2 fields to separate table. Does this make good design sense?
12-31-09, 11:15
Linear Mode
