Table Structure to use two different lists in a drop down.

[JMongi]

I might be overthinking this, but my normalization/database thinking patterns keep atrophying between opportunities to work on this project. My apologies in advance if this is a no-brainer.

One of the struggles is capturing/normalizing existing data while simultaneously setting up the structure for the future (better way) of capturing the data. I need to be able to use the legacy data while we begin to populate the newer information. I'll give an example.

We have equipment with industrial engines mounted on them. Our legacy data lists the manufacturer and model number of the engine for all such equipment we've ever produced. Useful data that's used frequently. This data is also duplicative from a database standpoint (i.e. the same mfg/model can be used on many pieces of equipment). So, on its own, there would be a table of these mfg/model combos and use that table to populate the "engine" field in another table. All simple. Something like:

tblGenericEngine
geID
EngineManuf
EngineModel

But, we would really like to incorporate the individual engine specifics (serial numbers and the asset management that goes with it). That is obviously a more detailed table of info. How do I go about referencing two different lists (generic engine list and specific engine list) so that a piece of equipment can select either a generic engine id or a specific engine id. The other wrinkle is that while a generic engine could be used on multiple records of equipment, there would only ever be ONE specific engine with a piece of equipment.

I can't quite wrap my mind around how to set it up so that an end user when creating a new unit (or entering legacy data) can select a specific engine if its available or use the more common generic combos. (from a drop down box on a form). With the idea that in the future, the specific engine info WILL be known and then the record can be updated to replace a generic engine with its specific replacement.

I hope I've been able to explain that clearly.

 

[The_Doc_Man]

Use the same trick that was introduced in Arabic numerals - 0. It's a placeholder. Well, define a specific engine whose name is "Generic" and for which, if there is any baggage, it is like a serial number of all zeros, an asset tag of all zeros, etc. Here is the trick if you take that approach. Any relationships will either have to be OUTER joins (thus allowing display of records that have no more details) OR you carry the "dummy" record approach to its logical conclusion, allowing INNER joins to the dummy record.

 

[JMongi]

I think there may be some missing pieces to the explanation

To be clear, the "dummy" or "placeholder" is meant to be in the specific Engine table? Meaning, even though I don't have any specific engine information, I do KNOW it's a specific engine. I just only know it make and model at this time.

