Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Jonas Moltumyr
Jonas Moltumyr
1,939 Points
Posted by Jonas Moltumyr
Jonas Moltumyr
1,939 Points

Need some advice on database design, relationship table

Let me fill you in on what I'm making.

I'm creating an app (ios) for a bar im working at. Im going to create a "Find drink" function, where the user can specify certain criterias to find drinks in our menu that suits them the best.

Give me a drink with Whisky as the base spirit. I want the drink to be medium of strength. And it should be sour and bitter.

What I need help to is designing the database when it comes to flavors. A drink can have several flavor profiles like:

1 - Bitter 2 - Bubbly 3 - Creamy 4 - Fruity etc.

I have made a table called cocktail. This table has Drink_id as primary key. I've also made a table called Flavors. Where Flavor_id is the primary key,

I guess I can make a table called FlavorRelation (or something like that), where field 1 could contain "Drink_id" and field two would contain "flavor_id".

I'm pretty new to coding, So I wonder how it is to show these and filter if I choose this database design later in code..

I guess its also possible to create several fields in the cocktail table, f.ex

Flavor1_id Flavor2_id Flavor3_id etc...

Then I know it would be easy to retrieve the information. I will be creating a REST webservice for my app, if this is relevant for the database design.

Hope this did not get to messy :)

3 Answers

Matthew Brock
Matthew Brock
16,791 Points
Matthew Brock
Matthew Brock
16,791 Points

There are many ways to achieve what you are looking for. It would depend on speed and the number of choices of flavor types. If your number of flavor types is 64 or less you could use the SET data type. This would allow setting multiple flavor types per drink. 

drink
    id
    name 
    set of flavors(64 different types max) -

check out the MySQL data type - http://dev.mysql.com/doc/refman/5.1/en/set.html

Another way of doing it would be having a flavor table and a flavor to drink table where

drink
   id
   name

flavor
   id
   text for flavor

flavor2drink
   drink_id
   flavor_id

And another way would be to just have a limited number of different flavor types per drink

flavor
   id
   name

drink
   id
   name
   flavor1_id
   flavor2_id
   flavor3_id
   ... etc
Jonas Moltumyr
Jonas Moltumyr
1,939 Points
Jonas Moltumyr
Jonas Moltumyr
1,939 Points

Well the flavor table will not contain many rows. For now it is 9 different flavors. I dont think it will ever get past 15 to be honest.

What solution would you recommend? I'm not familiar with the SET command, and Im using MSSQL, not sure if that matter though.

Matthew Brock
Matthew Brock
16,791 Points
Matthew Brock
Matthew Brock
16,791 Points

Do you mean MySQL - if so i would go with the SET data type. It will allow you to use up to 64 different types, each one being a text description. and associate each drink with as many different types as you set. look at link below for MySQL SET data type

http://dev.mysql.com/doc/refman/5.1/en/set.html

If you use the SET data type you will not need another table for the different flavors

drink
    id
    name 
    set of flavors(64 different types max)
Jonas Moltumyr
Jonas Moltumyr
1,939 Points
Jonas Moltumyr
Jonas Moltumyr
1,939 Points

Im using Microsoft SQL, does it support the SET type?

Matthew Brock
Matthew Brock
16,791 Points
Matthew Brock
Matthew Brock
16,791 Points

No it does not. You could do the same thing with a bit field, but that would take a lot of time to explain.

Try this setup

drink
   id
   name

flavor
   id
   text for flavor

flavor2drink
   drink_id
   flavor_id
Jonas Moltumyr
Jonas Moltumyr
1,939 Points
Jonas Moltumyr
Jonas Moltumyr
1,939 Points

Thank you!

Is there any good way to write a query, so I and up with only one row pr. drink_id with several columns for the flavor_id?

I guess this is the "drawback" using this method, I need to handle this in the application I guess.