Does my entity-relationship diagram reflect the relevant business rules?

Question

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:

1. Users can rate multiple businesses
2. A business can fall under more than one category
3. Users can comment on multiple businesses
4. Businesses can have zero to many ratings
5. Businesses can have zero to many comments
6. A business can only be located in one and only one city
7. A city can have multiple businesses
8. A category can have multiple businesses
9. Users can have one or more checkins for businesses
10. Businesses can have zero to many checkins

My entity-relationship diagram

And I have represented them in the diagram below:

Answer 1

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.

Answer 2

First - I agree with S4V1N completely on the Category-Business link - you need a table in between, as each business can have many categories, and each category could be tied to many businesses.

However, as the rules are currently stated, I don't think you need a table between City and Business. I think you've got City laid out wrong.

Currently, each business must be in one and only one city, and each city can be home to multiple businesses. For that, the City table should not have a B_ID column, as a city is tied to no one business. (I also see no reason for it to have a U_ID column, as no relationship between User and City has been established. I suspect a cut-and-paste issue.) Business, however, should have a CI_ID for the city it's in.

Also - the way I read the diagram, it looks like each user must have at least one Comment, Rating, and Checkin. The rules explicitly indicate "one or more" checkins per user, so that appears correct. However, it doesn't look to me like the user is required to have any comments or ratings - so shouldn't those two be linked with the "0 or more" connector?