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  

Complicated Databse w/many relationships



 
 
Thread Tools Display Modes
  #1  
Old July 11th, 2004, 11:51 PM
Søren
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

Hi,

I've had a look around in some of the questions here and especially the ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little sparring and couching! I am trying to create a database with a lot of contact information for the 732 members of the European Parliament. Status qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including street, city, ZIP, country, region, counstituency, phone and fax numbers, office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND max 3 EP Delegation AND 3 other bodies in EP AND a political group in the EP AND their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections (positions) - or the same, e.g. a MEP is always a Member of the European Parliament, and a member OR Chariman OR Vice-Chairman of a Political Group; the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at least 2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs to this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body, Home Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID & EP Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP Delegation.EPDelegationID) to create a Many-to-Many relationship; the relationship is one-to-many from MEP to EP Committeeship and many-to-one from EP Committeeship to EP Committee, which again have many-to-one to Position
4- I have direct one-to-many connections between MEP and EU Instituion, MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and fieldnames) in each table e.g. PositionID is key in Position and linked to a numeric field with the same name in all related tables, EPDelegationID is key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the tables mentioned above under 2.

Could someone give me a guidance through the this hurricane of relations and table?

Thanks very much in advance!


BR, Søren
  #2  
Old July 12th, 2004, 02:06 AM
Armen Stein
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

In article ,=20
says...
Hi,
=20
I've had a look around in some of the questions here and especially the o=

nes from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little spa=
rring and couching! I am trying to create a database with a lot of contact =
information for the 732 members of the European Parliament. Status qou is:
=20
I have 732 MEPs each with
=20
- 3 addresses (2x offices in Brussels & Strasbourg and Home) including st=

reet, city, ZIP, country, region, counstituency, phone and fax numbers, off=
ice locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND max=

3 EP Delegation AND 3 other bodies in EP AND a political group in the EP A=
ND their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections (posi=

tions) - or the same, e.g. a MEP is always a Member of the European Parliam=
ent, and a member OR Chariman OR Vice-Chairman of a Political Group; the ME=
P is also always a Member/Subsitute/Chairman/Vice-Chairman of at least 2-3 =
Committees AND 2-3 Delegations.
=20
Example:
=20
the MEP is =09Member of the European Parliament
=09=09Member of the Green Group
=09=09Chairman of the Environmental Committee
=09=09Member of the Transport Committee
=09=09Subsitute of the Energy Committee
=09=09Vice-Chairman of the Delegation A
=09=09Member of the Delegation B
=09=09Member of another body under the EU Instituion
=20
My setup now is
=20
1- a MEP table which include all fields i.d. fields that only belongs to =

this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body, Hom=

e Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID & EP=

Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP Delegatio=
n.EPDelegationID) to create a Many-to-Many relationship; the relationship i=
s one-to-many from MEP to EP Committeeship and many-to-one from EP Committe=
eship to EP Committee, which again have many-to-one to Position
4- I have direct one-to-many connections between MEP and EU Instituion, M=

EP and Home Country, MEP and Political Group, MEP and National Party
=20
All are liked via numeric autonumber ID fields with unique values (and fi=

eldnames) in each table e.g. PositionID is key in Position and linked to a =
numeric field with the same name in all related tables, EPDelegationID is k=
ey in EP Delegation etc.
=20
I have made lookup fields in the MEP table that refers to all the tables =

mentioned above under 2.
=20
Could someone give me a guidance through the this hurricane of relations =

and table?
=20
Thanks very much in advance!
=20
=20
BR, S=C3?ren
=20


Hi,

From what you've described, your table structure seems reasonable, and=20
not overly complex.

I might recommend one change: make all your Committee, Delegations and=20
Groups one table, called something generic like tblGroup. This table=20
will have a lookup to a group type table (Committee, Delegation, etc.)=20
and a name ("Green Group", "Transport Committee").=20

Then, you can track all the memberships of all the different kinds of=20
groups in one membership table, called Member or MEPGroup. This table=20
will be the many-to-many table linking MEPs and Groups, and will also=20
have a lookup to Position (Chairman, Member, etc.)

This structure will allow you to add other types of groups in the future=20
without affected your database structure.

Hope this helps,

--=20
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
  #3  
Old July 12th, 2004, 02:42 AM
tina
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

well, i don't know if i'm the Tina you mentioned but here's my two cents
worth. right off the bat, i would get rid of the Lookup fields in the
tables. for an explanation of why, see the link below:

http://www.mvps.org/access/lookupfields.htm

another very helpful link when getting started is

http://www.mvps.org/access/tencommandments.htm

it's written tongue-in-cheek, but gives serious and very useful advice. as
always, i recommend that you look around in the mvps.org website; it's a
fantastic resource generated by some of the best Access developers in the
world, many of whom also donate time to the newsgroups, and it's free to
everyone.



i know zilch about the EP, and politics in general, so i had to guess about
some things. in general, here's how I organize my data:

i start with the main subject, in this case *members* of the EP. anything
that the main subject has only one of, such as a name, goes in the main data
table - in this case tblMembers.

anything that the main subject *may* have more than one of, goes in a child
data table linked to the main table. the child data table contains data
about a certain subject - such as tblMemberAddresses. of course there may be
multiple child data tables.

any descriptive or categorizing data that has standard values, goes in a
"supporting" (lookup) table - such as a table of Countries, a table of
Cities, etc. that supporting table is used to populate the particular field
in the data table - such as Country, in tblMemberAddresses.

below is a possible table setup. you'll have to apply the guidelines above
to determine where i may have gone wrong due to not understanding various
terms used in your post.



tblMembers (data table)

MemberID (primary key)

