CODEWITHSUNDEEP
mysqlcodeignitersystem-administrationdb-structure
Logan
Logan

Reputation: 3

Correct MySQL DB Structure

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.

Upvotes: 0

Views: 116

Answers (1)

Moak
Moak

Reputation: 12885

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

Upvotes: 0

Related Questions