How to design a MySQL database?

Question

MySQL

Engaged in design. I'm just starting to work seriously with databases. I ask for help and comments on the current structure of the database.
The essence of the database.
There are users with different roles:
- Judge
- Organizer
- Chief Judge
, etc.
Each user has a certification for refereeing and organization. This data is stored in 3 tables: certification_chief , certification_judge , certification_organizer The organizer creates an event in the event
table .
After that, the organizer appoints judges for the event and the chief judge later and other staff. However, it can only assign those who have a valid certification from the tables certification_chief , certification_judge , certification_organizer 4 entries in the tables) 3. Approximately 200 events take place per year. 4. An event is assigned an average of 10 people or about 20 categories 200 x 20 = ~ 4000 appointment entries per year 1. Please point out obvious errors and flaws.

2. Most likely, creating several tables of the same type is stupid, but the database can grow. Grows by 500 records per month. I think this is nonsense, given that there are mainly only numbers (ID)
3. Please comment on any little thing) I don’t want to make mistakes and alterations in the future

Answer 1

1 and you even understand it, but for some reason you did it anyway. The same data is stored in one table, not three different ones. If the data is almost the same, but has 1 sign, different - it is better to make it as a field in the table. Just add, for example, a field with a role designation to the certificate table. Get rid of a bunch of difficulties in the future.
For MySQL and 1 million records is not a problem, if that. You are not trying to save money on those hamsters.

Answer 2

Look at buddypress, there are exactly the same starting data regarding your pseudo-table - there is such a piece there, it is implemented not on 9, but on 4 tables !!))
I.e. take buddypress, run it with demo data and unload the database dump into any constructor, then carefully and thoughtfully study the generated block diagram.
Or search Google for a flowchart, there probably is ....
Buddipress karoch. That's all you need to know about roles.