3 one to many relationship forms design

[herbertioz]

2 one to many relationship forms design

Hi all!
I want some advice from you.

I have 3 tables:
Table 1: Competition

Table 2: Athletes

Tables 3: Team

A team can have 1 or more athletes
A competition can have 1 or more athletes
A competition can have 1 or more teams.

When I started to design the form, It is simple with 1 one to many relationship, but when I try to use all together it fails.

I will have the main competition where you can choose the competitions, with the athletes underneath. But what about the teams? The reliationship between competiition and athletes does work fine, but not the athletes and teams. Some tips?

 

[DCrake]

You need many to many relationships. Think about the Divisions

Divisions = Div1, Div2, Div3, etc

Teams = TeamA, TeamB, TeamC, etc

TeamA = PlayerA, PlayerB,PlayerC, Etc

Table 1 = list of competitions
Table 2 = list of players
Table 3 = List of teams

Now look at player A

PlayerA at present plays for TeamA in Div1

He then get sold and and plays for TeamB in Div2

So player A has links to both Div1 and Div 2 and TeamA and TeamB

So if you did a search on player A you would find out which teams he played for in which divisions

Likewise if you searched by division you would see that Player A played for Team A in Div 1 and teamb in Div 2

hope that explains it better.

 

[herbertioz]

Hmm, I have learned that you must try to use one to many instead. Thats because it can be too complicated design, normalization and so on...

Do you have a example with many to many with my tables?

Thanks for help

You need many to many relationships. Think about the Divisions

Divisions = Div1, Div2, Div3, etc

Teams = TeamA, TeamB, TeamC, etc

TeamA = PlayerA, PlayerB,PlayerC, Etc

Table 1 = list of competitions
Table 2 = list of players
Table 3 = List of teams

Now look at player A

PlayerA at present plays for TeamA in Div1

He then get sold and and plays for TeamB in Div2

So player A has links to both Div1 and Div 2 and TeamA and TeamB

So if you did a search on player A you would find out which teams he played for in which divisions

Likewise if you searched by division you would see that Player A played for Team A in Div 1 and teamb in Div 2

hope that explains it better.

 

[herbertioz]

Someone with some examples, please?

Hmm, I have learned that you must try to use one to many instead. Thats because it can be too complicated design, normalization and so on...

Do you have a example with many to many with my tables?

Thanks for help

 

[herbertioz]

Here is my new design. I think this is better design and it works now, but not in my forms. Can someone help me with the form design? I would like to have 2 subforms with one to many.

Someone with some examples, please?