Multiple Junction Tables (1 Viewer)

[tmyers]

I have yet another question revolving around junction tables (sorry!). Since this looks like it will happen several times, I want to try and get some advice before I proceed and have to undo everything. This once again revolves around an app meant for quoting apartment complexes with multiple buildings.

Here a snip of how I think it may be done for one section:

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.

Here it is further down the chain:

This (to me) looks like it is getting quite messy. Could anyone offer some advice as to how I should do this? Or surprise me by saying I did this correctly?

 

[The_Doc_Man]

I see your dilemma. If you have both building disconnects and unit disconnects, you have an event with two different "scope of effect." This might be a case where you need to have a separate disconnect table that includes a flag showing scope of disconnect (building/unit). Then in your proposed junction, ONLY link to the unit. BUT if you generate a building disconnect, you create junction entries for every unit affected, and have a flag in the entry that says "this was a building disconnect". Because it seems to me that you could have a unit disconnect because somebody didn't pay their bill (or something like that) and then have a separate disconnect because the building's master distribution panel shorted itself into oblivion (or something extreme like that). You would populate the the building-wide disconnect because you could manage a query based off of the building/unit relationship.

I note, however, that you have not fixed the other issue. In your tblBuildingUnits, "Quantity" should never occur (I think). You should have one entry for each unit, not one entry for all similar units in bulk. Because the OUTGOING relationship arrow from there to the tblUnits makes no sense if that quantity is ever other than one. That same style of thinking affects the tblBuildingDisconnects. If there is a junction involved, each record in the junction reflects a singular relationship, even if it is potentially one that could be repeated. Usually when such repetition occurs, there is another field involved that allows making a distinction between the two. So for the Building/Unit junction, that might never be repeated. But for the Disconnct/Unit junction, there might be a cause-code to differentiate between reasons for a particular disconnect.

 

[tmyers]

To help explain a little bit:
The disconnect is not a literal disconnect of the power, but rather a piece of equipment (those small/big boxes with the handle on the side) that shuts off power. It is normal to see them for water heaters and such now a days as well which is why is appears twice. One for the building and one for the units.

As for the quantity in the tblBuildingUnits, a single unit (say 3 bedroom townhome) can occur multiple times within a building. Same goes for say a 2 bed room garden style apartment. There may also be different variations to a unit. Such as where bathrooms and such are located, so a town home may have 3 different variations (type A, B & C). So a building may consist of 3 type A units, 2 Type B and 1 Type C with 5 Type D (the garden style unit).

So thinking that way, I set that particular table up so show Building 1 consist of (x) amount of a particular style apartment. Does that help clarify that a little bit?

 

[tmyers]

I completed the tables and relationships based on my understanding of it:

All in all, I ended up with 7 junction tables with the only truly messy one still being the relationship between buildings, units and disconnects. Every instance is due to the fact that a piece of equipment can appear multiple times in multiple places. Multiple Buildings have a meter center, a single building may have multiple electric meter centers, with each meter center consisting of multiple branch meter centers (the unit that actually contains the electric meter) and each branch meter consist of multiple breakers.

It is quite the rabbit hole of everything thing consisting of multiple pieces and the pieces having pieces which also have their own pieces. Yuck.

 

[The_Doc_Man]

It sounds complex enough that I cannot "see" it in my head and therefore cannot advise on specifics. However, junction tables normally do not include a "quantity" in anything except possibly an inventory system. They repeat the entry if the entry is differentiable from others like it.

 

[tmyers]

It sounds complex enough that I cannot "see" it in my head and therefore cannot advise on specifics. However, junction tables normally do not include a "quantity" in anything except possibly an inventory system. They repeat the entry if the entry is differentiable from others like it.

It is kind of like inventory in a manner of speaking. A building has an inventory of say 1 meter main, 2 branch devices and 18 breakers. That same building has an inventory of apartments variations. Each variation shows up a (x) amount of times. Each of those times, they may have a few disconnects, but will have at least one panel which has an inventory of multiple breakers.

 

[plog]

As for the quantity in the tblBuildingUnits, a single unit (say 3 bedroom townhome) can occur multiple times within a building.

