Im creating a database for a project with Olympics as my case study. I have drafted a rough table storing information I would like to store and display in my database below (an unnormalised table):
I have drawn up this diagram to understand the basic relationships between my current existing tables (Athletes, Events, Medals & Country). But I also see the need to create a Sports table as well as an Events table as different Sports contain different events. From trying to follow normalisation stages my current database design is looking as such:
Could I ask for some feedback on this structure? I'm having a hard time applying 2NF and 3NF for this database. Please note this is a school project. I'm not asking for any straight up answers. Just a guidance from anyone with a better head on them for databases than I do.
Best Answer
Community wiki answer:
A given athlete can play in multiple sports, so you should probably separate that. Also I do not understand the ranking part attached to countries. This should be at best a view, not a table.
As a general advice, do not store ages for individuals, but date of birth, because this does not change while the age changes each year.
You should just store the result (position) number for each athlete and competition, not medals that will be derived from it.
You also need to take into account ex-aequos. Also, even for a given competition, a given athlete may participate in multiple steps (semi-finals, final, etc.) so you may need to cater for that.