The OUTER vs INNER comment only confused me. I have a basic understanding of the terminology (though its entirely possible I don't!) but I didn't follow what trick you were suggesting.

 

[JMongi]

The tricky part (if I'm understanding correctly) is that makes converting the legacy data more challenging as I need to bulk create a bunch of specific engine records to be used in the top level equipment table.

 

[The_Doc_Man]

Yes, put the dummy in the Engine details table so that you can refer to it from other tables and later change the other tables to point to the correct (actual) engine once it is defined. Your guess about having to create specific engine records is probably true. (It's your project so I can't see it, but I will believe you without hesitation on that point.)

Sorry I went over your head with the INNER/OUTER JOIN stuff. Here's a quick and dirty summary.

When you create a relationship between two tables using the Relationships panel, the dialog that crops up lets you choose how you match records.

If you choose "show records only if both tables match" then that implies an INNER join. If X is in table A, the only way you would see a record at all is if X is also in table B.

If you choose "show all records from Table A (or B) and any matching records from table B (or A)" then that implies an OUTER join. What that does is that you declare the table where you show ALL records as the independent table, and the other table is a dependent table.

When I say "Implies a specific JOIN" that means that if you tried to build a two-table query, you have to JOIN the tables in some way or else you get a messy thing called a Cartesian JOIN or permutation JOIN, which multiplies the size of the return by a LOT. IF you have a relationship already defined in the Relationships panel, though, the query build sees that and builds an INNER or OUTER join for you. The builder will use LEFT or RIGHT JOIN based on which one of the two is the independent table.

This makes a difference because OUTER JOIN queries can have nulls in fields that WOULD have come from the dependent table except that there was no matching record. This is why you prefer to have a dummy record explicitly showing that something is not yet fully defined. That way you can have an INNER JOIN and not have any nulls come back in your query. Then in the independent table, when you HAD a pointer to the generic engine and now have a correct engine to use, you just change the pointer to the correct engine record.

Just as a side note, using INNER JOIN doesn't mean "unique" because that is a table/field thing, not a JOIN thing. Multiple independent records can point to the same dependent record. That is just a many-to-one relationship. Perfectly OK in this context.

 

[JMongi]

This might be where the world of Access relationships can't fully define what could be a proper query join between tables. Then again, it could be total rubbish!
This is why I wish I had more time to work on this project. I feel like it takes a full 8 hours to get my database sea-legs back under me. I understood the inner/outer definitions conceptually. It's another thing to think about their application in the real world. I guess my question is more pie-in-the-sky hopefulness. I envisioned a smaller table of generic engine definitions connected 1:M to my "EngineID" field of my equipment record. Then having a 1:1 of specific engine definitions connected 1:1 to the same "EngineID" field. Which I've since discovered is verboten! Then the user could click a dropdown menu and see the generics on top and any available specific engines on the bottom.

Let's flip this on its head since we've gone here. This is an exercise in trying not to shoehorn the database structure into preconceived hierarchal ideas. As you all know, that hierarchy can often be created after the fact as long as the identifiers of that hierarchy (used on, parent of, etc) are properly recorded.

In this case, instead of:

tblEquipment
eqID
eqInfo
engineIDFK

tblEngines
engineID
Engine Make
Engine Model

You could structure the same data:

tblEquipment
eqID
eqInfo

tblEngines
engineID
EngineMake
EngineModel
UsedOnFK (to eqID)

The benefit to this structure is easier mapping of the existing data (in an excel spredsheet) but I wouldn't be sure how to recreate the individual record look if the data was stored that way. Not even sure if that's a good way to do it (subjectively speaking). This is probably where I run into bogging down because I just don't have the real world experience handling different data and understanding how they can be modelled. It's like having a basic understanding of a jig saw, circular saw and band saw but having to relearn each time which job its best suited for (and how to use it). Can be so tedious. An experienced carpenter just goes about doing their job without the excess mental overhead.

 

[Pat Hartman]

tblEngines should hold all the generic info regarding a type of engine.
tblEngineSerial should hold the serial number, build date, etc. It has a FK to tblEngines

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.

I'm not sure what the purpose is for linking Equipment to an Engine before you know what Engine it will be. Can you explain that again please.

If you don't need history AND there is only one engine EVER associated with a piece of Equipment, then you don't need the junction table. You use a FK to tblEngineSerial and perhaps add an InstallDT field.

 

[The_Doc_Man]

This might be where the world of Access relationships can't fully define what could be a proper query join between tables. Then again, it could be total rubbish!

I will suggest that if the table structure is correct, Access will almost certainly be able to define it. The trick is defining the correct structure first. I think your first option is more typical.

Your original question boils down to this: You have physical engines. They are of several types. Your problem is establishing a relationship with an equipment record and an engine record. Sometimes you have all the data for a "real" engine with serial numbers and other data. Sometimes you have a "generic" engine that is a place-holder. If your Engine Details table (whatever it is called) includes a few engine classes so you have multiple placeholders, there is still nothing wrong with that. It is up to you as to how much info you put into a generic record.

The thing you DO NOT want to try is to have two tables of engine stuff, one generic and one specific.

(A) You can't split a relationship that way because there is no way to specify (at that level) that the partner table for the linkage depends on some other field to act as a "flag" for the direction of the linkage.
(B) Other than the level of detail there is no difference in data functionally between the generic and the specific engine.

 

[Pat Hartman]

You could use tblEngineSerial even before the Serial Number and dates are assigned. However, this impedes your ability to enforce RI because you would have to create a record before you have all the fields that should be required. You could populate all these fields with dummy values except that the Serial Number should actually be unique for an engine type. So, you will have to generate bogus unique serial numbers. Serial numbers should always be defined as text and probably be longer than the longest one you know of. This is a piece of data over which you have no control so you have to be able to accommodate changes made by the manufacturer without too much disruption. I remember what a PITA it was to change the format for the VendorSequenceNum at a certain client site.

Make the Serial number extra long, use some fixed number of leading zeroes followed by a - and the FK to the Equipment That will be unique and when the Serial Number is that format, you code can force the dates to be bogus so you can clear them when the real Serial number is entered.

 

[JMongi]

tblEngines should hold all the generic info regarding a type of engine.
tblEngineSerial should hold the serial number, build date, etc. It has a FK to tblEngines

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.

I'm not sure what the purpose is for linking Equipment to an Engine before you know what Engine it will be. Can you explain that again please.

If you don't need history AND there is only one engine EVER associated with a piece of Equipment, then you don't need the junction table. You use a FK to tblEngineSerial and perhaps add an InstallDT field.

I'll give it a go. I don't always say thanks for all the help from the various folks here. I know most (aside from Mr. Retired ) have real jobs that take up their time. So, I appreciate it.

In advance, I fully know this data isn't normalized for database use as presented. This is business context.

Here are a few lines from our list of unit numbers (our designation for a piece of equipment). There are 2000+ entries.

We have additional information in various unit specific "books" that contain much more information. But, they are not up to date (at least confirmed) and not easily available like this list (and my new db will be). Just trying to provide a little background. I've spent a lot of time and asked a lot of questions on this board and I feel FAIRLY confident in the overall table structure of how things WILL be. But, I don't want to maintain TWO "master" lists.

Step 1 is almost complete - Create the basic database app and create a generic table to make my database the master. I've imported our unit list into a table and have been keeping it up to date over the past months and now have automated export to excel files. This basically duplicates the existing plan but ensuring there is only one master source of data now. So, only 1 place to update.

Step 2 - This is where I am at right now. How do I transition my spreadsheet like data into the proper structure without creating more work than necessary. For example, to normalize the existing data would involve the basic engine info table with 1:M relationship where I can drop an appropriate make/model for each unit. But, that doesn't account for tracking individual engines/serial numbers/etc which is the end goal.

Maybe I'm beating my head against the wall when I need to walk through the door. Or maybe this is just the nature of converting legacy data into a database format.

I hope I've at least expounded on my dilemma a little more clearly.

 

[JMongi]

Your original question boils down to this: You have physical engines. They are of several types. Your problem is establishing a relationship with an equipment record and an engine record. Sometimes you have all the data for a "real" engine with serial numbers and other data. Sometimes you have a "generic" engine that is a place-holder. If your Engine Details table (whatever it is called) includes a few engine classes so you have multiple placeholders, there is still nothing wrong with that. It is up to you as to how much info you put into a generic record.

I totally understand what you are saying. But, how does that work when the relationship between the equipment record and the corresponding engine record needs to be 1:1 when dealing with unique serial numbers. That doesn't match with engine classes that can be used on multiple equipment records.

 

[JMongi]

Perhaps I just had a lightbulb moment...maybe...instead of relying on the db for referential integrity, I would use other data validation to ensure that an engine with a serial number isn't used in more than one place. That way the engine record table would still have a 1:M relationship with the equipment record table to use the generic make/model records in more than one equipment record.

 

[The_Doc_Man]

Perhaps I just had a lightbulb moment...maybe...instead of relying on the db for referential integrity, I would use other data validation to ensure that an engine with a serial number isn't used in more than one place. That way the engine record table would still have a 1:M relationship with the equipment record table to use the generic make/model records in more than one equipment record.

I think that is an excellent idea and a pretty good light bulb. You are facing a "lowest common denominator" problem in that you need - but due to that time-delay on declaration of linkage cannot have - uniqueness for engine linkages. You can't have two tables depending from one field in one equipment table based on engine number because you can't split the relationship, but you can only establish one relationship at a time. In theory, if you COULD do that, it would imply a 1:1 relationship between the two dependent tables, which clearly is NOT the case here. (See discussion below on 1:1 situations.)

There is one other possibility... From your discussion, ALL equipment has potential for an engine. So... have two foreign keys - one for the "generic" engine table to specify immediately what kind of engine is required and one for the specific engine table once one has been assigned. Then when an assignment is made, the "needed engine type" can be left exactly as-is because that hasn't changed. One extra LONG, which you can say points to a requirements table. BUT - because you cannot always know the actual/specific engine ahead of time, you cannot say "1:1" between equipment and engine. You can say 1:M where M can be 0. After all, from a logical viewpoint, 1:1 is a subset of 1:M, so you can have information be optional. The down side of this is that it might very well lead to some records having NULL in the serial-number or other specific attributes of the engine.

There is another consideration... it is very rare to actually assign 1:1 relationships. Generally, the theory says if you REALLY have a 1:1 situation, you should not have two tables, but rather merge everything. This comes from a consideration of what a Primary Key really means from the logic & structural viewpoint - that the two things related 1:1 are both dependent on the same PK and thus should be counted as one thing, not two things. Thus meaning they should be in the same table rather than dispersed into two tables. Kind of esoteric in a way - but that is the correct way to look at it.

 

[Pat Hartman]

Perhaps I just had a lightbulb moment...maybe...instead of relying on the db for referential integrity, I would use other data validation to ensure that an engine with a serial number isn't used in more than one place. That way the engine record table would still have a 1:M relationship with the equipment record table to use the generic make/model records in more than one equipment record.

Did you look at my suggested structure? You need to do this the same way that inventory applications do. You have a table that defines a part but which does NOT include the serial number. For serialized parts, you have a child table that lists all the serial numbers AND who/where they are assigned. It is the foreign key in the serial number table that points to the asset.

The relationship between asset and serial number is 1-many so there is no way to use a serial number in multiple places as long as you add a unique index on engineType or manufacturer (whichever makes sense) and serial number because that will prevent the serial number from ever being duplicated. Indexing just serial number might work but if the serial numbers are assigned by the manufacturor, you have no control over them and there is nothing to prevent different manufacturers from assigning the same serial number to different engines.

Technically there is a m-m relationship between engines and assets but you do not define it with RI. You make the relationship from serialnumber to asset to ensure that an engine is only assigned to one asset. That relationship is 1-m.

Look at the table you posted again. See if you can define which are attributes of the engine generically and which are attributes of a specific engine. The serial number is an attribute of a specific engine and so are some of the dates. Once you separate the fields this way, you will have more understanding of what I suggested.

 

[JMongi]

@Pat Hartman - I've reread your posts on the table structure and I just can't seem to convert the text into an actual structure in my head. I have a pressing task to accomplish this morning. Once its finished I'm going to revisit this and make sure I fully understand your suggestions.

 

[Pat Hartman]

I just can't seem to convert the text into an actual structure in my head.

Start the way I suggested. Make two lists. One of attributes that are generic and apply to ALL engines of one type and the second, much shorter list, will be attributes that apply to ONE SPECIFIC engine.

 

[JMongi]

tblEngines
engID
engMake
engModel
engCylNum
engDisplacement
engHP
engRPM
engTorque


tblEngineDetails
engDetailID
engSerialNum
engHours (This is a fairly dynamic field; updated frequently overtime)
engMfgDate
engRebuildDate

 

[Pat Hartman]

Using prefixes on table fields is annoying and just gets in your way. You have to type extra characters before you get useful intellisense and when you open a table or query all you see is the prefix. You see no meaningful names so you have to widen the columns to see any significant part of the name.

In the tblEngineDetails goes the FK to the asset table. This is the table that everything links to. The question is, do you need to add a row to this table BEFORE you have the engine serial number to set up the link to Asset?

 

[JMongi]

Yes, exactly (to your question).

This is what I came up with incorporating your post #7. I realize the engDetailID is likely duplicative from a key standpoint to what is supposed to be a unique serial number.

 

[Pat Hartman]

This is NOT a many-many relationship UNLESS a unit can have more than one engine. Can a unit have more than ONE engine at a time? The extra fields belong in tblEngineDetails. If you are in the habit of moving engines from one unit to another and want to retain history, that is a different issue. If you want to retain history, you can use the tblUnitEngine to do it but instead of having a unique index on eglIDFK it needs to be on engDetailIDFK + DateInstalled. Normally the junction table would have a unique index on the two FK fields to prevent duplication.

You need the extra fields so the specific engine can exist multiple times in the junction table.

Also, tblEngineDetails needs a unique index on Serial