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
Prev Previous Post   Next Post Next
  #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
 




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 11:34 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.