View Single Post
  #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