FirstName

LastName

NationalParty

Position

Title

Constituency

(i assumed that national party is the political party of the country the
member is a citizen of, such as Democratic/Republican parties in the U.S.
if, instead, it has "standard" values, then I would use the primary key from
a supporting table to populate the field as a foreign key.

also, if position and title have "standard" values, then i would use a
supporting table as described above. Note: i have no idea what
"constituency" refers to. if it is specific to an address, then move it to
tblMemberAddresses. if the member may have multiple constituencies, not
specific to an address, then make a separate table for constituencies, with
a foreign key from tblMembers.)



tblMemberAddresses (data table; 1-n relationship with tblMembers)

MemAddID (primary key)

MemberID (foreign key from tblMembers)

Street

City (possibly a foreign key from a supporting table of cities)

Zip

CountryID (foreign key from tblCountries)

Region (possibly a foreign key from a supporting table of regions)



tblCountries (supporting table; 1-n relationship with tblMemberAddresses)

CountryID (primary key)

CountryName



tblAddressPhones (data table; 1-n relationship with tblMemberAddresses)

AddPhID (primary key)

MemAddID (foreign key from tblMemberAddresses)

PhoneTypeID (foreign key from tblPhoneTypes)

PhoneNumber (text field)

Comments

(usually, i use this table to list email addresses and websites, as well as
phone numbers - since they're all "ways to contact the main subject". but in
this case, you have multiple addresses. phone and fax numbers are probably
specific to each address, which is why i used the foreign key from
tblMemberAddresses, rather than the foreign key from tblMembers.

*but*, an email address might not be specific to a certain street address.
if each Member has only one email address, i would put it in tblMembers. if
they may have many, that are not specific to a street address, i would make
a separate table for email addresses, with a foreign key from tblMembers.

the same considerations apply to website URL.)



tblPhoneTypes (supporting table; 1-n relationship with tblAddressPhones)

PhoneTypeID (primary key)

PhoneTypeName

(names such as Phone, Fax, Cell, possibly Email and/or URL, etc.)



tblBodyTypes (supporting table; 1-n relationship with tblBodies)

BodyTypeID (primary key)

BodyTypeName

(names such as Committee, Delegation, Political Group, etc)



tblBodies (supporting table; 1-n relationship with tblMemberPositions)

BodyID (primary key)

BodyTypeID (foreign key from tblBodyTypes)

BodyName



tblPositions (supporting table; 1-n relationship with tblMemberPositions)

PositionID (primary key)

PositionName

(names such as Member, Substitute, Chairman, Vice-Chairman, etc)



tblMemberPositions (data table; 1-n relationship with tblMembers)

MemPosID (primary key)

MemberID (foreign key from tblMembers)

BodyID (foreign key from tblBodies)

PositionID (foreign key from tblPositions)



remember to *not* use Access reserved words as field names, such as Name,
Date, etc. and recommend you use only letters and underscores ( _ ) in your
table/field names; you can use numbers if you must, but i try to avoid it.


hth


"Søren" wrote in message
...
Hi,

I've had a look around in some of the questions here and especially the

ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little
sparring and couching! I am trying to create a database with a lot of
contact information for the 732 members of the European Parliament. Status
qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including

street, city, ZIP, country, region, counstituency, phone and fax numbers,
office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND max

3 EP Delegation AND 3 other bodies in EP AND a political group in the EP AND
their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections

(positions) - or the same, e.g. a MEP is always a Member of the European
Parliament, and a member OR Chariman OR Vice-Chairman of a Political Group;
the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at least
2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs to

this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body, Home

Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID & EP

Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP
Delegation.EPDelegationID) to create a Many-to-Many relationship; the
relationship is one-to-many from MEP to EP Committeeship and many-to-one
from EP Committeeship to EP Committee, which again have many-to-one to
Position
4- I have direct one-to-many connections between MEP and EU Instituion,

MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and

fieldnames) in each table e.g. PositionID is key in Position and linked to a
numeric field with the same name in all related tables, EPDelegationID is
key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the tables

mentioned above under 2.

Could someone give me a guidance through the this hurricane of relations

and table?

Thanks very much in advance!


BR, Søren



  #4  
Old July 12th, 2004, 07:42 AM
tina
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

btw, sorry for the double-spacing in the tables and multiple spaces between
paragraphs - makes it harder to read, i know! i didn't write the post that
way, must be some freaky thing that Outlook Express did to it.


"tina" wrote in message
...
well, i don't know if i'm the Tina you mentioned but here's my two cents
worth. right off the bat, i would get rid of the Lookup fields in the
tables. for an explanation of why, see the link below:

http://www.mvps.org/access/lookupfields.htm

another very helpful link when getting started is

http://www.mvps.org/access/tencommandments.htm

it's written tongue-in-cheek, but gives serious and very useful advice. as
always, i recommend that you look around in the mvps.org website; it's a
fantastic resource generated by some of the best Access developers in the
world, many of whom also donate time to the newsgroups, and it's free to
everyone.



i know zilch about the EP, and politics in general, so i had to guess

about
some things. in general, here's how I organize my data:

i start with the main subject, in this case *members* of the EP. anything
that the main subject has only one of, such as a name, goes in the main

data
table - in this case tblMembers.

anything that the main subject *may* have more than one of, goes in a

child
data table linked to the main table. the child data table contains data
about a certain subject - such as tblMemberAddresses. of course there may

be
multiple child data tables.

any descriptive or categorizing data that has standard values, goes in a
"supporting" (lookup) table - such as a table of Countries, a table of
Cities, etc. that supporting table is used to populate the particular

field
in the data table - such as Country, in tblMemberAddresses.

