Do these table designs makes sense? Various parts of my schema (1 Viewer)

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
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 would toss up a few snippets of my schema and make sure that my approach makes sense. Now is the time to sort this out if there are any lurking issues.

First up are the tables that define the various equipment that needs maintained (this is a maintenance tracking database more or less). The idea is that there are common attributes to all equipment (Unique ID, Type, Date Acquired, etc) and then subgroupings of equipment that have common attributes (vehicle type, make, model, year, etc). As I understand it, if I relate these various tables via 1:1 relationships through EqID as shown below, then while there must be a record in "Equipment" for every EqID, each subgroup should only contain records for their specific types tied to that unique EqID. Is my understanding correct? Do I have this structured correctly to accomplish this?

EqRel.PNG
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
To clarify my expectations:
Code:
Table 1:EqID, Name, Description, Type
Table 2:EqID, VehMake, VehModel, VehYear
Table 3:EqID, EngineMake, EngineModel, HP

So there could be two records in equipment, one for a car and one for an engine.
There would be 1 record in table 2 for additional car details.
There would be 1 record in table 3 for additional engine details.
 

plog

Banishment Pending
Local time
Today, 06:01
Joined
May 11, 2011
Messages
11,613
As I understand it, if I relate these various tables via 1:1 relationships through EqID as shown below, then while there must be a record in "Equipment" for every EqID, each subgroup should only contain records for their specific types tied to that unique EqID. Is my understanding correct? Do I have this structured correctly to accomplish this?

A 1:1 relationship is unusual to have, it means you only have at most 1 record in table A that relates to at most 1 record in table B. Normally wouldn't use table B and just put all the data in table A. So, does every piece of equipment only have 1 Vehicle? Must it have a vehicle record? Now answer that question for every table in a 1:1 relationship with Equipment (e.g. Engines, CompCylinders, etc).

From there, I have 3 other questions:

1. Can equipment have multiple InService/OutService periods?

2. What are values for EqStatus? Can that be determined by other data in the table (e.g. DateRemoved, DateInService, DateOutService, etc)

3. Engines has a Cylinders field, but you also have CompCylinders table--why?

Lastly, your clarifications made it harder. Don't genericize your tables (Table1, Table2, etc.) after you've specifically named them for us. Once you do that just reference their actual names as they relate to your diagram.
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
Sorry, site was down couldn't respond to your post. I will now.
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
1. Yes
2. I don't have the status' defined yet. They will be a list in a separate table like EqType. Some may be able to be programatically assigned.
3. I didn't get into the nuts and bolts of the business. I know that is helpful for table design in general. Short answer, two different cylinders, two different attributes.

To clarify using my table:

Code:
Equipment: EqId, EqType, EqNotes, ...,
Engines: EqId, Cylinders, Horsepower, Type
Compressors: EqId, Stages, Type, Frame Number, ...,

A record in the Equipment table defines the attributes of a unique piece of equipment. The fields in the equipment table are generally applicable to all equipment.
A record in the Engine table is always a piece of equipment. The fields in the Engine table are specific to engines and would be blank for all other equipment types.
A record in the Compressors table is always a piece of equipment. The fields in the Compressors table are specific to compressors and would be blank for all other equipment types.

To avoid these groups of blank fields, I created the 1:1 structures you see in the diagram. It is my understanding that this will allow the following data to be stored:

Equipment Table:
Record 1: EqID = 001, EqType = Engine, various general attributes
Record 2: EqID = 002, EqType = Compressor, various general attributes
Record 3: EqID = 003, EqType = Engine, various general attributes

Engine Table:
Record 1: EqID = 001, various engine specific attributes
Record 2: EqID = 003, various engine specific attributes

Compressor Table:
Record 1: EqID = 002, various compressor specific attributes

Is this correct?
Or would there be essentially blank records in the Engine and Compressor tables for all records in the equipment table that are of a different type?
 

plog

Banishment Pending
Local time
Today, 06:01
Joined
May 11, 2011
Messages
11,613
With that last post, I think you have set them up correctly and all those 1:1 tables are correct.

Since a piece of equipment can go in and out of service multiple times, I would suggest a seperate table to track that if the historical in/out periods are needed. If you don't care about that then your structure is fine.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:01
Joined
Feb 19, 2002
Messages
42,981
Your design is pretty sophisticated for a first attempt. I agree with plog about a service history table being useful.
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
That's my problem really. I learn fast enough to attempt sophisticated...then...I bite off more than I can chew!
The service table idea is a good one. I'm going to incorporate that. There is going to be some change logging that I want to incorprate in certain ones as well. I have a hard time doing anything halfway. Its both good and bad sometimes.

