Re: database design question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
>

Relevant Pages

  • RE: "Module" option does not show up when setting permissions.
    ... It's by design. ... Access 2000 introduced the separate VBA ... Project for handling the code (notice the separate code window (VB Editor) ... an MDE database file out of it if you want to hide the source code from ...
    (microsoft.public.access.security)
  • Re: Linking Main Contact Table
    ... tblMaster. ... try and learn database design and get the tables right ... database set up as soon as possible. ... ProjectTitle; LeadEngineer (long integer format to link with contactID); ...
    (microsoft.public.access.forms)
  • Re: Access 2002 Advice
    ... Aside from your desire to use 13 separate sections on the form, ... relational database is to start with the ... then design forms that make it ... > primary key in each table (ex: account number as PK in each table). ...
    (microsoft.public.access.tablesdbdesign)
  • Re: database design question
    ... TblProjectRole ... If you would like help with your database, contact me at my email address below. ... > I'm trying to design a database to store project information for construction ... If I build separate tables for each contact role on a project (I've got ...
    (microsoft.public.access.gettingstarted)
  • Need help with SQL DatABASE!
    ... Designing a Simple Database ... Design a simple database for storing information ... information in an SQL Schema: ... Arbitrary number of separate sections. ...
    (microsoft.public.sqlserver.odbc)