below is a possible table setup. you'll have to apply the guidelines above
to determine where i may have gone wrong due to not understanding various
terms used in your post.



tblMembers (data table)

MemberID (primary key)

FirstName

LastName

NationalParty

Position

Title

Constituency

(i assumed that national party is the political party of the country the
member is a citizen of, such as Democratic/Republican parties in the U.S.
if, instead, it has "standard" values, then I would use the primary key

from
a supporting table to populate the field as a foreign key.

also, if position and title have "standard" values, then i would use a
supporting table as described above. Note: i have no idea what
"constituency" refers to. if it is specific to an address, then move it to
tblMemberAddresses. if the member may have multiple constituencies, not
specific to an address, then make a separate table for constituencies,

with
a foreign key from tblMembers.)



tblMemberAddresses (data table; 1-n relationship with tblMembers)

MemAddID (primary key)

MemberID (foreign key from tblMembers)

Street

City (possibly a foreign key from a supporting table of cities)

Zip

CountryID (foreign key from tblCountries)

Region (possibly a foreign key from a supporting table of regions)



tblCountries (supporting table; 1-n relationship with tblMemberAddresses)

CountryID (primary key)

CountryName



tblAddressPhones (data table; 1-n relationship with tblMemberAddresses)

AddPhID (primary key)

MemAddID (foreign key from tblMemberAddresses)

PhoneTypeID (foreign key from tblPhoneTypes)

PhoneNumber (text field)

Comments

(usually, i use this table to list email addresses and websites, as well

as
phone numbers - since they're all "ways to contact the main subject". but

in
this case, you have multiple addresses. phone and fax numbers are probably
specific to each address, which is why i used the foreign key from
tblMemberAddresses, rather than the foreign key from tblMembers.

*but*, an email address might not be specific to a certain street address.
if each Member has only one email address, i would put it in tblMembers.

if
they may have many, that are not specific to a street address, i would

make
a separate table for email addresses, with a foreign key from tblMembers.

the same considerations apply to website URL.)



tblPhoneTypes (supporting table; 1-n relationship with tblAddressPhones)

PhoneTypeID (primary key)

PhoneTypeName

(names such as Phone, Fax, Cell, possibly Email and/or URL, etc.)



tblBodyTypes (supporting table; 1-n relationship with tblBodies)

BodyTypeID (primary key)

BodyTypeName

(names such as Committee, Delegation, Political Group, etc)



tblBodies (supporting table; 1-n relationship with tblMemberPositions)

BodyID (primary key)

BodyTypeID (foreign key from tblBodyTypes)

BodyName



tblPositions (supporting table; 1-n relationship with tblMemberPositions)

PositionID (primary key)

PositionName

(names such as Member, Substitute, Chairman, Vice-Chairman, etc)



tblMemberPositions (data table; 1-n relationship with tblMembers)

MemPosID (primary key)

MemberID (foreign key from tblMembers)

BodyID (foreign key from tblBodies)

PositionID (foreign key from tblPositions)



remember to *not* use Access reserved words as field names, such as Name,
Date, etc. and recommend you use only letters and underscores ( _ ) in

your
table/field names; you can use numbers if you must, but i try to avoid it.


hth


"Søren" wrote in message
...
Hi,

I've had a look around in some of the questions here and especially the

ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little
sparring and couching! I am trying to create a database with a lot of
contact information for the 732 members of the European Parliament. Status
qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including

street, city, ZIP, country, region, counstituency, phone and fax numbers,
office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND

max
3 EP Delegation AND 3 other bodies in EP AND a political group in the EP

AND
their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections

(positions) - or the same, e.g. a MEP is always a Member of the European
Parliament, and a member OR Chariman OR Vice-Chairman of a Political

Group;
the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at

least
2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs to

this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body,

Home
Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID &

EP
Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP
Delegation.EPDelegationID) to create a Many-to-Many relationship; the
relationship is one-to-many from MEP to EP Committeeship and many-to-one
from EP Committeeship to EP Committee, which again have many-to-one to
Position
4- I have direct one-to-many connections between MEP and EU Instituion,

MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and

fieldnames) in each table e.g. PositionID is key in Position and linked to

a
numeric field with the same name in all related tables, EPDelegationID is
key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the tables

mentioned above under 2.

Could someone give me a guidance through the this hurricane of relations

and table?

Thanks very much in advance!


BR, Søren





  #5  
Old July 12th, 2004, 05:44 PM
Søren
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

Yes - you are the Tina I mentioned; thanks for the input! Basicly all of your assumptions were right; a constituency is the geographical area where a person is standing as a Parliamentarian Candidate - e.g. a US Senator's constituency would be the state in which he is running for election.

Unfortunately I still have some unanswer questions...

Status quo is I have now changed the lay-out of the table structure to the following:

tblMEP (your tblMembers) - I have removed the lookup fields but kept the info in standard text fields (is that smart?). I have kept all the address fields (offices, home), contact details (2 e-mails and 2 websites) in this table as well as Constituency, Suffix, Birthsday, Notes, MemberSince. I guess that is OK.

The table also have fields like EUInstituion, EPCommitte1, EPCommitte2, EPCommitte3 (same for EPDelegation and OtherBodies), NationalParty, PoliticalGroup. How do I connect these fields to the respective support tables?

tblNational Party holds 146 entries with standard values (national party names as spelled in the specific country) and is linked to tblMEP in a 1-n because 1 MEP can only be connected to one National Party at a time.

tblHomeCountry holds 25 standard entries (UK, France, Denmark etc) and is linked to tblMEP in a 1-n (only one country at a time)

tblTitle holds many standard entries and is linked totblMEP in a 1-n because you can only be a Frau, Signora or Madame etc at one point in time...

