1 to 5 relationship

[Loterken]

I have 2 tables
table "Person" and table "Team" with a 1-M relationship
a person can be a member of 0 or 1 team
but a team can have from 0 to 5 Persons
at the moment i am enforcing this with code
is there any way to enforce this with a constraint on table "Team" ?

 

[Loterken]

sorry, i meant
is there any way to enforce this with a constraint on table "Person" ?
and not
is there any way to enforce this with a constraint on table "Team" ?

 

[Al Campagna]

Loterken,
Logically, tblTeam should be the ONE, tblPersons should be the MANY.
One team, many persons...
Given that a Person can belong to only one team... tblTeam PersonID
should be constrained (Indexed/No Dupes) to prevent assignment to two teams.
Also, the most logical form would be tblTeam on the main form,
and tblPersons on a continous subform related to the Team by TeamID.

If you look at it as One Person to One Team, the tblPersons TeamID can
not
be constrained (No Dupes) ... as other persons (in that same tblPersons)
could also belong to that same team.

Is that what you're trying to determine?
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Loterken]

indeed i mistyped on my original post, corrected on the reply to myself
team is the 1 side, person is the many sides
so team has only 1 field (ID), the primary key (of course there are more
fields)
person has among others a field (TeamID), a foreign key
the problem is,that allows a team to have a whole lot of persons
but a team may not have more than 5 persons
i solved it by not allowing the user to give the same team to more than 5
users
i did that by using code in my program (vb6)
i am wondering if there is a way to have a constraint on the person table
that would not allow more then 5 teamID
i thougt of putting 5 person fields in the team table, but that would
violate 1NF

 

[John W. Vinson]

I have 2 tables
table "Person" and table "Team" with a 1-M relationship

Well... no. I think you have it backward. It's a many to one relationship -
each Person has no more than one Team, each Team can have more than one
Person.

a person can be a member of 0 or 1 team
but a team can have from 0 to 5 Persons
at the moment i am enforcing this with code
is there any way to enforce this with a constraint on table "Team" ?

Teams
TeamID
TeamName
<other information about the team as a ding an sich>

Persons
PersonID <primary key>
TeamID <foreign key>
<biographical data about the person>

There's no table validation rule that will limit the team to no more than five
members; when Access gets table triggers you can use a BeforeInsert trigger to
detect the sixth addition and prevent it, but with Access as it is in version
2007, you need code in the Form's BeforeInsert event to count the existing
number of team members and block the addition if the team is full.

 

[Al Campagna]

Check out John's response... regarding using the BeforeUpdate event
to prevent a sixth entry per team.
There is no property that will limit a table to five entries per team.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Loterken]

Al,john, thank you
so i'll leave it as it is,and put a discleamer on it, that i dont assume
responsability for correctnes if the users input data outside of my program
since i am a strong believer that a users data is property of the user,not
of the programmer,i dont like to secure a db with pasword or user level
thank you guys