Table Structure to use two different lists in a drop down. (1 Viewer)

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
Oops...inexperience rearing its ugly head again. I forgot junction tables functioned with the dual field keys.

Theoretically there could be multiple engines on a single unit, though this is extremely rare. But, that's not actually why I did that, so let's skip that usage case for the moment. I will add it back in to cover that usage case, but I want to understand what you are going for first.

As far as engine swaps. We are in the habit of swapping engines when one goes down and is not repairable in the field. On slightly rarer occasions we will replace one engine with another when refurbishing a unit. So history is important (and one of the things this database is supposed to do a better job of tracking than our current spreadsheet du jour).
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
So, is this what you were describing (to include installation history)?

1630526131818.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
I would NOT use a natural key instead of an autonumber as the PK for tblUnitEngine. Leave the autonumber as the PK. But make a unique index on the THREE fields I mentioned - the two PK being connected AND the date the engine was installed. This satisfies your history as well as the multi-engine case.

You will need code to manage how engines are replaced. At a minimum, you cannot add a new record to UnitEngine for a DetailID if there is one already there with a null DateRemoved. So, you need to Remove an engine before you can reuse it. There are probably other rules also but that is a minimum.

The reason I use autonumbers plus a unique business rules index for junction tables is because of the way combo's work. They ASSUME single-field unique index. Without that, you may have trouble using them.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
ok. I must have misunderstood what you were suggesting.

With this setup, how would I handle the case of an existing unit that would only have information for tblEngines, but not for tblEngineDetails? This incorporates most of our legacy data.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
Ok, I'm beginning to understand where I'm tripping myself up. I was seeing index and thinking keys. So, is the relationship diagram in #19 correct? Or what needs to change to match your description in #23?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
#19 is fine provided the PKs and FKs are set as I described.

When you have a natural key that comes from a different system such as a serial number assigned by the manufacturer, you need to ensure uniqueness but you do NOT want to use the field as a primary key. In the case of Serial number, the values could be coming from multiple sources and you have no control over what scheme they use to assign the serial numbers so in order to keep a serial assigned by CompanyA from conflicting (duplicating) a serial assigned by CompanyB, you might need an additional field in that unique index.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
@Pat Hartman - Sorry, didn't mean to leave you hanging. I took a few early vacation days around the holiday weekend to put a dent in the project of painting our house (not as big a dent as one would have hoped, but c'est la vie).
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
So, just restating all of the scenarios.
1. One specific engine can be installed on exactly one unit.
2. Multiple specific engines can be installed on a unit.
3. One specific engine can be replaced with another specific engine (with history).
4. A unit does not have to have an engine at all.
5. A unit can have a generic engine without a specific engine.

I don't know that we have addressed how to make #5 work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
I explained how to do #5 a while ago. You create a unique but bogus serial number so you always assign from the junction table NOT the engine table. When/IF you get the real serial number, you replace the bogus number with the real number. I even suggested how to create obviously bogus but unique serial numbers.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
Ok. Thanks. I wasn't sure if that previous suggestion was still the way to go about this (assigning from the junction table w/ dummy unique s/n).

A slight zig if I may since this highlights more inexperience on my part in crafting queries. In this structure we've been going through there is the possibility of multiple engines assigned to the same unit. This concept applies to a few other pieces of equipment assigned to a unit. In the table structure, this information would be contained in multiple records (in this case the tblUnitEngine). How would I go about "flattening" this data for presentation? Meaning, creating a query where all of the assigned equipment shows up as a single pseudo "record" of the unit number similar to the output posted in #10? It may not be possible. But, I'd definitely like to know before I spend a bunch of time to arrive at the conclusion that it won't work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
Rather than flattening the structure for reporting, use subreports. The other option is the concat function which you can find by searching but it can be slow due to all the work it has to do. Depending on how many of these things you might have attached, you might get a good visual if the subreport has multiple columns Use the Z order for the columns. i.e. accross then down. The down then accross order will be a problem with a subreport.

PS, rather than having multiple attachment tables, add a type field to the Engine table and rename the table to Attachments or something more generic.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
I think you just officially overloaded my brain! Sub-reports! Why don't I know anything about sub-reports? Was I not paying attention? I presume their like subforms. *Sigh* It's times like these that I have to fight off the thoughts, "I know nothing! Why am I even trying to do this?" o_O

Off to the land of google for me...
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
PS, rather than having multiple attachment tables, add a type field to the Engine table and rename the table to Attachments or something more generic.
Your trying to hurt my head with this one too! ;)
I think I understand what you are going for. I just don't understand how adding a type field helps me accomplish this since I still need to have FKs for each detail table don't I?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
I would go back to the picture in #22. I really don't know how you got to this schema.