I have created a tblEntityType (your tblBodyTypes), a tblEntityName (tblBodies), tbl Membership (tblMemberPositions), and I already had a tblPosition (tblPositions). I have linked them as you suggested.

tblMembership is linked to tblMEP in a 1-n and again respectively linked to tblEntityName and tblPosition n-1.

tblEntityName is again linked to tblEntityType in a n-1.

Now the questions is: How does it work? There are no data in the MembershipID in tblMEP (FYI: I have already, before I posted the first question, put all details of the 732 MEPs into the database).

I have also constructed a form based on the old design - how do I get access to fields from the new tables? I guess I have to because now I cannot get access to the Entity info unless I use the static unlinked fields I still have in tblMEP - and such a solution seems a little stupid to me...

How does the link between membership type and MEP work? I mean, since each MEP can have several connections (Member/Substitute/Chairman etc) to different Committees, Delegations, Other Bodies, AND always has a memberstatus like e.g. Member/Chairman/Vice-Chairman of a Political Group AND is a Member of a National Party AND a Member of the EP... How does I seperate all this info and get it into a form...?

Tina, if you can get me through this database labyrinth I shall buy you are 3 star Michelin dinner next time you're in Brussels! I am reachable at ¤ or you can skype me under username gravesen (if you do not know Skype see www.skype.com).

Thanks! - Søren

"tina" wrote:

well, i don't know if i'm the Tina you mentioned but here's my two cents
worth. right off the bat, i would get rid of the Lookup fields in the
tables. for an explanation of why, see the link below:

http://www.mvps.org/access/lookupfields.htm

another very helpful link when getting started is

http://www.mvps.org/access/tencommandments.htm

it's written tongue-in-cheek, but gives serious and very useful advice. as
always, i recommend that you look around in the mvps.org website; it's a
fantastic resource generated by some of the best Access developers in the
world, many of whom also donate time to the newsgroups, and it's free to
everyone.



i know zilch about the EP, and politics in general, so i had to guess about
some things. in general, here's how I organize my data:

i start with the main subject, in this case *members* of the EP. anything
that the main subject has only one of, such as a name, goes in the main data
table - in this case tblMembers.

anything that the main subject *may* have more than one of, goes in a child
data table linked to the main table. the child data table contains data
about a certain subject - such as tblMemberAddresses. of course there may be
multiple child data tables.

any descriptive or categorizing data that has standard values, goes in a
"supporting" (lookup) table - such as a table of Countries, a table of
Cities, etc. that supporting table is used to populate the particular field
in the data table - such as Country, in tblMemberAddresses.

below is a possible table setup. you'll have to apply the guidelines above
to determine where i may have gone wrong due to not understanding various
terms used in your post.



tblMembers (data table)

MemberID (primary key)

FirstName

LastName

NationalParty

Position

Title

Constituency

(i assumed that national party is the political party of the country the
member is a citizen of, such as Democratic/Republican parties in the U.S.
if, instead, it has "standard" values, then I would use the primary key from
a supporting table to populate the field as a foreign key.

also, if position and title have "standard" values, then i would use a
supporting table as described above. Note: i have no idea what
"constituency" refers to. if it is specific to an address, then move it to
tblMemberAddresses. if the member may have multiple constituencies, not
specific to an address, then make a separate table for constituencies, with
a foreign key from tblMembers.)



tblMemberAddresses (data table; 1-n relationship with tblMembers)

MemAddID (primary key)

MemberID (foreign key from tblMembers)

Street

City (possibly a foreign key from a supporting table of cities)

Zip

CountryID (foreign key from tblCountries)

Region (possibly a foreign key from a supporting table of regions)



tblCountries (supporting table; 1-n relationship with tblMemberAddresses)

CountryID (primary key)

CountryName



tblAddressPhones (data table; 1-n relationship with tblMemberAddresses)

AddPhID (primary key)

MemAddID (foreign key from tblMemberAddresses)

PhoneTypeID (foreign key from tblPhoneTypes)

PhoneNumber (text field)

Comments

(usually, i use this table to list email addresses and websites, as well as
phone numbers - since they're all "ways to contact the main subject". but in
this case, you have multiple addresses. phone and fax numbers are probably
specific to each address, which is why i used the foreign key from
tblMemberAddresses, rather than the foreign key from tblMembers.

*but*, an email address might not be specific to a certain street address.
if each Member has only one email address, i would put it in tblMembers. if
they may have many, that are not specific to a street address, i would make
a separate table for email addresses, with a foreign key from tblMembers.

the same considerations apply to website URL.)



tblPhoneTypes (supporting table; 1-n relationship with tblAddressPhones)

PhoneTypeID (primary key)

PhoneTypeName

(names such as Phone, Fax, Cell, possibly Email and/or URL, etc.)



tblBodyTypes (supporting table; 1-n relationship with tblBodies)

BodyTypeID (primary key)

BodyTypeName

(names such as Committee, Delegation, Political Group, etc)



tblBodies (supporting table; 1-n relationship with tblMemberPositions)

BodyID (primary key)

BodyTypeID (foreign key from tblBodyTypes)

BodyName



tblPositions (supporting table; 1-n relationship with tblMemberPositions)

PositionID (primary key)

PositionName

(names such as Member, Substitute, Chairman, Vice-Chairman, etc)



tblMemberPositions (data table; 1-n relationship with tblMembers)

MemPosID (primary key)

MemberID (foreign key from tblMembers)

BodyID (foreign key from tblBodies)

PositionID (foreign key from tblPositions)



