A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

1 to 5 relationship



 
 
Thread Tools Display Modes
  #1  
Old February 20th, 2010, 01:54 PM posted to microsoft.public.access.tablesdbdesign
Loterken
external usenet poster
 
Posts: 12
Default 1 to 5 relationship

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" ?


  #2  
Old February 20th, 2010, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Loterken
external usenet poster
 
Posts: 12
Default 1 to 5 relationship

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" ?


  #3  
Old February 20th, 2010, 07:03 PM posted to microsoft.public.access.tablesdbdesign
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default 1 to 5 relationship

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" wrote in message
...
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" ?




  #4  
Old February 20th, 2010, 08:22 PM posted to microsoft.public.access.tablesdbdesign
Loterken
external usenet poster
 
Posts: 12
Default 1 to 5 relationship

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


  #5  
Old February 20th, 2010, 09:32 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 1 to 5 relationship

On Sat, 20 Feb 2010 13:54:52 +0100, "Loterken" wrote:

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.
--

John W. Vinson [MVP]
  #6  
Old February 21st, 2010, 02:28 AM posted to microsoft.public.access.tablesdbdesign
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default 1 to 5 relationship

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" wrote in message
...
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




  #7  
Old February 22nd, 2010, 11:59 AM posted to microsoft.public.access.tablesdbdesign
Loterken
external usenet poster
 
Posts: 12
Default 1 to 5 relationship

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.