Does this structure make sense? Storing IDs from various lists of items. (1 Viewer)

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
Here's a simplified version of part of my DB structure via record, not by fields:
Equipment
EQ1
EQ2
...

Facility
FC1
FC2
...

Unit
0200
0201
...

Vehicle
VE1
VE2
...

MaintTaskLog
MTL1
MTL2
....

Each of these tables has unique ID's to identify the record as you would expect. In order to construct a maintenance task log (MTL) record via form, one of the necessary tasks is selecting which item from the other four tables above is receiving said maintenance. It seemed the way to do this would be:
1. User selects one of 4 categories from combo box (Equipment, Facility, Vehicle, Unit)
2. This selection populates the second combo box with the appropriate list query.
3. User selects the appropriate item from the second combo box.
4. After this selection is made, the appropriate ID is pulled from the query ready to be stored in the chosen field in the MTL table record.

Is this the right type of process? I thought I would double check before I went to far down the form rabbit hole for the Task Log.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
14,860
Hi. Just a guess, but I think that may be backwards. I would expect a child table of maintenance for each category. For instance.

tblEquipment
EquipmentID, PK

tblEquipmentMaintenance
EqMaintID, PK
EquipmentID, FK
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
So your saying that I should have a separate (what I'm calling a log) table for each category? Interesting, I guess I never thought of it that way.

I've been researching the idea of multiple FKs related to a specific table field but its hard to ingest because they usually described via SQL which is harder for me to translate. There appear to be a few different approaches, each with their pros/cons depending on your usage case.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
Kind of thinking/writing out loud now...so, I would just construct the appropriate queries so a singular "New Task" form could be used to create all new maintenance tasks for any category? The tricky part for me is that this seems to make expanding the categories in the future more invasive.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
@theDBguy - I see the idea of your suggestion. It's hard for me to ignore the duplication. The data itself isn't duplicated but having (in this case) 4 log tables whose only difference is the FK name seems like their has to be a more efficient way. Any new category then needs an additional new table and the same amount of duplicated fields. That seems to against the spirit of a normalized relational database even if it doesn't violate the rules.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:50
Joined
May 21, 2018
Messages
4,879
I have done something similar in a couple different ways to see what is doable. You can make them work, but they have pros and cons especially maintaining referential integrity.

I have made the data table hold foreign keys to different tables with an identifier to which table it came from.

MaintTaskLog
.... maint task information
TableNameIdentifier (tells which table the foreign key relates to)
foreignKey

So if you can join to the vehicles table where tablenameidentifier = "Vehicle". You end up with a union query to bring this all together. Building a form to support this requires some workarounds.


I think you were talking more like

MaintTaskLong
.... maint task information
TableNameIdentifier (tells which table the foreign key relates to)
VehicleID_FK
EquipmentID_FK
FacilityID_FK

This will work and I think better than the first method. Again you may need a union query to show all this information at one time. If you linked to each foreign key all at one time to each table, you would get a bunch of columns.
I have to think what is the downside of this approach.
I think you could make your form such that you pick a category and only see a way to pick the appropriate FK.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:50
Joined
Feb 19, 2002
Messages
31,689
This is a situation where I would use an entity table. The entity table contains all the common fields, a field that identifies the entity type, Vehicle, Equipment, Facility, etc., and assigns the PK. Then each of the other tables includes a foreign key to the entity table. When you create a maintenance task, you choose the entity from the entity table and the EntityID is the single foreign key.

When you are working with maintenance tasks, you would use a subform to show the data from the specific type. So if the form is showing a maintenance task for a vehicle, the subform will show the vehicle data. If there are only a few different types, you can stack the subforms. Only the relevant one will actually have data and in the current event of the main form, you would make the appropriate subform visible.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
@MajP - We kind of cross-posted. I will read your post in greater detail.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
@PatHarman - I think I started out with that in mind, moved away from it, and am likely coming back to it.

I guess I was internally struggling as to what to call that entity but it does seem to make the most sense and simplify things.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
For those reading the linked article, I believe Pat's suggestion is one of the "belongs to" type of arrangements. Since the discussion is in SQL I struggle to fully take it in. Thus the "believe".
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:50
Joined
May 21, 2018
Messages
4,879
@MajP - We kind of cross-posted. I will read your post in greater detail
If I read the article correctly, the first example I mentioned would be "join table per relationship type". The second was the "Exclusive belongs to" where you include every foreign key table, but force it so you can only pick one of the foreign keys. The author supports the latter.
Do you have specific concerns about holding all of the FKs?
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
It's an interesting topic to be sure. Now that I have some DB nomenclature to search with, I'm going to see if I can find an article that isn't as heavily descriptive in SQL. I spend too much time stretching my very limited knowledge of SQL and not enough time understanding how the data is related in each scenario.

I too believe that the "Exclusive belongs to" structure is what makes sense for this application and I think that is what @PatHartman was recommending too unless I misunderstood. I'm going to search for "Exclusive Belongs To" and "Exclusive Arc" and see what I can find.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
So, all of the articles I've found so far seem to reference each other. So I run into the same SQL/code heavy "examples". That being said, I think writing down some truths about this particular data might help point to the "proper" or "best fitting" implementation. I think it would be particular relevant for the very experienced ones who are offering their thoughts as my simplified example may not be good enough to highlight which approach fits best. Here is a picture of the current table structure.

TaskScheme.PNG

My original solution is something similar to what @Pat Hartman is suggesting (I think). The "Equipment" table is the umbrella that all other categories fall under. The downside is that I don't think certain categories should really fall under this umbrella. Units and facility items (just a placeholder at the moment) wouldn't really use a majority of the common fields in the equipment table. Maybe I'm being too particular.

1. The maintenance task record itself is unrelated to a specific thing to be maintenanced.
-For example, a record in "MaintTasks" could be "Rotate Motor" with a description to rotate the motor 360 degrees and lubricate the bearings. It might have a frequency (every 3 months) and a duration (15 minutes).

2. The maintenance log record is what is related to the maintenanced thing.
-A MaintTaskLog consists of a maintenance task and a thing to be maintenanced. This log entry is created when a maintenance task is scheduled. You could say that this record would have a natural key consisting of the task, item and schedule date.
-Then this log record is updated as the task is processed.

The main reason I made this post is to see if there is a different way to accomplish this without jamming everything under the equipment umbrella. Just thinking about the data itself, it is logically grouped via equipment, units and facility.

Hopefully, this more specific information will illuminate the path forward. Thanks again for burning your brain cells on my account. :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:50
Joined
May 21, 2018
Messages
4,879
I will ask the question again. Do you have concerns of just having each foreign key in the the maintenance log, and limiting the user to just being able to only have one selected? I think that is what the author was recommending and you can enforce both table level and "form level" data integrity? Personally I do not see the benefit in what Pat is suggesting. I would go that approach if all tables hat a subset of like data, but I do not think this is the case.

This makes forms, queries, and table structure simple IMO. You just have to do a union to show all logs at once if you are showing fields from all tables.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
@MajP - Sorry, I didn't mean to ignore your question. I didn't have an adequate answer. I was still wrapping my mind around the possibilities pros/cons.

Here is a counter point to the Exclusive Arc idea


As with most of these types of questions, it's weighing the pros and cons. I am far from an expert but I think I could simplify the "Exclusive Arc" debate down to - If your check logic fails for some reason (due to mistakes or maliciousness) than you can easily introduce data corruption into your database.

To specifically answer your question, I don't have a particular issue with the idea of all FKs (in this revised thread down to 3) and preventing the user from selecting more than one. But, I'm going to think further on it.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
@Pat Hartman - Could you possibly elaborate further on your entity table recommendation? It's the one I understand the best and it's the way I was already approaching my structure (to a point). How would you handle the situation in my structure posted in #14?

A unit, which would be a type in the entity table as I understand it, wouldn't use many of the common fields in the Equipment table.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:50
Joined
May 21, 2018
Messages
4,879
As with most of these types of questions, it's weighing the pros and cons.
That is actually my question. I am not suggesting any solution is the "right" solution. In these kind of non traditional designs there are pros and cons. The choice of how you go needs to be IMO based on pain vs gain. You have to look forward and think about you will use the database to include data entry, querying, and reporting.

An example is a Entity Attribute Value model. That is not a normalized design. It will require non traditional queries, table structure, and code to validate data. That can be a lot of work. However you can reduce hundreds of fields and make developing forms far easier. It also provides flexibility to add or subtract measures without table redesign. There is not yes are no answer on when to use that. If you are not savvy in code and queries this may be beyond ones skill set. If the amount of measures is 5, 10 20, 100 ... you need to decide your breaking point. So how big, how you plan to enter data, report data, etc will help make the decision.

Hierarchical data is another. It is the most efficient to do a self referencing table but if you cannot do recursive code then this may not be the way to go. If your max levels is 5, 10 ,.... you need to decide at what point you want self referencing vs individual tables.

So I am not saying this approach is the correct approach, but asking what if any issues specifically concerns you. (Data integrity, form design, queries).
I can make any of those approaches work, and all have pros and cons, but from what you show I think storing each foreign key has the most pros and least cons. Again, you have to look at the down stream effects in how you plan to use the database. You have to project to make a decision.
 

JMongi

Member
Local time
Today, 04:50
Joined
Jan 6, 2021
Messages
335
I see your point. Unfortunately, it's a bit like predicting which car will suit your future needs the best while learning how to drive!

I'll parse your post a little further and see if I can provide some answers to help weigh the pros and cons.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:50
Joined
Feb 19, 2002
Messages
31,689
Some common fields might be nomenclature (object's name), year placed in service, and Type (vehicle, Unit Facility)

tblEntity
EntityID (autonumber, PK)
Nomenclature
EmployeeID (FK to tblEmployee) (facility manager, assigned to for equipment or vehicle)
MfgYear (year built/manufactured)
InServiceYear (used for calculating depreciation)
EntityLife (used for calculating depreciation)
Type

tblFacility
FacilityID (autonumber PK)
EntityID (FK to tblEntity) (add a unique index)
Addr1
Addr2
...
tblUnit
UnitID(autonumber PK)
EntityID (FK to tblEntity) (add a unique Index)
SerialNumber
....
tblVehicle
VehicleID (autonumber, PK)
EntityID (FK to tblEntity) (add a unique index)
Model
VIN
...

The maintenance log and any other tables that need to reference ANY entity will use EntityID as the FK. This will allow RI to be maintained. the relationahip between Entity and the other tables is technically 1-m but adding the unique index on EntityID effectively turns it into a 1-1. I think you can't have multiple 1-1 to the same"left-side" table and the unique index solves that problem.
 

Users who are viewing this thread

Top Bottom