My next post will be another section of the schema that might illustrate what I just said o_O.
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
Ok. Had a few other projects encroach on my time. They are wrapped up so here is the next part of my schema. This is the maintenance task portion of the schema. Their are other tables connected to the "Equipment" table as you've seen previously. There are other tables related to "UnitBasicInfo" as well.

TaskLog.PNG


The idea is that a general task is created. For example, the task "Rotate Electric Motor By Hand" would be created in the "MaintTasks" table. There would be a general description of this task. In the future, I might expand to include specific steps on the task itself or a field for the filename of the task procedure. In any case, once a task has been created it can then be scheduled to be accomplished. The idea behind this setup and the junction table "MaintTaskWork" is that the same task can be scheduled multiple times and/or work performed by multiple employees. So, the "MaintTaskLog" table is the main repository of specific work progress on a specific instance of a task.

This part is tricky because I'm basically creating the business rules at the same time as schema since we have no current maintenance program to model.

So, a few related questions:
1. I would like to limit the "Equipment" field in "MaintTasks" to be only existing Equipment from the "Equipment" table or units from the "UnitBasicInfo" table. Are my table relationships setup properly for this? I ask because as I've tried to add some sample data into my database I'm getting errors telling me I need to add data in other tables. So I'm wondering if I have things related correctly.
2. I would like to have a log of the edits and status changes to the various general tasks. I have one page bookmarked on logging, I'd love for some additional references. Ideally, not too complicated.

Thanks again for everyone's input.
 

plog

Banishment Pending
Local time
Today, 06:01
Joined
May 11, 2011
Messages
11,613
Overall, it looks really good; and although I had to slowly reread your description a few times while refering to the image-- it makes sense. And for your questions:

1. No they are not set up correctly. You shouldn't link 2 tables to 1 field like that. I would add a new field in MaintTasks for UnitID. Technically you could merge the data in Equipment and UnitBasicInfo into one table--which as an outsider looking in seems nearly impossible, but you know your data best.

I have a question about these tables--I understand that 1 piece of equipment can go to many tasks, but couldn't many tasks use the same equipment? Should there be a junction table between those?

2. I don't have anything in particular, but I've seen many threads on this site, search for "transaction log".
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
@plog
1. It's possible that my idea is just a bad way of looking at it and I need to approach it differently. The data probably COULD be "merged" in that I could handle units as I do other equipment i.e. assign a unique EqID and the unit number becomes the defacto serial number. The UnitBasicInfo data becomes specific attributes for a subtype of equipment just like the other equipment subtypes (Engine, Compressor, etc). While that would make sense to me as the DB designer, I can forsee users wondering why they have to remember the EqID of a unit when they are very used to dealing with just the unit number. A unit number is our own assigned unique ID for our final assembled equipment package that we lease or sell to our customers.

Replying to Your question - Yes, that is very true. It does sound like I need a junction table. I'll have to revisit that and think about the relationships. This is where my inexperience in the shorthand of data relationships makes this take more time than it normally would.

2. Thanks for the suggestion on a search term to use.

Circling back to to your main thought from #1...

Setting aside my specific design needs, how would one normally go about accomplishing the basic idea I'm trying to do (only be able to select an EqID or a Unit Number when creating a new maintenance task) or is that just bad database design? I'm beginning to come around on the idea that it needs to be a subtype of equipment but that I can "hide" that fact when displaying the data in forms to the end user i.e. they don't need to see the EqID when they are dealing with units. It just needs to be carried around in the background to make the relationships work.
 

plog

Banishment Pending
Local time
Today, 06:01
Joined
May 11, 2011
Messages
11,613
how would one normally go about accomplishing the basic idea I'm trying to do (only be able to select an EqID or a Unit Number when creating a new maintenance task) or is that just bad database design?

I would try and get them into 1 table if at all possible.
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
Hmm...I originally had a "master task" vs "scheduled task" table setup that I got rid of because it wasn't quite working right/making sense. Now I think I know why. I had set it up because it makes sense for there to be a master "rotate motor" task that could be applied to any motor. But I wasn't thinking of it in terms of a junction table. Now that I have that idea more clearly in mind, the right way to set it up may be clearer. I'll post back when I have a revised version.
 

JMongi

Active member
Local time
Today, 07:01
Joined
Jan 6, 2021
Messages
802
As an aside, there are "similar threads" showing up from tmyers. I promise I'm not him! He writes an awful lot like me (poor guy)!
 

Users who are viewing this thread

Top Bottom