1. You have a generic engine with some attributes that apply to ALL instances of this type of engine.
2. You have a specific engine with a serial number and more detail attributes that apply to only a single engine..
3. What I said about the serial number is that you need to create a row in tblEngineDetails with a dummy serial number if you want to link the engine to an asset BEFORE you actually know the serial number. This sounds a little strange to me but not so strange that I think it is wrong. You know what kind of engine you want long before you know what specific engine arrived on the loading dock so this request isn't actually farfetched.
If you didn't need history, this would be the end of it. You would add the AssetID to the EngineDetails table to assign this specific engine to a specific asset. But for some reason you made a junction table. OK. You don't need it to implement this relationship as originally defined. BUT if you sometimes remove an engine from one asset and then attach it to a different asset or even put it back on the original asset, having the junction table works to provide history. So instead of putting the AssetID in the EngineDetails, you can put this FK in the junction table.
4. This is where we now have to be careful about our foreign keys and our unique indexes because you CANNOT have one engine assigned to multiple assets at the same time. You cannot do this entirely with Referential Integrity, you will need code in your forms to force an engine to have a value in the DateRemoved field before you can add a second instance of that specific engine to a different asset.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
@Pat Hartman - Sorry to be confusing you. My pictures of relationship diagrams are my attempts to model the table structure per your suggestions.

1. You have a generic engine with some attributes that apply to ALL instances of this type of engine.
Correct.

2. You have a specific engine with a serial number and more detail attributes that apply to only a single engine..
Correct.

