How to relate these two tables? Somewhat Self-Referencing (1 Viewer)

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
I hope this is the right forum for this. If not, please feel free to move this thread to a more appropriate location.

Since database design requires some knowledge of business rules to be effective, I'll try to summarize what's going on as best I can without getting too bogged down in the details.

Scope
This database overlaps two different areas so hopefully it's not trying to bite off more than it can chew. This database is meant to accomplish two primary goals:
1. We have a current Word document with a table in it :eek: that is used as a master list of our units. Units are our primary business and are a piece of machinery that is either leased or sold to other third parties. These units have various attributes associated with their use. They also have various attributes associated with their use by our customers. All of these attributes are stored in this list. Goal 1 is to move this list into a database setup so that there is 1 master repository of information and then this repository is used to generate any other needed reports/lists. (Sounds like a database doesn't it! ;))

2. We have a variety of major components that go on these units that need maintained/monitored, various shop equipment that needs the same, and the units themselves are in various states. While it is not going to be a full asset tracking system, the main idea is to manage the maintenance/tasks associated with this equipment. We have ZERO system for this right now. We just started to do a better job of assessing units going out and coming in. So unfortunately, that means that there aren't a lot of robust business rules to lean on to develop this system.

Right or wrong I've already done a lot of work setting up the database and I think it captures what we need fairly well. I'm going to assume :ROFLMAO: that I did an okay job. I do have a wrinkle I'm trying to sort out. It involves the relationship between our units and my master equipment list for the maintenance portion.

Equipment List
The equipment table contains EqID as a PK and a subsequent list of basic details. I have additional tables for each equipment type (Vehicle, Engine, Compressor, etc) with their specific detail fields. These are connected via 1:1 relations using EqID. Pretty basic stuff.

Unit List
The master unit list was more difficult because of its variability on the structure of the unit itself which makes capturing its major components a bit trickier. Thankfully, the units lend themselves into subdividing the tricky parts and I was able to create a structure I believe will capture the data without duplication.

One tricky part is that some of the equipment in the previous list will be attached into these various subdivisions i.e. a specific engine will be attached in the drive subdivision. So there is a 1:M relationship using EqID and the subdivision field on the unit. Like I said, though, I think this is sorted.

The Question
So I have a table "UnitBasicInfo" and a table "UnitAssyInfo". They are connected 1:1 through the "UnitID" field. The UnitAssyInfo table is the one that manages the relations to the various equipment that could go into the unit. How do I connect the Unit to my master "Equipment" table? Do I place an EqID field in UnitBasicInfo and make it the primary key. Will that affect how UnitBasicInfo and UnitAssyInfo get related?

If I can clarify or need to adjust something for forum rules, let me know. Thanks!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
I am a big fan of self referencing tables when it makes sense. However, they can make things easier or harder. As you pointed out if you know the max amount of levels and the types of levels, you may be better off with multiple tables. It can be a trade off. If I have an unknown amount of levels
assembly
-- sub assembly
---- sub sub assembly
-------sub......

Then having a single table to do this can be much easier. However, queries become more difficult since Access does not have any native recursive queries. If you can move to SQL Server you have much more capability.

With everything said you probably need to show us your table structure or post a sample. If you have a true self referencing, that treeview class may be of interest.


 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
Here is the relationships view from Access.
MaintDB.PNG
 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
Well, tell me if this is a bad idea but...
1. There should have been a 1:1 between UnitID's in the UnitAssyInfo and UnitBasicInfo tables.
2. I added and EqID to UnitBasicInfo and made it the PK
3. I made sure UnitID in UnitBasicInfo was no duplicates so I could use it as a 1:1 with the other tables as previously done.

No arguments from Access so far. Any problems with what I did? Here's the updated relationship picture:
MaintDB2.PNG
 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
Another related question...
The Unit number (UnitID in the database) is basically the serial number for these units. I originally had a serial number in many of the detail tables of the equipment subtypes, then moved it back into the main table. I suppose it doesn't REALLY matter as the serial number could be left blank. But then I'd have 2700+ blank serial numbers in the equipment.........I just remembered why I didn't have an EqID in the first place. Wow, sorry to waste everyone's time. I didn't need to create a separate EqID for our units. They are their own entity. I just need to refer to either an EqID OR a UnitID when creating a maintenance task.
 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
Here's the updated schema.
I'm sorry for those that took the time to try and sort out my ramblings. I had forgotten what I intended to do and was interrupted before I went on vacation 2 weeks ago.
Thanks for those that burned brained cells in my behalf!
MaintDB3.PNG
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:56
Joined
Sep 12, 2006
Messages
15,613
out of interest

"UnitBasicInfo" and a table "UnitAssyInfo". They are connected 1:1 through the "UnitID" field

if you have two tables connected 1-1, why do you need both tables separately? Why not put all the information into just one table?
Sometimes you might do this, but the only real reason is that

a) there are too many fields to fit in one table or
b) you need some of the information to be confidential.
 
Last edited:

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
In this particular case, we have legacy data in a spreadsheet style layout. This information can (more or less) be imported into the "UnitBasicInfo" table. We have over 2500 entries in this table. We have very little actual "UnitAssyInfo" and what we do have is not centrally stored. So, this table would have a lot of blank fields in those 2500 records.

Additionally, unless there is a downside to the 1:1 relationships, breaking it up this way let me better visualize the relationships needed to normalize and represent the assembly data which took me a little while to wrap my mind around.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
We have over 2500 entries in this table. We have very little actual "UnitAssyInfo" and what we do have is not centrally stored. So, this table would have a lot of blank fields in those 2500 records.
The additional tables seem to be specific properties to those types of systems. One thing that may (or may not) simplify this would be to turn those extra tables in Entity Attribute Models / Entity Value Models. Then you could have a single table to hold these extended properties. I am not suggesting this is the correct way to do it because EVM/EAV have pros and cons which you have to weigh. They can simplify things where you have lots and lots of measurements like you have. You can search this forum for some many discussions and examples.
 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
Thanks for the forum links. I will check that out. Reading about Supertypes / Subtypes is how I figured out to structure my Equipment tables the way that I did.
 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
I believe it's fair to say that this conversation has morphed and moved into the following thread in the tables section...

Thanks to all who have commented so far! I really appreciate the help and patience!
Jim
 

Steve R.

Retired
Local time
Today, 08:56
Joined
Jul 5, 2006
Messages
4,617
Another related question...
The Unit number (UnitID in the database) is basically the serial number for these units. I originally had a serial number in many of the detail tables of the equipment subtypes, then moved it back into the main table. I suppose it doesn't REALLY matter as the serial number could be left blank. But then I'd have 2700+ blank serial numbers in the equipment.........I just remembered why I didn't have an EqID in the first place. Wow, sorry to waste everyone's time. I didn't need to create a separate EqID for our units. They are their own entity. I just need to refer to either an EqID OR a UnitID when creating a maintenance task.
In your image, post #6, the primary key has labels like "UnitID" and "BotID". In your post cited above: "The Unit number (UnitID in the database) is basically the serial number for these units." Are these values actually an identifiable element of the item or are they independent of the item?

The reason I bring it up is that I am advocate of the primary key (PK) not being something that could potentially change, like an actual serial number, model number, or something similar. Nothing wrong with labeling a PK as "UnitID". However, the PK should be an "invisible" value that does not associate with an attribute in the item (product). Normally, the PK field would be "invisible" to the user.
 

JMongi

Active member
Local time
Today, 08:56
Joined
Jan 6, 2021
Messages
802
The image from #6 is a bit out of date, that's why I posted the other thread.
 

Users who are viewing this thread

Top Bottom