Third Normal Form ERD

I am designing a ERD on the Third Normal Form for a database and I have a problem.

I can't seem to do is join the People table with the Competitions table as for the Third normal form ERD I can't have any Many-To-Many relationships, only 1-to-Many or 1-To-1 and the task that I'm doing states that:

"Any person can enter more than one competition. A recording of which person enters which competition(s) should be kept in the database. The person is then given a number which indicates in which order the people compete in one particular competition."

So in that case: A person can be in MANY competitions and a competition can(has to) have MANY people - Many-To-Many.. Can't figure out how to get that into One-To-Many.

Oh and yes, I am a student and yes this is my coursework. I am not looking for people to do my work for me as I do want to learn, this is only a really small bit of the draft ERD that I have been stuck on for quite alot of time and can't seem to figure out.

I have attached this bit of my draft ERD in a word document any help would be great, thanks!

Comments

Before i start telling you what to do there are one or two things you should take a look at. Like, according to rules table names should be singular. so it would be 'competition' and not 'competitions' etc. . .

You are going to have to make a composite table between people and competitions (this is just another table between the two). Usually the easiest way to name it will be taking the two names of the two many to many relational tables and calling it that. So, for instance you could call it 'person competition'. then you will therefore have a one to many relationship from person to person competition, (ID will therefore become a FK in this new table). and then you will also have a one to many relationship from competitions to person competition and therefore this new table will also have competitionID in it.

This will solve your problem, as the new table will show the competitionID and the person(ID) that takes part in that particular competition.

I dont know what your case study says but unless it says a competition has only one sponsor and a sponsor can sponsor many competitions, then usually a competition can have many sponsors. So you would have a composite table betweeen these two aswell.

Looking at the date you posted this, i guess my reply is a bit late. . .

Before i start telling you what to do there are one or two things you should take a look at. Like, according to rules table names should be singular. so it would be 'competition' and not 'competitions' etc. . .

You are going to have to make a composite table between people and competitions (this is just another table between the two). Usually the easiest way to name it will be taking the two names of the two many to many relational tables and calling it that. So, for instance you could call it 'person competition'. then you will therefore have a one to many relationship from person to person competition, (ID will therefore become a FK in this new table). and then you will also have a one to many relationship from competitions to person competition and therefore this new table will also have competitionID in it.

This will solve your problem, as the new table will show the competitionID and the person(ID) that takes part in that particular competition.

I dont know what your case study says but unless it says a competition has only one sponsor and a sponsor can sponsor many competitions, then usually a competition can have many sponsors. So you would have a composite table betweeen these two aswell.

Looking at the date you posted this, i guess my reply is a bit late. . .

Before i start telling you what to do there are one or two things you should take a look at. Like, according to rules table names should be singular. so it would be 'competition' and not 'competitions' etc. . .

You are going to have to make a composite table between people and competitions (this is just another table between the two). Usually the easiest way to name it will be taking the two names of the two many to many relational tables and calling it that. So, for instance you could call it 'person competition'. then you will therefore have a one to many relationship from person to person competition, (ID will therefore become a FK in this new table). and then you will also have a one to many relationship from competitions to person competition and therefore this new table will also have competitionID in it.

This will solve your problem, as the new table will show the competitionID and the person(ID) that takes part in that particular competition.

I dont know what your case study says but unless it says a competition has only one sponsor and a sponsor can sponsor many competitions, then usually a competition can have many sponsors. So you would have a composite table betweeen these two aswell.

Looking at the date you posted this, i guess my reply is a bit late. . .

Before i start telling you what to do there are one or two things you should take a look at. Like, according to rules table names should be singular. so it would be 'competition' and not 'competitions' etc. . .

You are going to have to make a composite table between people and competitions (this is just another table between the two). Usually the easiest way to name it will be taking the two names of the two many to many relational tables and calling it that. So, for instance you could call it 'person competition'. then you will therefore have a one to many relationship from person to person competition, (ID will therefore become a FK in this new table). and then you will also have a one to many relationship from competitions to person competition and therefore this new table will also have competitionID in it.

This will solve your problem, as the new table will show the competitionID and the person(ID) that takes part in that particular competition.

I dont know what your case study says but unless it says a competition has only one sponsor and a sponsor can sponsor many competitions, then usually a competition can have many sponsors. So you would have a composite table betweeen these two aswell.

Looking at the date you posted this, i guess my reply is a bit late. . .