DB schema, many-many or bool values in table

Question

If I have a bunch of restaurants in my db, and each restaurant can have either a lunch menu, dinner menu, or brunch menu, does it make sense to have boolean values in the Restaurants table like so:

Or should I create a menu table, and then have a RestaurantMenu table (many-many relationship)

Menu id | Menu Name

1 | lunch menu

2 | dinner menu

3 | brunch menu

If there will only 3 menu column which have `bool` type there no need to create another table — safarov, Mar 19 '12 at 20:34
So, can a restaurant have more than 1 menu or is it exclusively one of those 3? — Mosty Mostacho, Mar 19 '12 at 20:37
Honestly the answer to this question depends on whether you think additional menu types will need to be added to the database. If you are *certain* that you've covered all types then having `bool` columns would be very simple to use and query. But if you have to add one unexpectedly, it might be a headache. I've provided two answers that show a couple of methods that I have used for expandability. — JYelton, Mar 19 '12 at 20:58

score 1 · Accepted Answer · answered Mar 19 '12 at 20:55

Another suggestion is that you use a linker table. This would be more maintainable and easily documented. A linker table is used when you have a many-to-many relationship. (A restaurant can have many types of menu, and a particular type of menu can be utilized by many restaurants.)

This lets you add additional menu types as a row in a "menu_types" table later, without changing the structure of any table.

It does make your queries somewhat more complicated, though, as you have to perform some joins.

First, you would have three tables something like this:

restaurants
---------------
id    name
1     Moe's
2     Steak & Shrimp House
3     McDonald's

restaurant_menus
----------------
restaurant_id    menu_type
1                1
1                3
2                4
3                1
3                3
3                4

menu_types
---------------
id    type
1     Breakfast
2     Brunch
3     Lunch
4     Dinner

So, to see what kind of menus each restaurant offers, your query goes like this:

SELECT r.name, mt.type
FROM restaurants r
    JOIN restaurant_menus rm
        ON (r.id = rm.restaurant_id)
    JOIN menu_types mt
        ON (rm.menu_type = mt.id)
ORDER BY r.name ASC;

This would produce:

name                  type       
--------------------  -----------
McDonald's            Lunch      
McDonald's            Breakfast  
McDonald's            Dinner     
Moe's                 Breakfast  
Moe's                 Lunch      
Steak & Shrimp House  Dinner

Hey thanks for the awesome answer(s). Right now I'm implementing this for an iphone-app, and I'm using HTTP GET. The following is the SQL query I'm using, but it does not contain joins. Is this efficient enough? "SELECT menu_name FROM Menus WHERE menu_id IN (SELECT menus_id FROM Restaurants_Menus WHERE Restaurants_id = '$restaurantID')" — Apollo, Mar 19 '12 at 21:34
I'd say to post this as a new question, regarding SQL query efficiency. But generally speaking, subqueries in my experience have not been very efficient; it depends on your implementation of indexes and other factors. — JYelton, Mar 20 '12 at 14:49

JYelton · Answer 2 · 2012-03-19T20:44:42.187

My suggestion is to use a simple bitmask type of field:

For example:

1 = breakfast
2 = brunch
4 = lunch
8 = dinner

You can add more if it becomes necessary.

If a restaurant has both breakfast and lunch but not brunch nor dinner, that's 1+4, so the column gets 5.

If a restaurant has all of the above, that's 1+2+4+8, which is 15.

It's probably not the most readable for future db maintainers, however. It is, however, a simple way to have one column indicate multiple options.

Edit:

This is just simple binary manipulation. It works like this:

Dinner   Lunch   Brunch   Breakfast
-------- ------- -------- ---------
0        0       0        1             1 (Breakfast only)
1        1       0        0             12 (Dinner + Lunch)
1        1       1        1             15 (All four)

The advantage of this kind of field is that you can add additional menu types with no change to the database, assuming your int field is storing enough bits.

In your program, you can determine what kinds of menus are available with some bitwise operators. In C#, for example:

const int BREAKFAST = 1;
const int BRUNCH = 2;
const int LUNCH = 4;
const int DINNER = 8;

int RestaurantMenuType = 5;

bool OffsersBreakfastMenu = (RestaurantMenuType & BREAKFAST) == BREAKFAST;
bool OffsersBrunchMenu = (RestaurantMenuType & BRUNCH) == BRUNCH;
bool OffsersLunchMenu = (RestaurantMenuType & LUNCH) == LUNCH;
bool OffersDinnerMenu = (RestaurantMenuType & DINNER) == DINNER;

Console.WriteLine("Offers breakfast? {0}", OffsersBreakfastMenu ? "Yes" : "No");
Console.WriteLine("Offers brunch? {0}", OffsersBrunchMenu ? "Yes" : "No");
Console.WriteLine("Offers lunch? {0}", OffsersLunchMenu ? "Yes" : "No");
Console.WriteLine("Offers dinner? {0}", OffersDinnerMenu ? "Yes" : "No");

But if you have `4` how would you distinguish from `3+1`? I think you meant `2^X`, right? — Mosty Mostacho, Mar 19 '12 at 20:36
BUt than 1 + 3 = 4 and 4 also is dinner. So you need 0 - no menu, 1 - breakfast, 2 - brunch, 4 - lunch, 8 - dinner (2^n) — safarov, Mar 19 '12 at 20:38
I started with a bogus application of binary, and have since edited the answer. My apologies for this I was not mentally converting things properly. The values should be 1, 2, 4, 8 and so on, not 1, 2, 3, 4, etc. — JYelton, Mar 19 '12 at 20:56
This is very helpful but unnecessary for my needs. My db is pretty simple at this point so the solution you gave above seems more practical. This is quite interesting though. — Apollo, Mar 19 '12 at 21:32

DB schema, many-many or bool values in table

2 Answers2