Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Thumbs up Unanswered: Table Design Issue

    I have some questions regarding these tables.

    Question 1:

    Is it best to have the design like this, ie different tables for each type linked with a foreign key or would it be better to just have the main table and on implementation just use an ENUM to offer the possible choices for each type? Welcome any suggestions.

    ========================
    Notification_Table
    ========================
    Notification_ID
    Person_ID
    NotificationType_ID
    NotificationSubType_ID
    Notification_Details
    Notification_OpenDate
    Notification_CloseDate
    Target_Date
    StatusType_ID
    ========================


    =======================
    NotificationType_Table
    =======================
    NotificationType_ID
    Notification_Type
    =======================


    ==========================
    NotificationSubType_Table
    ==========================
    NotificationSubType_ID
    Notification_SubType
    ==========================


    ========================
    StatusType_Table
    ========================
    StatusType_ID
    Status_Type
    ========================

    Regards

    Wealth-Builderz
    Last edited by Wealth-Builderz; 12-02-04 at 08:41.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the "lookup" tables are by far the better design

    ENUM is evil, don't drink that koolaid!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In principle r937 is right. However in , very very, few circumstances an enum may be a practical design. imhv it works if you can at design time be confident than you can capture all possible states. If you can then an enum is practical, but in practise I've always used tables as r937 suggests. I'm no propoellor head and therefore never feel entirely happy that we can define all states to use an enum.

  4. #4
    Join Date
    Oct 2004
    Posts
    29
    Thanks for the advice.

    I wanna try get all my table design questions out of the way.

    Question 2:

    In the Notification table shown below I need to store two Person_ID's, one pertaining to the Customer (submitter) and another one pertaining to the assigned Resolver (respondant). How should/would I go about modelling this in the design. I wouldn't have this trouble if I had two separate tables ie Customer and Resolver but I've been reading that it is best to keep all the Person like objects/attributes in one entity and then subtype the types of Person for ease and flexibility of maintenance. Again any advice would be most welcome.

    ========================
    Notification_Table
    ========================
    Notification_ID
    Person_ID
    NotificationType_ID
    NotificationSubType_ID
    Notification_Details
    Notification_OpenDate
    Notification_CloseDate
    Target_Date
    StatusType_ID
    ========================


    ========================
    Person_Table
    ========================
    Person_ID
    First_Name
    Last_Name
    Username
    Password
    PersonType_ID
    ========================

    ========================
    PersonType_Table
    ========================
    PersonType_ID
    Person_Type
    ========================

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are correct that all persons should be in one entity, with attributes to distinguish their types

    speaking of types, the existence of both type and subtype in the notification table is suspect

    is there a relationship between type and subtype? if so, the design fails second (i think) normal form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2004
    Posts
    29
    Ummm, I'm new to databases, I'm not entirely sure.

    One of my project requirements is to produce a report showing the number of complaints by type and sub type over a given period, hence me creating a 'Notification' table supporting these two individual types. How would you suggest going about it, always welcome any advice which helps me learn?

Posting Permissions

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