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:
- Do my primary and foreign keys make sense? Should I have an auto-increment ID for each table or is that not necessary?
- 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?
- 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.
- Ignore the relationships between entities. I don't really understand crow's notation at the moment..
Thanks so much!