remember to *not* use Access reserved words as field names, such as Name,
Date, etc. and recommend you use only letters and underscores ( _ ) in your
table/field names; you can use numbers if you must, but i try to avoid it.


hth


"Søren" wrote in message
...
Hi,

I've had a look around in some of the questions here and especially the

ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little
sparring and couching! I am trying to create a database with a lot of
contact information for the 732 members of the European Parliament. Status
qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including

street, city, ZIP, country, region, counstituency, phone and fax numbers,
office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND max

3 EP Delegation AND 3 other bodies in EP AND a political group in the EP AND
their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections

(positions) - or the same, e.g. a MEP is always a Member of the European
Parliament, and a member OR Chariman OR Vice-Chairman of a Political Group;
the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at least
2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs to

this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body, Home

Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID & EP

Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP
Delegation.EPDelegationID) to create a Many-to-Many relationship; the
relationship is one-to-many from MEP to EP Committeeship and many-to-one
from EP Committeeship to EP Committee, which again have many-to-one to
Position
4- I have direct one-to-many connections between MEP and EU Instituion,

MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and

fieldnames) in each table e.g. PositionID is key in Position and linked to a
numeric field with the same name in all related tables, EPDelegationID is
key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the tables

mentioned above under 2.

Could someone give me a guidance through the this hurricane of relations

and table?

Thanks very much in advance!


BR, Søren




  #6  
Old July 12th, 2004, 05:49 PM
Søren
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

Thanks for your suggestion! Please see the post under Tina - I think I for all practical purposes have implemented your suggestion via the suggestions I also received from Tina. Thanks again! BR Søren

"Armen Stein" wrote:

In article ,
says...
Hi,

I've had a look around in some of the questions here and especially the ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little sparring and couching! I am trying to create a database with a lot of contact information for the 732 members of the European Parliament. Status qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including street, city, ZIP, country, region, counstituency, phone and fax numbers, office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND max 3 EP Delegation AND 3 other bodies in EP AND a political group in the EP AND their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections (positions) - or the same, e.g. a MEP is always a Member of the European Parliament, and a member OR Chariman OR Vice-Chairman of a Political Group; the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at least 2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs to this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body, Home Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID & EP Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP Delegation.EPDelegationID) to create a Many-to-Many relationship; the relationship is one-to-many from MEP to EP Committeeship and many-to-one from EP Committeeship to EP Committee, which again have many-to-one to Position
4- I have direct one-to-many connections between MEP and EU Instituion, MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and fieldnames) in each table e.g. PositionID is key in Position and linked to a numeric field with the same name in all related tables, EPDelegationID is key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the tables mentioned above under 2.

Could someone give me a guidance through the this hurricane of relations and table?

Thanks very much in advance!


BR, SÃ?ren


Hi,

From what you've described, your table structure seems reasonable, and
not overly complex.

I might recommend one change: make all your Committee, Delegations and
Groups one table, called something generic like tblGroup. This table
will have a lookup to a group type table (Committee, Delegation, etc.)
and a name ("Green Group", "Transport Committee").

Then, you can track all the memberships of all the different kinds of
groups in one membership table, called Member or MEPGroup. This table
will be the many-to-many table linking MEPs and Groups, and will also
have a lookup to Position (Chairman, Member, etc.)

This structure will allow you to add other types of groups in the future
without affected your database structure.

Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

  #7  
Old July 12th, 2004, 07:59 PM
tina
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

hi Soren. my schedule is full this afternoon. i'll give your comments some
thought and post a reply to you this evening 7/12. tina


"Søren" wrote in message
...
Yes - you are the Tina I mentioned; thanks for the input! Basicly all of

your assumptions were right; a constituency is the geographical area where a
person is standing as a Parliamentarian Candidate - e.g. a US Senator's
constituency would be the state in which he is running for election.

Unfortunately I still have some unanswer questions...

Status quo is I have now changed the lay-out of the table structure to the

following:

tblMEP (your tblMembers) - I have removed the lookup fields but kept the

info in standard text fields (is that smart?). I have kept all the address
fields (offices, home), contact details (2 e-mails and 2 websites) in this
table as well as Constituency, Suffix, Birthsday, Notes, MemberSince. I
guess that is OK.

The table also have fields like EUInstituion, EPCommitte1, EPCommitte2,

EPCommitte3 (same for EPDelegation and OtherBodies), NationalParty,
PoliticalGroup. How do I connect these fields to the respective support
tables?

tblNational Party holds 146 entries with standard values (national party

names as spelled in the specific country) and is linked to tblMEP in a 1-n
because 1 MEP can only be connected to one National Party at a time.

tblHomeCountry holds 25 standard entries (UK, France, Denmark etc) and is

linked to tblMEP in a 1-n (only one country at a time)

tblTitle holds many standard entries and is linked totblMEP in a 1-n

because you can only be a Frau, Signora or Madame etc at one point in
time...

I have created a tblEntityType (your tblBodyTypes), a tblEntityName

(tblBodies), tbl Membership (tblMemberPositions), and I already had a
tblPosition (tblPositions). I have linked them as you suggested.

tblMembership is linked to tblMEP in a 1-n and again respectively linked

to tblEntityName and tblPosition n-1.

tblEntityName is again linked to tblEntityType in a n-1.

Now the questions is: How does it work? There are no data in the

MembershipID in tblMEP (FYI: I have already, before I posted the first
question, put all details of the 732 MEPs into the database).

I have also constructed a form based on the old design - how do I get

access to fields from the new tables? I guess I have to because now I cannot
get access to the Entity info unless I use the static unlinked fields I
still have in tblMEP - and such a solution seems a little stupid to me...

How does the link between membership type and MEP work? I mean, since each

