Design and create a Schema for a Social database
At the company Panaworld, the CEO wanted to expand the main products. At the moment they were just statically generated web pages about playing mini games with your web browser. For the future his vision was about creating a big community of users, where they could communicate while playing the games. This required to design and create the database schema (MySQL) in order to have the following features at the website:
- Login and User Registration system
- Change Password
- Comments for each game
- Comments at the user profile page
- Gathering insights about what games they played and when
- Like or Dislike the games
- Add to favourite lists
- Activity feed (notifications)
- Profile info
- All the data for the avatar system
- Mutual friendship system
- Friend requests
- Blocking users
- Reporting users
- Top users ranking
- Achievements system
- Recommended games based on what you play
These were the social features, but I had to take into account the basics we previously had at our static generation project, they were the following:
- Data about the games in order to work
- Game Tags
- Categories, subcategories, tree system
While creating this database I had to take into account how we would write the queries at the backend side (php), so I could design the indexes properly, these tables would hold millions of rows in the future. I chose to gather the data in a normalized way, relational database with foreign keys, that way we would have more data to play with in the future.
EER diagram
With Panaworld's permission and censoring the table names for security, I have prepared a image from a spreadsheet where one can see the statistics about this database. As you can see the columns Rows or data_length holds info about how big are these tables today, and we never had performance issues, all because the indexes. The only issue is that indexes take up more space so you will need more disk space or cleaning data for maintenance.
Production info about this database
By the time you read this I don't know if the website will be still up (I hope so) but all the features used with the help of this database are in macrojuegos.com and also in: microgiochi.com minigamers.com microjeux.com macrogames.ru microspiele.com microgry.com microjogos.com, they just use the same schema but we separated the communities for each language.