Re: database design question

From: Kevin3NF (KHill_at_NopeIDontNeedNoSPAM3NF-inc.com)
Date: 06/29/04 

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
>
Quantcast