How to relate these two tables? Somewhat Self-Referencing

[JMongi]

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 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 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]

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.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

 

[JMongi]

Here is the relationships view from Access.

 

[JMongi]

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:

 

[JMongi]

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]

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!

 

[gemma-the-husky]

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.

 

[JMongi]

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]

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.

record value in table when one of many buttons are clicked

That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great. I get that. Thank you for the effort of showing me a "map." Works well for me. If that is what we're all talking about then it may be conceivable to incorporate that...

www.access-programmers.co.uk

User Interface for Custom Properties

In my db, ive created a feature I'll call Custom Properties. This feature is intended to allow additional User Defined information to be captured about a particular entity and then the value(s) assigned to these properties to be used at decision points in processing logic. An individual...

www.access-programmers.co.uk

 

[JMongi]

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]

I believe it's fair to say that this conversation has morphed and moved into the following thread in the tables section...

Do these table designs makes sense? Various parts of my schema

So, I'm at the point of putting some test data into my database structure to make sure that it works as expected and so that I have some data to work with as I start adding subforms to my main navigation form. Since I'm new to creating a properly designed database from scratch, I thought I...

www.access-programmers.co.uk

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

 

[Steve R.]

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]

The image from #6 is a bit out of date, that's why I posted the other thread.

 

[JMongi]

Thanks for the input though!