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