Does my entity-relationship diagram reflect the relevant business rules? 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:
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?