Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Moving 2 fields to separate table. Does this make good design sense?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PUK_999 View Post
    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.
    pretty decent thinking for a noob

    such is often not seen from people who have tons of design experience

    this design also allows a very easy migration path to a scenario where work can be assigned to more than one user

    nice job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I agree with your two table design. To enforce the same thing with the single table design you could use a CHECK constraint:

    CHECK (
    (ASSIGNED_USER IS NULL AND ASSIGNED_AT IS NULL)
    OR (ASSIGNED_USER IS NOT NULL AND ASSIGNED_AT IS NOT NULL)
    )

    but I find it more logical to make two separate tables for what are in reality two different types of fact. There's no obvious reason to bundle two different entity types into one table. Keeping them separate has at least the advantage of avoiding any possible ambiguity about the meaning of nulls and it also eliminates an additional constraint which would otherwise have to be validated for each insert and update.

  4. #4
    Join Date
    Dec 2009
    Posts
    8
    Thank you both for giving it the once over. Nice to learn about the CHECK constraint too.

    PUK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •