SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cocktail/Drink Mixes Table Design

    I am thinking about developing a website that has a big databse of different alcoholic drinks, but the database and code is going to be huge so I was going to see if someone already has something like this I can use as a base or knows something I could use as a base... A example of something I want to make is at http://www.drinksmixer.com/.

    I have kind of sorted some MySQL tables I think.

    catergories:
    catergory_id
    catergory_name

    ingredients:
    drink_id
    ingredients_amount
    ingredients_name

    drink:
    cat_id
    drink_id
    glass_id
    drink_name
    drink_directions
    drink_comment

    glass:
    glass_id
    glass_name
    glass_description

    I think that is right... I don't if I have the ingredients right... Maybe I should just include that in the drinks table?

    ingredients table:
    1 1.5 Tia Maria
    1 1.25 Vodka

    And then when viewing a drink just grab the drink_id and match it against the drink_id in ingredients. Please help me atleast get the MySQL tables up to scratch as this is the main part.

    What else I should I do with these tables so that they are linked correctly? I am new to both PHP and MySQL and I want to the database tables finalized before I start any code.

  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    The ingredients should not have the drink_id in it. You would have way too much replication. Instead, use an ingredient id. You would then have an associative entity to link up the ingredients with the drink.

    So my tables would look like

    ingredients
    ingredient_id
    ingredient_name
    ingredient_proof

    igredient_drink
    ingredient_id
    drink_id
    amount

    drink
    cat_id
    drink_id
    glass_id
    drink_name
    drink_directions
    drink_comment

    Then you would just perform a join on the ingredients table to get all the ingredients for that specific drink. Does that make sense?

  3. #3
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What would be the best way to implement the glass type?

    Do you know of any scripts that already do this?

    Also what is the ingredients_proof?

  4. #4
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nathanj
    What would be the best way to implement the glass type?

    Do you know of any scripts that already do this?

    Also what is the ingredients_proof?
    The ingredients_proof would be the proof of the alcohol (if there is a proof)...

    I would say the way that you are implementing the glass type right now would be fine. Maybe an actual glass_type field, that would hold something like 'Martini' or 'Margarita'.

  5. #5
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    glass_name is probably the equivelent of glass_type.

    This is going to be very complex. Do you know any exsisting programs that do what I am trying to do?

  6. #6
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How will do this?!?

    Sometimes the ingredients won't be exact amounts it may be a dash, one of something etc? How am I going to record this?

  7. #7
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    hmmm

    in the ingredient_drink table make a field called ingredients_measure and hold its measurement (dash, oz, shot, etc.)

    I don't know of any software that does all of this already. You may try HotScripts

  8. #8
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    catergories:
    id
    name

    ingredients:
    id
    name
    description
    image

    drink:
    id
    name
    method
    description
    image

    glass:
    id
    name
    size
    description
    image

    measure_type:
    id
    name

    whattocallthis:
    drink_id
    glass_id
    catergory_id
    ingredients_id
    measurement
    measure_type_id

    This is what I have come up with so far what do you think?

  9. #9
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    It looks good. I think the measurement_type table is overkill though. Just my personal opinion.

  10. #10
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hartmann yeah I am new so I don't know what a good design is :S

    But I thought because I am going to have oz, ml, cl, a splash, a dash, etc it would be handy as well when i want to back a drop down box with type of measue. What should I call the table with all the ids etc?

  11. #11
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Well, for one thing you need an associative entity (a table) that links a drink to its ingredients.

    So, name a table drink_ingredient and have a drink_i_id as well as the drink_id and ingredient_id and quantity in it (as well as measure).

  12. #12
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what_to_call_this:
    id
    drink_id
    glass_id
    catergory_id
    ingredients_id
    measurement
    measure_type_id

    I think that is the table you are talking about isnt it?

    What if I wanted to also add a rating to each drink too?

  13. #13
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    The only problem with that is that you do not need a reference to the glass_id in every record, that's overkill.

    Let me draw up a diagram, that may make it a little easier to understand.

  14. #14
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool I would like to see this.

  15. #15
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You whipped up anything yet?

  16. #16
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nathanj
    You whipped up anything yet?
    I was working on it yesterday and got caught up in a long distance phone call. I'll have something for you today. Sorry for the wait.

  17. #17
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)


    The reason for that middle table is that every ingredient could be in more than one drink and every drink could have more than one ingredient (I assume that every drink needs an ingredient).

    Then, in your SQL query you would do something like:

    Code:
    SELECT d.drink_id, i.ingredient_name as i_name, i.ingredient_amount as i_amount 
       FROM ingredients i, drink d, ingredient_drink id 
       WHERE d.drink_id = $number 
       AND d.drink_id = id.drink_id 
       AND id.ingredient_id = id.ingredient_id

  18. #18
    SitePoint Enthusiast nathanj's Avatar
    Join Date
    Dec 2004
    Location
    Perth, WA, Australia
    Posts
    67
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is extremely handy. Thanks.

    http://www.fabforce.net/dbdesigner4/

    I might give this program a try when I start major development.


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
  •