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  

Many to many reationship design?



 
 
Thread Tools Display Modes
  #1  
Old January 14th, 2005, 06:31 PM
Dave
external usenet poster
 
Posts: n/a
Default Many to many reationship design?

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
-------------
+AutoID
Project# (1 to M) - linked to PROJECTS (Project#)
Sitecode (1 to M) - linked to SITES (Sitecode)


  #2  
Old January 14th, 2005, 09:39 PM
BruceM
external usenet poster
 
Posts: n/a
Default

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" wrote:

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
-------------
+AutoID
Project# (1 to M) - linked to PROJECTS (Project#)
Sitecode (1 to M) - linked to SITES (Sitecode)


  #3  
Old January 14th, 2005, 10:59 PM
Dave
external usenet poster
 
Posts: n/a
Default

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



"BruceM" wrote:

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" wrote:

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
-------------
+AutoID
Project# (1 to M) - linked to PROJECTS (Project#)
Sitecode (1 to M) - linked to SITES (Sitecode)


  #4  
Old January 15th, 2005, 01:15 AM
Immanuel Sibero
external usenet poster
 
Posts: n/a
Default


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" wrote in message
...
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....



"BruceM" wrote:

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" wrote:

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
-------------
+AutoID
Project# (1 to M) - linked to PROJECTS (Project#)
Sitecode (1 to M) - linked to SITES (Sitecode)




  #5  
Old January 19th, 2005, 12:11 AM
Dave
external usenet poster
 
Posts: n/a
Default

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!

"Immanuel Sibero" wrote:


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" wrote in message
...
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....



"BruceM" wrote:

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" wrote:

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
-------------
+AutoID
Project# (1 to M) - linked to PROJECTS (Project#)
Sitecode (1 to M) - linked to SITES (Sitecode)





 




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
How can I customise an entire design set David Publisher 2 November 12th, 2004 10:43 AM
design master problem J. Vermeer General Discussion 0 September 8th, 2004 03:23 PM
Action queries changing when reopened in design view Kendra Running & Setting Up Queries 2 August 31st, 2004 12:34 AM
Document Starts in Design Mode every time I open it Colin Higbie General Discussion 4 June 14th, 2004 12:24 PM
opening a document so it is NOT in design mode Brad Pears New Users 1 May 3rd, 2004 09:13 PM


All times are GMT +1. The time now is 10:47 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.