Re: database design question
From: Kevin3NF (KHill_at_NopeIDontNeedNoSPAM3NF-inc.com)
Date: 06/29/04
- Next message: John Vinson: "Re: Follow-up to Can Access do this? Attn. John Vinson"
- Previous message: PC Datasheet: "Re: database design question"
- In reply to: CNC: "database design question"
- Next in thread: John Vinson: "Re: database design question"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 29 Jun 2004 13:52:54 -0500
If the Project can have more than one contact, and Bob (a contact) can be
involved with more than one project, you have a classic many-to-many
relationship. This dictates the use of a junction table that (at a minimum)
has projectID and ContactID fields in it. Very likely it will also have a
RoleID (which is a foreign key to your Roles table.
tblProject:
ProjectID
ProjectDesc
Location
etc.
tblContact
ContactID
LastName
Firstname
etc.
tblProjectContact
ProjectID
ContactID
RoleID?
etc.
-- Kevin Hill President 3NF Consulting www.3nf-inc.com/NewsGroups.htm www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs. "CNC" <CNC@discussions.microsoft.com> wrote in message news:8614D6E1-4229-4273-9232-043533D287E8@microsoft.com... > Hi all, > > I'm trying to design a database to store project information for construction projects, to be used for marketing purposes. My experience with Access is pretty limited, and as such, I'm having trouble evisioning a proper table design for the database. > > The problem comes when I consider contacts for each project. I'd like to be able to have a single table of contacts and assign project roles as appropriate. The reason that I'd like to do this is contacts may play different roles on different projects (i.e., a client can also be a tenant or a building owner, etc.). If I build separate tables for each contact role on a project (I've got 13 different roles that I want to assign a contact to), I'd end up with duplicate contacts in different tables. > > So far, I've set up the following tables: > > Project_Physical (all the physical attributes of the project - location, size, etc) > Project_Services (the services that we provided on the project, all true/false) > Project_Costs (costs for various pieces of the project) > > Can someone advise me on how to handle the contacts in this situation? Can I use one contact table and use lookup tables to do this, or am I going to have to create a bunch of separate contact tables? > > Thanks, > Chris >
- Next message: John Vinson: "Re: Follow-up to Can Access do this? Attn. John Vinson"
- Previous message: PC Datasheet: "Re: database design question"
- In reply to: CNC: "database design question"
- Next in thread: John Vinson: "Re: database design question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|