SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member metabeat's Avatar
    Join Date
    Mar 2002
    Location
    Spain
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Lookup Table > Can Primary Key's contain dates?

    I am setting up something of a lookup table to track what specials (from another table) appear every day on a restaurant's daily menu...

    the table I have created is as follows:

    Code:
    mysql> describe days;
    +-----------+------------+------+-----+------------+-------+
    | Field     | Type       | Null | Key | Default    | Extra |
    +-----------+------------+------+-----+------------+-------+
    | specialID | int(11)    |      |     | 0          |       |
    | date      | date       |      |     | 0000-00-00 |       |
    | courseID  | tinyint(4) |      |     | 0          |       |
    +-----------+------------+------+-----+------------+-------+
    3 rows in set (0.00 sec)
    Apart from the specialD field, which specifies which special we're talking about, and the date field which is self explanatory, I also need to specify wether the special is for lunch or dinner that day (in some cases it might be both).

    I guess the primary key, as I imagine it, should be a multi-column primary key, so i can pull the specials for each day's lunch and dinner menus and print them out. What columns should the primary key span though? Can I specify more than two columns for a primary key, and must they be numeric?

    Thx!

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Keys can be any datatype you like as long as the combination creates a unique value
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Will any 2 columns have the same specialID? I'm guessing they will if you ever repeat the same special. You could use a combo of the specialID and date if you will NEVER have the same special on more than one day. If there could still possibly be comflict you would have to use a combo of all 3 columns. In that case it would be better to create a new column just for the primary key and make it auto increment.

  4. #4
    SitePoint Member metabeat's Avatar
    Join Date
    Mar 2002
    Location
    Spain
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, specials will be repeated, and what's more they might be repeated on the same day, which is why I have the courseID column. I've also added a new column to track the position of each special on the daily menus, in case the staff want to change the order the specials print after entering them. My table now looks like this
    Code:
    mysql> describe days;
    +-----------+------------+------+-----+------------+-------+
    | Field     | Type       | Null | Key | Default    | Extra |
    +-----------+------------+------+-----+------------+-------+
    | date      | date       |      | PRI | 0000-00-00 |       |
    | specialID | int(11)    |      | PRI | 0          |       |
    | courseID  | tinyint(4) |      | PRI | 0          |       |
    | slotID    | tinyint(4) |      | PRI | 0          |       |
    +-----------+------------+------+-----+------------+-------+
    4 rows in set (0.00 sec)
    Since this is essentially a kind of lookup table I don't think I need a unique ID column, since I'll be using this table to pull up the specials say for lunch on a particular day, in a particular order.

    Let me know if this can be improved in any way...

  5. #5
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just remember that if all 4 columns are the primary key, you will have to use all 4 columns in order to modify or delete a row. Using a seperate ID column would make modifying and deleting easier.

    Also, your queries will only use the index if they use the columns in the order of the primary key. You may want to add other indexes if you will be searching in other ways (which is very likely).

  6. #6
    SitePoint Member metabeat's Avatar
    Join Date
    Mar 2002
    Location
    Spain
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see your point about the keys... I have to read up on the subject as this is my first database, but I think I'm starting to see the light in as much as keys is concerned.

    About the ID column, I still don't quite see how I would use it. Since this is a lookup table, at most I'll be saying 'show me the specials for this date' or 'how many days has this special been on a menu'. How would I use a unique ID, I mean, how would I know what records are on what ID? Surely I would have to do a query to find this out (wouldn't I effectively end up having to use a lookup table for this lookup table?)

    Thx for all the help!

  7. #7
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For a select query, you wouldn't use the primary key. The primary key is used to either modify/delete a row, or to link it with another table.

    So when you are looking up a special, the primary key won't even play a part unless you need to use it to match up some row(s) in a different table.

    So I guess if you don't plan on using another table to relate with data in the lookup table, using the 4 columns as a primary key would be ok. However, if you do plan on relating the data with another table, it is best to have a single comumn primary key. This is because you will need to use the primary key in the lookup table as a foreign key in the 2nd table. A 4 column foreign key just doesn't work very well.
    Last edited by xev; Apr 5, 2002 at 07:14.

  8. #8
    SitePoint Member metabeat's Avatar
    Join Date
    Mar 2002
    Location
    Spain
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I think I got it now. I'm gonna put in a unique ID column and have that as the Primary Key.

    Then I'll use explain on my most common queries and create indexes as needed.

    I'll mostly be pulling daily menus, so I guess I might make a multi column index(date,courseID,slotID) to get ordered results for each day.

    I think I have just seen the light as to how Keys work thanks to u guys!! This is great fun!!

    Any good ideas on how to allow users to change the order of specials on the daily menus?

  9. #9
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure what you mean by changing the order. Maybe the 'ORDER BY' statement is what you're looking for?

    As far as keys go, any column that you use in the 'WHERE' section of a select statement is a good candidate for a key. Especially if the the column has a lot a variation in it. Columns that only have 1 or 2 different possible values won't benefit from a key as much as a column that has hundreds or thousands of possible values.

  10. #10
    SitePoint Member metabeat's Avatar
    Join Date
    Mar 2002
    Location
    Spain
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need a method of recording what order the specials appear on the daily menus, and I also need the staff to be able to change this order, so say if they add a soup at the end of the menu they might want it to appear first on the menu. These changes need to be recorded on the days table, which is why i created the slotID column.

    Just trying to find what the logic would be behind using the slotID's...

  11. #11
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is one way you could do it. Whenever you add a new item you would need to find the highest slotID, and increment that by one to find the new item's slotID. If you need to add an item to the beginning or middle of the order, you would 1st need to increment all the other item's slotID that will be below it by 1. Then just insert the new item into the break in the slotID order it created.

    You need to be careful in your sql qeuries here though. It will be really easy to mess up the order. Locking the table first would be a good idea.

    Here is example code for incrementing the slotID for items in the 3rd position and below. After this, just insert the new item with a slotID of 3.
    Code:
    UPDATE days SET slotID=(slotID+1) WHERE slotID >= 3
    Last edited by xev; Apr 6, 2002 at 16:15.


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
  •