0

I'm developing a web administration panel for a local restaurant directory. Their old system is very clunky and not able to expand without modifying a bunch of code.

The current DB structure is like this:

table: Restaurants
cols: id, restname, preferred, image, phone, address, website, vip_special

This table is static as far as columns go.

table: Locations
cols: id, restname, downtown, bluffton, tybee, pooler, etc...

table: Cuisines
cols: id, restname, american, chinese, seafood, bar_tavern, etc...

table: Ambiances
cols: id, restname, outdoor_dining, fine_dining, waterfront, rooftop, romantic, etc...

The latter 3 tables require a developer to go in and add a new column for each new location, cuisine, or ambiance as well as change the code to reflect those new columns.

I'm not the greatest when it comes to DBA so I was curious as to what the best structure would be to this situation.

Associations: A restaurant can have several locations, cuisines, and ambiances

Thank you all for the help.

Stefano Borini
  • 138,652
  • 96
  • 297
  • 431
Logan
  • 3
  • 1

1 Answers1

0

Here's a start

restaurant
restaurant_id | restaurant_nm | everything that a restaurant has exactly one of

tag_restaurant_rel
restaurant_id | tag_id

tag
tag_id | tag_type(_id) | tag_nm

tag_type
tag_type_id | tag_nm

Tag type could be an enum if you have a few types that won't change you can drop the type table.
I'm not sure if you want to assign one restaurant multiple locations as, even though they might be a chain, the ambiance might be different and the menu might offer different cuisines

Moak
  • 12,596
  • 27
  • 111
  • 166
  • So the tag table can have a row like this? `1 | ambiance | waterfront` `2 | location | downtown` `3 | cuisine | chinese`? That would make tag_restaurant_rel: `1 | 3` `1 | 2` `1 | 1` – Logan Apr 10 '11 at 03:21
  • The location, ambiance, and cuisine issue with a chain or multi location type restaurant won't matter because they will be creating a new record for each specific location because of that reasoning and the possibility that not every location will be opting in with this service. – Logan Apr 10 '11 at 03:45
  • @Logan correct the the rel table is for creating joins. and you can asign 0-∞ relationships. You wont be creating any new tables if you want to start tagging venues with bus/metro stops or – Moak Apr 10 '11 at 08:14
  • Also you will want to use InnoDB and use foreign keys for the relation table. that way when a venue or a tag gets removed all the useless relations will be cascaded – Moak Apr 10 '11 at 08:16