Many to many reationship design?

[Dave]

Looking for a reality check to see if I designed this correctly. Found a couple
examples and based this off of them. A project has
at least 2 sites and 2 pieces of equipment. I want to make sure I've set these
relationships up correctly. Any input/suggestions would be appreciated as
I'm still learning Access. Thanks in advance!

Have the following tables:

frmProject
========================

PROJECTS
-------------
+Project#
Project siteA combobox (rowsource SITES)
Project siteB combobox (rowsource SITES)
Project EquipmentA combobox (rowsource EQUIPMENT)
Project EquipmentB combobox (rowsource EQUIPMENT)

========================

SITES
-----
+Sitecode
Address
Phone
(misc details)

EQUIPMENT
---------
+EquipmentID
(misc details)


ProjectEquipment
--------------------
+AutoID
Project# (1 to M) - linked to PROJECTS (Project#)
EquipmentID (1 to M) - linked to EQUIPMENT(EquipmentID)

ProjectSites

 

[BruceM]

I think you will want to take a closer look at your Projects table. It
should just contain any information that is specific to the project (start
date, supervisor, etc.). It almost certainly would not contain fields for
Site A & B and Equipment A & B unless you are absolutely certain there will
never be more than two of each. Even then there is nothing to be gained by
putting all of that in one table. A many-to-many relationship would be
something like a project that could use many pieces of Equipment, and a piece
of equipment that can be used on many sites. It sounds like you have a
project that can take place on several sites, and a project that uses several
pieces of equipment.

Let me suggest that you establish a projects table (tblProject) with an
autonumber primary key (let's say ProjectID) and whatever else is specific to
the project. Then establish a sites table (tblSite), containing an
autonumber primary key (SiteID), a foreign key (ProjectID, with Data Type set
to Number), Address, and whatever else is specfic to Site. Establish a
Relationship (Tools > Relationships) between Project ID in the two tables.
Make a form (frmProjects) based on tblProjects, and make frmSites based on
tblSites. With frmProjects open in design view, drag the icon for frmSites
onto an empty space on the form. Try a few test records.
I suspect Equipment is handled differently. If tblEquipment is a listing of
equipment the company owns, then each piece of equipment can be used on many
projects (or maybe on many sites, depending on just what you need to track).
In either case tblProjectEquipment (or tblSiteEquipment) makes sense as a
junction table, with the its own PK and the FKs ProjectID and EquipmentID,
and date or whatever else is needed. The subform would probably be based on
tblProjectEquipment (with tblProject as the main form like before).
Where this potentially gets a little tricky is if you need to track
equipment per Site and per Project. You may need somebody with greater
expertise than mine to work that out. It would probably help if you could
describe in plain language (without DB terminology) just what you need to do.
Whether the relationships are set up correctly depends on what you need the
database to do. They may be set up correctly, but there is not enough
information to know for sure.

 

[Dave]

Bruce,

I appreciate you taking the time to check out my post and offer your advice.
I'll take a look at the design again. What I'm trying to do is to ensure that
I can track equipment per Site and per Project. It's simple enought to track
at a project level, but I need to ensure a relationship exists between all of
the tables so i have a relationship that is queryable.

I'll ponder it this weeked. Again, thanks for the thoughts and I'll look
into splitting the tables if possible.

Have a good weekend....

 

[Immanuel Sibero]

Hi Dave,

As Bruce pointed out, your tblProject needs a little work. It should only
contain information specific to a project. I think you have properly
identified entities that are important - Projects, Sites, Equipment. So I
would start with creating those three "ENTITY" tables. Then, to glue them
into a relational database model that emulate your real world problem, you
would need more tables <g>. These additional tables reflect the
relationships between your "entity" tables (i.e. sometimes called "LINK" or
"JUNCTION" tables). Hence, whether or not you need "junction" tables depends
on the relationship among your "entity" tables. Many-to-Many relationship
between two entity tables requires a junction table.

There are some questions that only you can answer:
1. Can a site have multiple projects going on? If so, there is a
Many-to-Many between Projects and Sites
2. Can an equipment be involved (i.e. used) in multiple projects? If so,
there is a Many-to-Many between Projects and Equipment

I'm assuming the answer is yes to the questions above. In this case, two
alternatives for you to consider:

ALTERNATIVE ONE:
Entity Tables:
-tblProject (ProjectID PK, other fields such as Project Name, Project level
data, etc)
-tblSite (SiteID PK, other fields such as Site address, phone, etc..)
-tblEquipment (EquipmentID PK, other fields such as Equipment description)

Junction Tables:
-tblProjectSite between tblProject and tblSite (ProjectID FK, SiteID FK,
other fields such as start date, end date)
-tblProjectEquipment between tblProject and tblEquipment (ProjectID FK,
EquipmentID FK, other fields such as Equipment operator, etc.)


ALTERNATIVE TWO:
Entity Tables:
-tblProject (ProjectID PK, other fields such as Project Name, Project level
data, etc)
-tblSite (SiteID PK, other fields such as Site address, phone, etc..)
-tblEquipment (EquipmentID PK, other fields such as Equipment description)

Junction Tables:
-tblProjectSite between tblProject and tblSite (ProjectID FK, SiteID FK,
other fields such as start date, end date)
-tblProjectSiteEquipment between tblProjectSite and tblEquipment(ProjectID
FK, SiteID FK, EquipmentID, Equipment operator, etc.)


There are other alternative designs (i.e. you could have a third
alternative - relate Project and Equipment first and then to Site), but
there is probably only one that's most appropriate, and that is, the one
that most closely approximate your real world. A few things to notice from
the above alternatives:
- For both alternatives, the "entity" tables are setup the same way. As a
matter of fact, no matter how many alternative designs you can come up with,
"entity" tables should remain the same.
- For each alternative, the "junction" tables are setup differently.
- The most appropriate model to use is the one where the setup of "junction"
tables most closely emulate your real life problem.

Using alternative one, you can assign an equipment to a project at anytime,
you can also assign a project to a site at anytime. In other words, you can
assign an equipment to a project without knowing ahead of time the site of
the project. Using alternative two, however, you can only assign an
equipment to a project only after the site has been determined.

As you can see, the setup of the junction tables (i.e. relationships) is
crucial in determining what you can and can not do. You will have to
carefully examine the interrelations between your entities (Project, Site,
Equipment). You can do this by thinking through your business requirements
and processes which are what you're trying to emulate. By the way, either
alternative will do the query requirement you specified (i.e. tracking
equipment by site or by project) and many more.

HTH,
Immanuel Sibero

 

[Dave]

Immanuel,

Thanks for your comments...I just got back to work today. I'll talke a look
at what you suggested. I beleive I'm on the right track but need to refine
what I did. I'll take a closer look at all of your suggetions. Thanks for the
advice!