SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table Design: Using parents

    Let's say I have a form with a drop-down called 'options'

    I have different forms. The list of options vary, but sometimes the same option is needed on different forms.

    There are about 10 forms in all, and each one has some different and some of the same options.

    Example Form 1
    Options:
    Working
    Attempted Call
    Emailed Quote

    Example Form 2
    Working
    Emailed Quote
    Not Interested
    Submitted

    I figured I could create a table like this:
    Code HTML4Strict:
    id          parent        option
    --------------------------------
    1          NULL          form1
    2          NULL          form2
    3          1             Working
    4          1             Attempted Call
    5          1             Emailed Quote
    6          2             Working
    7          2             Emailed Quote
    8          2             Not Interested
    9          2             Submitted
    This might look pretty ridiculous, but at least I stopped myself and came here before going on! I'd rather not be duplicating data here, but I don't see how else to design the table. Any suggestions?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,904
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    1) I'm not sure why you want to store the options in the database, why not just hard code them in the HTML? Or are they subject to a lot of change so it's easier to manage them dynamically.

    2) If you really want to put them in the database I would do it like this:

    Code:
    TABLE: options
    
    id         option
    --------------------------------
    1          Working
    2          Attempted Call
    3          Emailed Quote
    4          Not Interested
    5          Submitted
    
    TABLE: forms
    
    form_id    option_id      position
    ----------------------------------
    1          1              1
    1          2              2
    1          3              3
    2          1              1
    2          3              2
    2          4              3
    2          5              4
    Mainly because that doesn't repeat option names so if you ever have to change one you only need to change it once.
    Also I feel that forms and options are completely different entities and mixing them in the one table just looks weird / feels wrong.

    The position column is meant to order the options by, but is completely optional; you can also order by some other criteria if you like.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My client likes to make little phpMyAdmin changes to the database where he can, so I said I'd try to build an options table for him. I built this in a PHP reference file already, but he wanted to update the forms himself.

    You've given me some good ideas here. Thanks for the assistance, once again.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,904
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    You're welcome. And whatever you do, don't be tricked into using ENUM. Ask @r937 ; if you want to know why not
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

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