How could I improve my DB design?

Question

I'm a second year software engineering major looking to create an iOS app that connects to a MySQL database (database not yet created) over the summer. I have a strong understanding of programming and object-oriented design, but have never taken a course in relational databases and don't know much about them at all. I could whip up an OO class diagram for this project in a matter of minutes but I can't seem to map that design to a relational database. This stuff is trickier than I thought.

Background Info About App: Well it's not incredibly complicated. The idea is to make an app that allows skateboarders to upload skateparks, write reviews on them, and discover new parks based on location, user reviews, photos, and other park characteristics. Users sign up for accounts within the app and get a username and password. They can search for nearby parks and filter them. They can bookmark their favorite parks. They can flag both parks and park photos if deemed inappropriate or inaccurate. Park reviews can be voted on as helpful or unhelpful. It's basically just a directory of skateparks w/ reviews and photos consolidated into an iPhone app.

Below is an image of my ERD at this moment in time. I'd appreciate it if you could pinpoint any flaws in the design (sorry for being so vague).

Specific areas I'm struggling with:

1. Do my primary and foreign keys make sense? Should I have an auto-increment ID for each table or is that not necessary?
2. Users need to filter parks by: distance, average rating, popularity (number of ratings and photos), free/paid admission, and types of riders allowed in the park (skateboarder, biker, inline skater, or any combo). Does the way my DB is currently layed out support efficient filtering of parks by these attributes?

Notes:

1. Just pretend that I have a fully functional user database and user_id works to identify unique users. I'm more focused on the park design than the user management design right now.
2. Ignore the relationships between entities. I don't really understand crow's notation at the moment..

Thanks so much!

Answer 1

This might not be the answer but a general tip for designing is to remember that for your initial logical design. A logical relational DB schema is only made up of one-to-many relations. e.g. One Park has many Photos and One User has many photos. That allows for a many-to-many such as; A user may log photos for many parks and a park may have photos logged by many users. These are called business rules. You want to always go for just one-to-many relations in your model of the business rules. Later you might add a few one-to-one entities but rarely. As for many-to-many , just exclude them from your model and represent them as one-to-many-to-one ( i.e. as two one-to-many relations). Which you have done.

Your schema design looks fairly close to reasonable but you have included some one-to-one relations. They are normally not part of a new schema design. They are basically extension tables/entities and done as a way to optimise performance in cases where some fields are rarely used. For now, I would exclude modelling them. But actually in your particular model, I think you just made a mistake with the notation. So you probably want to keep those entities but correct the relation types. e.g. Park-to-Favorite Park should be one-to-many ( 1:M ) . The Park Type to Park should be either 1:M or M:M ( via two 1:M ) , depending on your business rule. Can a Park be of more than one type; I expect a ParkType can be used by several Parks. If so then the relation is 1:M ( ParkType:Park ). If a Park can also be of several types then you'll need another entity and create the relation 1:M:1 via ParkType:ParkTypePark:Park . Now, location is tricky. Example data would help, as you list Address, long, lat as fields I assume that a location record is only usable by exactly one Park. If location is like a region where many Parks can be associated with one Regional Location then the entity makes sense to me (as a 1 Location: M Parks relation ) . But if the Location is very precise so only one Park can ever be there, then you don't need the Location entity, store that data in the Park entity. However, for optimisation you may put it back in if you think that searching for Parks in a Location area would be faster using a separate table with only that in it. But I doubt you need to do that.

Not sure what ParkSubmission is, I'm guessing it is suggestions for new parks. If so, possibly you don't need it and can instead have a status flag on the Park record to show that it is a Suggested, Draft, Approved, Rejected, etc.