Looks like a simple design with a pit table and a match table as the center pieces will be sufficient. We'll use competition and team mapping tables as reference data. One requirement is to know the status of pit reporting; which pit reports have not started yet. This means we need to know which teams participate in which competitions. This is a many-to-many relationship. Therefore a mapping table would make sense.
Here is a proposal showing the ER diagram and a small set of test tables and simple test data to confirm the queries.
[Note that usually an automatic mapping_id is not needed for mapping tables but since we will refer to the mapping (not just use the table to ensure data consistencey) from pit and match tables so using a mapping_id makes sense.]
Looks like a simple design with a pit table and a match table as the center pieces will be sufficient. We'll use competition and team mapping tables as reference data. One requirement is to know the status of pit reporting; which pit reports have not started yet. This means we need to know which teams participate in which competitions. This is a many-to-many relationship. Therefore a mapping table would make sense.
Here is a proposal showing the ER diagram and a small set of test tables and simple test data to confirm the queries.
Database_Mapping.pdf
[Note that usually an automatic mapping_id is not needed for mapping tables but since we will refer to the mapping (not just use the table to ensure data consistencey) from pit and match tables so using a mapping_id makes sense.]