MEP can have several connections (Member/Substitute/Chairman etc) to
different Committees, Delegations, Other Bodies, AND always has a
memberstatus like e.g. Member/Chairman/Vice-Chairman of a Political Group
AND is a Member of a National Party AND a Member of the EP... How does I
seperate all this info and get it into a form...?

Tina, if you can get me through this database labyrinth I shall buy you

are 3 star Michelin dinner next time you're in Brussels! I am reachable at
¤ or you can skype me under username gravesen (if you do
not know Skype see www.skype.com).

Thanks! - Søren

"tina" wrote:

well, i don't know if i'm the Tina you mentioned but here's my two cents
worth. right off the bat, i would get rid of the Lookup fields in the
tables. for an explanation of why, see the link below:

http://www.mvps.org/access/lookupfields.htm

another very helpful link when getting started is

http://www.mvps.org/access/tencommandments.htm

it's written tongue-in-cheek, but gives serious and very useful advice.

as
always, i recommend that you look around in the mvps.org website; it's a
fantastic resource generated by some of the best Access developers in

the
world, many of whom also donate time to the newsgroups, and it's free to
everyone.



i know zilch about the EP, and politics in general, so i had to guess

about
some things. in general, here's how I organize my data:

i start with the main subject, in this case *members* of the EP.

anything
that the main subject has only one of, such as a name, goes in the main

data
table - in this case tblMembers.

anything that the main subject *may* have more than one of, goes in a

child
data table linked to the main table. the child data table contains data
about a certain subject - such as tblMemberAddresses. of course there

may be
multiple child data tables.

any descriptive or categorizing data that has standard values, goes in a
"supporting" (lookup) table - such as a table of Countries, a table of
Cities, etc. that supporting table is used to populate the particular

field
in the data table - such as Country, in tblMemberAddresses.

below is a possible table setup. you'll have to apply the guidelines

above
to determine where i may have gone wrong due to not understanding

various
terms used in your post.



tblMembers (data table)

MemberID (primary key)

FirstName

LastName

NationalParty

Position

Title

Constituency

(i assumed that national party is the political party of the country the
member is a citizen of, such as Democratic/Republican parties in the

U.S.
if, instead, it has "standard" values, then I would use the primary key

from
a supporting table to populate the field as a foreign key.

also, if position and title have "standard" values, then i would use a
supporting table as described above. Note: i have no idea what
"constituency" refers to. if it is specific to an address, then move it

to
tblMemberAddresses. if the member may have multiple constituencies, not
specific to an address, then make a separate table for constituencies,

with
a foreign key from tblMembers.)



tblMemberAddresses (data table; 1-n relationship with tblMembers)

MemAddID (primary key)

MemberID (foreign key from tblMembers)

Street

City (possibly a foreign key from a supporting table of cities)

Zip

CountryID (foreign key from tblCountries)

Region (possibly a foreign key from a supporting table of regions)



tblCountries (supporting table; 1-n relationship with

tblMemberAddresses)

CountryID (primary key)

CountryName



tblAddressPhones (data table; 1-n relationship with tblMemberAddresses)

AddPhID (primary key)

MemAddID (foreign key from tblMemberAddresses)

PhoneTypeID (foreign key from tblPhoneTypes)

PhoneNumber (text field)

Comments

(usually, i use this table to list email addresses and websites, as well

as
phone numbers - since they're all "ways to contact the main subject".

but in
this case, you have multiple addresses. phone and fax numbers are

probably
specific to each address, which is why i used the foreign key from
tblMemberAddresses, rather than the foreign key from tblMembers.

*but*, an email address might not be specific to a certain street

address.
if each Member has only one email address, i would put it in tblMembers.

if
they may have many, that are not specific to a street address, i would

make
a separate table for email addresses, with a foreign key from

tblMembers.

the same considerations apply to website URL.)



tblPhoneTypes (supporting table; 1-n relationship with tblAddressPhones)

PhoneTypeID (primary key)

PhoneTypeName

(names such as Phone, Fax, Cell, possibly Email and/or URL, etc.)



tblBodyTypes (supporting table; 1-n relationship with tblBodies)

BodyTypeID (primary key)

BodyTypeName

(names such as Committee, Delegation, Political Group, etc)



tblBodies (supporting table; 1-n relationship with tblMemberPositions)

BodyID (primary key)

BodyTypeID (foreign key from tblBodyTypes)

BodyName



tblPositions (supporting table; 1-n relationship with

tblMemberPositions)

PositionID (primary key)

PositionName

(names such as Member, Substitute, Chairman, Vice-Chairman, etc)



tblMemberPositions (data table; 1-n relationship with tblMembers)

MemPosID (primary key)

MemberID (foreign key from tblMembers)

BodyID (foreign key from tblBodies)

PositionID (foreign key from tblPositions)



remember to *not* use Access reserved words as field names, such as

Name,
Date, etc. and recommend you use only letters and underscores ( _ ) in

your
table/field names; you can use numbers if you must, but i try to avoid

it.


hth


"Søren" wrote in message
...
Hi,

I've had a look around in some of the questions here and especially

the
ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little
sparring and couching! I am trying to create a database with a lot of
contact information for the 732 members of the European Parliament.

Status
qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including

street, city, ZIP, country, region, counstituency, phone and fax

numbers,
office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND

max
3 EP Delegation AND 3 other bodies in EP AND a political group in the EP

AND
their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections

(positions) - or the same, e.g. a MEP is always a Member of the European
Parliament, and a member OR Chariman OR Vice-Chairman of a Political

Group;
the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at

least
2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs

to
this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body,

Home
Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID &

