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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |