This brief article shall give some advice on how to start your database project regarding data model and a way to think it through.
The first thing you need to make sure is that you know what your application shall be able to do. Having thought about that, you can decide for your data structure. Try to think of the following:
- What are the main entities my database is dealing with?
- What are the relationships between those entities?
- Do I need additional relationship tables?
- What are the main attributes of both my entities and my relationships?
You can use an Entity Relationship Modeling tool or just a sheet of paper — it depends on how complex your database is.
Afterwards, decide for a unique primary key for each of your entities.
Now I would recommend to sort of “emulate” all your database tables and all your attributes including primary keys using excel or yet again a sheet of paper. Visualize your tables and fill them with example data. That way you can be sure not to miss something important. When that is done, think about the kind of data your primary key consists of. Is it data you get from a different system, where you can be sure that every new entry has a different key value, or do you want to let it be filled automatically using auto_increment.
Finally, build your data model using create table statements.