Background
I am developing a social web app for poets and writers, allowing them to share their poetry, gather feedback, and communicate with other poets. I have very little formal training in database design, but I have been reading books, SO, and online DB design resources in an attempt to ensure performance and scalability without over-engineering.
The database is MySQL, and the application is written in PHP. I'm not sure yet whether we will be using an ORM library or writing SQL queries from scratch in the app. Other than the web application, Solr search server and maybe some messaging client will interact with the database.
Current Needs
The schema I have thrown together below represents the primary components of the first version of the website. Initially, users can register for the site and do any of the following:
- Create and modify profile details and account settings
- Post, tag and categorize their writing
- Read, comment on and "favorite" other users' posts
- "Follow" other users to get notifications of their activity
- Search and browse content and get suggested posts/users (though we will be using the Solr search server to index DB data and run these type of queries)
Schema
Here is what I came up with on MySQL Workbench for the initial site. I'm still a little fuzzy on some relational databasey things, so go easy.
Questions
- In general, is there anything I'm doing wrong or can improve upon?
- Is there any reason why I shouldn't combine the ExternalAccounts table into the UserProfiles table?
- Is there any reason why I shouldn't combine the PostStats table into the Posts table?
- Should I expand the design to include the features we are doing in the second version just to ensure that the initial schema can support it?
- Is there