Multiple Junction Tables (1 Viewer)

[MajP]

That looks super fancy. And super complicated

That the beauty of class modules. All the code is in the classes like a big template So to reuse it is not much code at all. You use the methods that is there, you do not modify it. If you look at the very first download in that thread you can see it reused on a couple different tables. Just consider the classes black boxes do not look inside them. You only need to no the inputs and outputs.

 

[Pat Hartman]

Sorry, I skimmed the thread. It made my head hurt.

tblUnits and tblBuildingUnits are redundant. If tblUnits is supposed to be a generic unit type, then it should not contain BuildingID.

Can a building/unit have more than one disconnect? Can a single disconnect belong to more than one building/unit? Seems to me, the disconnect is an attribute of a building or a unit. This is not a m-m relationship. it is 1-m at best if you want to keep a table for the disconnects because you use many of a given part number.

Branch is similar to disconnect.

 

[tmyers]

Sorry, I skimmed the thread. It made my head hurt.

tblUnits and tblBuildingUnits are redundant. If tblUnits is supposed to be a generic unit type, then it should not contain BuildingID.

Can a building/unit have more than one disconnect? Can a single disconnect belong to more than one building/unit? Seems to me, the disconnect is an attribute of a building or a unit. This is not a m-m relationship. it is 1-m at best if you want to keep a table for the disconnects because you use many of a given part number.

Branch is similar to disconnect.

Per Plog, tblUnits has been removed and its attributes essentially rolled into another table. The junction table for disconnects has also been removed. Plog made a wonderful suggestion to use a dummy unit for it. They dont appear for buildings very often, so it was a good solution to that mess.

Branch is different. That is because a building will always have a meter center. The meter centers are modular in that they can have a pretty wide variety of branches. A main could have one branch, or it could have 8. Each branch may be used multiple times for a single main, or each could be different. It is a pain to try and reconcile. I thought of it in a student/class style. A main meter is the "class" and the branch devices are the "students".

 

[Pat Hartman]

Hardware is installed. It doesn't move from building to building so it cannot be m-m.

you don't need a "dummy" solution for disconnects if you model the relationship correctly. Two tables can point to the same 1-side table so both the unit and building tables can point to the disconnect table.

 

[JMongi]

If I may interject momentarily. You have some super smart individuals trying to help you out from the database standpoint. If I may make some observations that may help these fine folks understand what you are doing (they may already). IMO your estimating database is a type of Bill Of Materials style database. I suggest you do some searching on that. I too needed to capture some data structured in this manner. It can be a tad confusing as this type of structure involves many-to-many relationships i.e. junction tables.

While everyone thinks differently, I feel one statement may be tripping you up (or at least making it harder to wrap your mind around the structure).

Disconnects can appear both for the buildings and for the units. So I made the two tables [tblBuildingDisconnect] and [tblUnitDisconnect] as junction tables, but that creates a traceable loop.

I'm not sure I follow your approach here. A junction table is used to interface between two tables that would "normally" have a many to many relationship. That type of relationship cannot exist directly in a relational database and so a junction table sits in between those two tables to keep our database rules intact. It is quite common for a table that contains a list of items (such as disconnects) to be related to more than one table without the need for a junction table.

For example, let's say I have an employee table:
TblEmployee
EmpID
EmpName

And I have a table that stores which employee is the project lead.
TblProject
ProjID
ProjName
ProjLead

Let's say I also have an office table that stores which employee has been assigned a particular office.
TblOffice
OffID
OffLocation
OffUser

TblEmployee can have a 1 to Many relationship with both TblOffice and TblProject. No junction tables needed. This is the same as your statement "Disconnects can appear for both buildings and units." "Employees can appear for both Projects and Offices" So, that particular statement is NOT the reason you may need a junction table.

a piece of equipment can appear multiple times in multiple places

Even this statement does not mean that we necessarily need junction tables. It depends upon how we arrange our "multiple places".