EP
Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP
Delegation.EPDelegationID) to create a Many-to-Many relationship; the
relationship is one-to-many from MEP to EP Committeeship and many-to-one
from EP Committeeship to EP Committee, which again have many-to-one to
Position
4- I have direct one-to-many connections between MEP and EU

Instituion,
MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and

fieldnames) in each table e.g. PositionID is key in Position and linked

to a
numeric field with the same name in all related tables, EPDelegationID

is
key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the

tables
mentioned above under 2.

Could someone give me a guidance through the this hurricane of

relations
and table?

Thanks very much in advance!


BR, Søren






  #8  
Old July 13th, 2004, 05:41 AM
tina
external usenet poster
 
Posts: n/a
Default Complicated Databse w/many relationships

from your first few paragraphs below, seems your table is definitely not
normalized. you should not have fields for multiple addresses all in one
table. you should not have fields named xx1, xx2, xx3, etc - again, not
normalized. you need to house this data in separate tables, as i described.
i sent you an email at about 12:30 am U.S. Eastern Time, Tuesday. if you
don't get it, pls post back here. i looked at the skype website; looks cool,
but i don't have a headset or mic, so we'll have to communicate via the
keyboard, i'm afraid.


"Søren" wrote in message
...
Yes - you are the Tina I mentioned; thanks for the input! Basicly all of

your assumptions were right; a constituency is the geographical area where a
person is standing as a Parliamentarian Candidate - e.g. a US Senator's
constituency would be the state in which he is running for election.

Unfortunately I still have some unanswer questions...

Status quo is I have now changed the lay-out of the table structure to the

following:

tblMEP (your tblMembers) - I have removed the lookup fields but kept the

info in standard text fields (is that smart?). I have kept all the address
fields (offices, home), contact details (2 e-mails and 2 websites) in this
table as well as Constituency, Suffix, Birthsday, Notes, MemberSince. I
guess that is OK.

The table also have fields like EUInstituion, EPCommitte1, EPCommitte2,

EPCommitte3 (same for EPDelegation and OtherBodies), NationalParty,
PoliticalGroup. How do I connect these fields to the respective support
tables?

tblNational Party holds 146 entries with standard values (national party

names as spelled in the specific country) and is linked to tblMEP in a 1-n
because 1 MEP can only be connected to one National Party at a time.

tblHomeCountry holds 25 standard entries (UK, France, Denmark etc) and is

linked to tblMEP in a 1-n (only one country at a time)

tblTitle holds many standard entries and is linked totblMEP in a 1-n

because you can only be a Frau, Signora or Madame etc at one point in
time...

I have created a tblEntityType (your tblBodyTypes), a tblEntityName

(tblBodies), tbl Membership (tblMemberPositions), and I already had a
tblPosition (tblPositions). I have linked them as you suggested.

tblMembership is linked to tblMEP in a 1-n and again respectively linked

to tblEntityName and tblPosition n-1.

tblEntityName is again linked to tblEntityType in a n-1.

Now the questions is: How does it work? There are no data in the

MembershipID in tblMEP (FYI: I have already, before I posted the first
question, put all details of the 732 MEPs into the database).

I have also constructed a form based on the old design - how do I get

access to fields from the new tables? I guess I have to because now I cannot
get access to the Entity info unless I use the static unlinked fields I
still have in tblMEP - and such a solution seems a little stupid to me...

How does the link between membership type and MEP work? I mean, since each

MEP can have several connections (Member/Substitute/Chairman etc) to
different Committees, Delegations, Other Bodies, AND always has a
memberstatus like e.g. Member/Chairman/Vice-Chairman of a Political Group
AND is a Member of a National Party AND a Member of the EP... How does I
seperate all this info and get it into a form...?

Tina, if you can get me through this database labyrinth I shall buy you

are 3 star Michelin dinner next time you're in Brussels! I am reachable at
¤ or you can skype me under username gravesen (if you do
not know Skype see www.skype.com).

Thanks! - Søren

"tina" wrote:

well, i don't know if i'm the Tina you mentioned but here's my two cents
worth. right off the bat, i would get rid of the Lookup fields in the
tables. for an explanation of why, see the link below:

http://www.mvps.org/access/lookupfields.htm

another very helpful link when getting started is

http://www.mvps.org/access/tencommandments.htm

it's written tongue-in-cheek, but gives serious and very useful advice.

as
always, i recommend that you look around in the mvps.org website; it's a
fantastic resource generated by some of the best Access developers in

the
world, many of whom also donate time to the newsgroups, and it's free to
everyone.



i know zilch about the EP, and politics in general, so i had to guess

about
some things. in general, here's how I organize my data:

i start with the main subject, in this case *members* of the EP.

anything
that the main subject has only one of, such as a name, goes in the main

data
table - in this case tblMembers.

anything that the main subject *may* have more than one of, goes in a

child
data table linked to the main table. the child data table contains data
about a certain subject - such as tblMemberAddresses. of course there

may be
multiple child data tables.

any descriptive or categorizing data that has standard values, goes in a
"supporting" (lookup) table - such as a table of Countries, a table of
Cities, etc. that supporting table is used to populate the particular

field
in the data table - such as Country, in tblMemberAddresses.

below is a possible table setup. you'll have to apply the guidelines

above
to determine where i may have gone wrong due to not understanding

various
terms used in your post.



tblMembers (data table)

MemberID (primary key)

FirstName

LastName

NationalParty

Position

Title

Constituency

(i assumed that national party is the political party of the country the
member is a citizen of, such as Democratic/Republican parties in the

U.S.
if, instead, it has "standard" values, then I would use the primary key

from
a supporting table to populate the field as a foreign key.

also, if position and title have "standard" values, then i would use a
supporting table as described above. Note: i have no idea what
"constituency" refers to. if it is specific to an address, then move it

to
tblMemberAddresses. if the member may have multiple constituencies, not
specific to an address, then make a separate table for constituencies,

with
a foreign key from tblMembers.)



tblMemberAddresses (data table; 1-n relationship with tblMembers)

MemAddID (primary key)

MemberID (foreign key from tblMembers)

Street

City (possibly a foreign key from a supporting table of cities)

Zip

CountryID (foreign key from tblCountries)

Region (possibly a foreign key from a supporting table of regions)



tblCountries (supporting table; 1-n relationship with

tblMemberAddresses)

CountryID (primary key)

CountryName



tblAddressPhones (data table; 1-n relationship with tblMemberAddresses)

AddPhID (primary key)

MemAddID (foreign key from tblMemberAddresses)

PhoneTypeID (foreign key from tblPhoneTypes)

PhoneNumber (text field)

Comments

(usually, i use this table to list email addresses and websites, as well

as
phone numbers - since they're all "ways to contact the main subject".

but in
this case, you have multiple addresses. phone and fax numbers are

probably
specific to each address, which is why i used the foreign key from
tblMemberAddresses, rather than the foreign key from tblMembers.

*but*, an email address might not be specific to a certain street

address.
if each Member has only one email address, i would put it in tblMembers.

if
they may have many, that are not specific to a street address, i would

make
a separate table for email addresses, with a foreign key from

tblMembers.

the same considerations apply to website URL.)



tblPhoneTypes (supporting table; 1-n relationship with tblAddressPhones)

PhoneTypeID (primary key)

PhoneTypeName

(names such as Phone, Fax, Cell, possibly Email and/or URL, etc.)



tblBodyTypes (supporting table; 1-n relationship with tblBodies)

BodyTypeID (primary key)

BodyTypeName

(names such as Committee, Delegation, Political Group, etc)



tblBodies (supporting table; 1-n relationship with tblMemberPositions)

BodyID (primary key)

BodyTypeID (foreign key from tblBodyTypes)

BodyName



tblPositions (supporting table; 1-n relationship with

tblMemberPositions)

PositionID (primary key)

PositionName

(names such as Member, Substitute, Chairman, Vice-Chairman, etc)



tblMemberPositions (data table; 1-n relationship with tblMembers)

MemPosID (primary key)

MemberID (foreign key from tblMembers)

BodyID (foreign key from tblBodies)

PositionID (foreign key from tblPositions)



remember to *not* use Access reserved words as field names, such as

Name,
Date, etc. and recommend you use only letters and underscores ( _ ) in

your
table/field names; you can use numbers if you must, but i try to avoid

it.


hth


"Søren" wrote in message
...
Hi,

I've had a look around in some of the questions here and especially

the
ones from Tina, Allan and Rebecca - but I'm a bit stuck; I need a little
sparring and couching! I am trying to create a database with a lot of
contact information for the 732 members of the European Parliament.

Status
qou is:

I have 732 MEPs each with

- 3 addresses (2x offices in Brussels & Strasbourg and Home) including

street, city, ZIP, country, region, counstituency, phone and fax

numbers,
office locations, e-mails, websites, position, title etc;
- Uptil 12 connections (memberships) split on max 3 EP-Committess AND

max
3 EP Delegation AND 3 other bodies in EP AND a political group in the EP

AND
their National Party AND, of course, the European Parliament itself.
- Each of these connections can have different types of connections

(positions) - or the same, e.g. a MEP is always a Member of the European
Parliament, and a member OR Chariman OR Vice-Chairman of a Political

Group;
the MEP is also always a Member/Subsitute/Chairman/Vice-Chairman of at

least
2-3 Committees AND 2-3 Delegations.

Example:

the MEP is Member of the European Parliament
Member of the Green Group
Chairman of the Environmental Committee
Member of the Transport Committee
Subsitute of the Energy Committee
Vice-Chairman of the Delegation A
Member of the Delegation B
Member of another body under the EU Instituion

My setup now is

1- a MEP table which include all fields i.d. fields that only belongs

to
this table and lookup fields connected to other table.
2- tables for EU Instituion, EP Committee, EP Delegation, Other Body,

Home
Country, National Party, Political Group, Title, Position
3- two intermediate table called EP Committeeship (fields: MEP.MEPID &

EP
Committee.EPCommitteeID) and EP Delegationship (MEPID.MEPID & EP
Delegation.EPDelegationID) to create a Many-to-Many relationship; the
relationship is one-to-many from MEP to EP Committeeship and many-to-one
from EP Committeeship to EP Committee, which again have many-to-one to
Position
4- I have direct one-to-many connections between MEP and EU

Instituion,
MEP and Home Country, MEP and Political Group, MEP and National Party

All are liked via numeric autonumber ID fields with unique values (and

fieldnames) in each table e.g. PositionID is key in Position and linked

to a
numeric field with the same name in all related tables, EPDelegationID

is
key in EP Delegation etc.

I have made lookup fields in the MEP table that refers to all the

tables
mentioned above under 2.

Could someone give me a guidance through the this hurricane of

relations
and table?

Thanks very much in advance!


BR, Søren






 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not seeing all relationships in layout window jettabug General Discussion 3 June 18th, 2004 05:42 PM
relationships Steven Database Design 2 June 17th, 2004 02:17 PM
Relationships between tables Mel New Users 2 June 11th, 2004 04:00 PM
removing relationships. FFira General Discussion 1 June 4th, 2004 05:33 AM
Table Relationships Design Tom Database Design 1 May 5th, 2004 11:56 PM


All times are GMT +1. The time now is 09:54 AM.


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