What that means is you are not specifically tracking units--you are only generaly tracking them. Is that correct?

Suppose you install a panel. With your database you will know what building it went into and you will know it went into a 2 bedroom unit, but you will not know what unit it exactly went into. Is that fine?

 

[tmyers]

What that means is you are not specifically tracking units--you are only generaly tracking them. Is that correct?

Suppose you install a panel. With your database you will know what building it went into and you will know it went into a 2 bedroom unit, but you will not know what unit it exactly went into. Is that fine?

That is fine. I don't need to know the EXACT apartment. Just that Building 1 has 3 of a 2 bedroom unit and each of those units has this particular panel. I would not need to know that apartment 200 has panel A and apartment 201 has panel B etc.

 

[plog]

Sounds good, just making sure. As for the issue you posted about--I would not have tblBuildingDisconnect, I would make all disconnects belong to a unit. That means you add a "dummy" unit to tblUnits for this specific case.

Also, BuildingID comes out of tblUnits because you obtain that information via tblBuildingUnits.

 

[tmyers]

Sounds good, just making sure. As for the issue you posted about--I would not have tblBuildingDisconnect, I would make all disconnects belong to a unit. That means you add a "dummy" unit to tblUnits for this specific case.

Also, BuildingID comes out of tblUnits because you obtain that information via tblBuildingUnits.

Ok. I removed BuildingID from [tblUnits] (table seems anemic now with just two fields). I removed the table [tblBuildingDisconnect] as you made a very valid point with just creating a dummy entry for it in Units. It is actually rare that a building would have one, but it does occur.

That looks so much cleaner.

 

[plog]

(table seems anemic now with just two fields)

That raises the question--do you really need to know about the unit types? Since you aren't tracking them specifically, the only thing it does is tell you about the composition of the building. It doesn't really help you identify anything downstream (disconnect, panel, breaker, etc.)

If you don't you can delete tblBuildingUnits and tblUnits, which means tblUnitPanels and tblUnitDisconnect link directly to tblBuildings.

 

[tmyers]

I do sadly. In the event they would use this for a single building complex (think high rise apartment building), I have seen those things have 20 different apartment variations. The electrical panels going into them may be the same for each one, but I would still have to be able to differentiate them in some way from unit to unit. That way we can break down what each unit type consist of (even if they are the same).

 

[plog]

The electrical panels going into them may be the same for each one, but I would still have to be able to differentiate them in some way from unit to unit.

You could do that in tblUnitPanels:

BuildingID
PanelID
UnitType
PanelQuantity


Additionally, I would prefix all [Quantity] fields with what they are quantities of. When you start writing queries/code its going to make it a little more difficult,

 

[tmyers]

You could do that in tblUnitPanels:

BuildingID
PanelID
UnitType
PanelQuantity


Additionally, I would prefix all [Quantity] fields with what they are quantities of. When you start writing queries/code its going to make it a little more difficult,

Fair enough.

 

[plog]

Just a suggestion, I think either way will work.

 

[tmyers]

Just a suggestion, I think either way will work.

All good.
I just want to get my tables right the first time around this time lol. The forms are going to be a pain. I foresee tons of cascading combos and such.

 

[MajP]

I will bow out of this since you have lots of other helpers. But once you get your tables and input forms I will see if I can put into a tree. It may be a nice way to visualize and enter data. You can click on a node and then pop up a form to enter child records or edit the current node. This may make it a simpler than cascading combos.

 

[tmyers]

I will bow out of this since you have lots of other helpers. But once you get your tables and input forms I will see if I can put into a tree. It may be a nice way to visualize and enter data. You can click on a node and then pop up a form to enter child records or edit the current node. This may make it a simpler than cascading combos.

That actually sounds wonderful. A tree sounds like a very visually appealing way of doing it.

 

[MajP]

Here is a demo tree with every bell and whistle we could think of from the thread I posted earlier. The thread is huge so i posted the example here. This has drag drop, sorting, right click, left click menus, partial loading. You can see some of the capabilities. This is pretty well encapsulated so you can drop pretty much anything in here without writing much new code.

 

[tmyers]

That looks super fancy. And super complicated.