I'm new to database design and I need some critiques, feedback, suggestions, advice, etc. regarding a) some business rules, b) the entity-relationship diagram I have developed to represent them, and c) whether or not they "match".
Business rules
The relevant business rules are as follow:
- Users can rate multiple businesses
- A business can fall under more than one category
- Users can comment on multiple businesses
- Businesses can have zero to many ratings
- Businesses can have zero to many comments
- A business can only be located in one and only one city
- A city can have multiple businesses
- A category can have multiple businesses
- Users can have one or more checkins for businesses
- Businesses can have zero to many checkins
My entity-relationship diagram
And I have represented them in the diagram below:
Best Answer
You need a table between Cities -> Business, and a table between Categories and Business.
This way since city can have many business keeping it all in the single table you are breaking the 2nd Normal Form which is suggesting that the groups of data shouldnt be repeated within a table. What that means is that for instance if you have City of New York that has multiple business you will have 2+ records with the same City name but different City ID.
Therefore i would keep the table cities with metadata about the City only and add a new table CityBusiness with its own identity id and c_id and b_id OR since cities can have only one business (is it possible for one business to expand and open its office to another city?) you can have a primary key defined as combination of b_id and c_id.
The same rule applies for the Categories. A new table BusinessCategory with ca_id and b_id, and a Category table with metadata related to category itself.