3. What I said about the serial number is that you need to create a row in tblEngineDetails with a dummy serial number if you want to link the engine to an asset BEFORE you actually know the serial number. This sounds a little strange to me but not so strange that I think it is wrong. You know what kind of engine you want long before you know what specific engine arrived on the loading dock so this request isn't actually farfetched.
Understand the suggestion (dummy s/n's). As you say, strange, but effective and also similar to the suggestion of @The_Doc_Man.

If you didn't need history, this would be the end of it. You would add the AssetID to the EngineDetails table to assign this specific engine to a specific asset. But for some reason you made a junction table. OK. You don't need it to implement this relationship as originally defined. BUT if you sometimes remove an engine from one asset and then attach it to a different asset or even put it back on the original asset, having the junction table works to provide history. So instead of putting the AssetID in the EngineDetails, you can put this FK in the junction table.
"For some reason I made a junction table" - this junction table was made based on my understanding of this comment from you in post #7. (bold is mine)
tblEquipmentEngineSerial is the junction table btween a specific engine from tblEngineSerial and tblEquipment and includes the date on which it was installed. If you want to keep history of where an engine was installed at a particular time, you could do that by adding a RemovedDT field to this table. You might also want an OutOfServiceDT if you want to retire engines and not allow them to be moved to a different piece of Equipment.
If there is a different, non junction table way to model this data, I'm all ears.

4. This is where we now have to be careful about our foreign keys and our unique indexes because you CANNOT have one engine assigned to multiple assets at the same time. You cannot do this entirely with Referential Integrity, you will need code in your forms to force an engine to have a value in the DateRemoved field before you can add a second instance of that specific engine to a different asset.
I understand that I will need to enforce the proper data structure via VBA code in the FE to check that a specific engine is available to be assigned to a unit.

That leads us to this relationship diagram from post #19.
1631199515935.png


Here we have a unique autonumber PK for tblUnitEngine so that it works better with combos. We have two FKs to connect the junction table. That aligns with this comment of yours from post #23.
I would NOT use a natural key instead of an autonumber as the PK for tblUnitEngine. Leave the autonumber as the PK. But make a unique index on the THREE fields I mentioned - the two PK being connected AND the date the engine was installed. This satisfies your history as well as the multi-engine case.
....
The reason I use autonumbers plus a unique business rules index for junction tables is because of the way combo's work. They ASSUME single-field unique index. Without that, you may have trouble using them.
All of this makes sense and I believe I understand everything.

However, that still leaves this final comment from post #31 for me to understand:
PS, rather than having multiple attachment tables, add a type field to the Engine table and rename the table to Attachments or something more generic.
I presume this comment was made to highlight an efficient way to handle attaching other equipment that would be structured similarly. While I think I understand the concept (it's similar to how you structure your lookup list table), this particular implementation I don't fully understand because I don't quite see how to make all of the relationships work. So, I created the final relationship diagram in post #35 as an attempt to model what I thought you were suggesting and also to highlight that clearly I'm missing something important in order to understand your "type field" suggestion. I created two additional fake detail tables for compressors and bottles to use to model the relationships. They are meant to be equivalent to tblEngineDetails.

Setting aside your "type field" suggestion for a moment, adding the additional detail tables would yield the following structure:
1631199591413.png

(EDIT - Replaced picture with one with the correct PK-FK relationship for tblUnitBottle)

Now, we have 3 "attachment" tables as you called them. If I'm understanding correctly, your "type field" suggestion is meant to combine these three "attachment" tables into one table and the "type field" is what makes that possible. So, now we've come full circle to my relationship diagram from post #35.

1631199622686.png

This is where I'm struggling to see how to relate these tables to accomplish the same things as having 3 "attachment" tables.

I hope this extra long post has made things clearer.

Edited to replace screenshots that disappeared somehow.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
"For some reason I made a junction table" - this junction table was made based on my understanding of this comment from you in post #7. (bold is mine)
I didn't say you needed a junction table. I said the junction table ALREADY existed. The details table is essentially a junction table because it has a FK to the engine table (1-m) AND a FK to the asset table (1-m) which is the halmark of a junction table. This one is a little different though since applying the proper unique indexes on this table will prevent a general usage and restrict it to a specific serial number. That is why you need the unique index. Technically, the serial number could be the PK of this table and then you wouldn't need the unique index except that I would not recommend making PKs on data that you do not control. The serial numbers are issued by the manufacturer and they can change their format at will. You do NOT want a change of the format of one manufacturer's serial numbers to force you to modify the FKs in your database. So we leave the Serial as data with a unique index and create all relationships using the autonumberPK which you control. You didn't need a second one Then you said you wanted history so I approved the new junction table because it would allow a m-m between the details and the asset. If you do NOT need history, you do NOT need this junction table since you do NOT have a m-m between details and assets. Technically there is a m-m between engines and assets but that relationship is NOT physically manifested because the relationship is not between a generic machine and assets. It is between a specific machine and assets.

I suggested that you think about a single table for all components rather than one for engines and more for each type of components. Think about how Sikorsky would handle this. Do you think they have a table for engines. A table for rotors. A table for radios, etc. No. They have one table for parts - PERIOD. And so should you. Parts have many common attributes and even more unique ones. The common ones all go in the table previously named engines. Other details can go into comments since this application will not likely be doing anything with them. They are just part of the description. You would need to add a new column to the "engine" table to specify what type of component the record is for. So far I've seene Engine, Compressor, Bottle. Make a lookup table to ensure only valid values are stored.

If you want to keep additional details, then you would create a separate table using an ultra-normalized format:

tblSpec
SpecID (autonumber PK)
engID (FK to what used to be engines)
SpecName (Length, width, speed, the name for whatever attribute you are describing) (make a lookup table to ensure that only valid values are stored)
SpecValue (make this a text field regardless of what it will hold)

Keeping these details will give you a way of finding certain components with like attributes.

I have no idea what "test" something is. You originally called the main "thing" an asset. Now you're using different terminology and making multiple types of them.

Start by answering the history question. Do you actually need history? If you don't, then you would remove the tblEngineUnit table and put the UnitID in the details table.
 

JMongi

Active member
Local time
Yesterday, 23:16
Joined
Jan 6, 2021
Messages
625
I didn't say you needed a junction table. I said the junction table ALREADY existed.
And I tried to spell out in great detail why that is not true. :) I'm saying that it did not already exist. It was created within the context of this thread. Perhaps I was a bit unclear in quoting your post #7. At the time you commented in post #7, I hadn't posted any relationship diagrams. The closest I came to a table structure was in post #6. No junction table in sight. I was going back and forth with @The_Doc_Man. Then you graciously entered to help as well with post #7. So the idea for the junction table specifically came in response to your post #7, me modelling it in Access and then posting a screen shot of the resultant relationship diagram to verify that I was understanding your post correctly. That's all I can say. I appreciate that you are busy and volunteer your time to assist us inexperienced ones. I'm trying to be very correct here so that we don't waste our time figuring out a junction table that may potentially be unnecessary.

I have no idea what "test" something is. You originally called the main "thing" an asset. Now you're using different terminology and making multiple types of them.
Wasn't trying to be confusing. These were just test tables to help me visualize what you were suggesting. So, I called them test.

I suggested that you think about a single table for all components rather than one for engines and more for each type of components. Think about how Sikorsky would handle this. Do you think they have a table for engines. A table for rotors. A table for radios, etc. No. They have one table for parts - PERIOD. And so should you. Parts have many common attributes and even more unique ones. The common ones all go in the table previously named engines. Other details can go into comments since this application will not likely be doing anything with them. They are just part of the description. You would need to add a new column to the "engine" table to specify what type of component the record is for. So far I've seene Engine, Compressor, Bottle. Make a lookup table to ensure only valid values are stored.

Thanks for the clarification! I now understand what you were originally referring to in your postscript. I'll think more on this. It definitely makes sense.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:16
Joined
Feb 19, 2002
Messages
33,219
I said that tblEngineSerial IS the junction table but you later added a second junction table to the right of tblEngineSerial. THAT is the extraneous table.

You still haven't clarified the need for history. You indicated that you thought you might. Don't let me talk you into it. It just complicates the schema so you should add it only if you actually have a requirement to track what components were installed on which assets and when.
 

Users who are viewing this thread

Top Bottom