Heavy equipment table hierarchy

[Stephenson]

Hello all,

I'm having difficulties wrapping my head around my table hierarchy for equipment in my database. I know that starting from the bottom it should be, Equipment Type, Equipment Equipment Class, and Equipment Model, but am not convinced I have my relationships right. i would also like to define the equipment rate at the Equipment Class level. Please have a look at my table arrangement bellow and give me any pointers you may have.



Thank you for having a look,

John

[Micron]

I think I'll be speaking for others as well when I say you'll have to provide more information. We don't have the business context, so we'll likely have different interpretations of what's what. If you could provide a narrative about the relationships and the business of the database we'd be better equipped to make suggestions. I'm looking at it and wondering why you appear to have two tables related to class. Include some examples of the hierarchy to help us grasp what you have. I'm looking at it thinking that maybe a type example would be 'construction' and a class might be earth-moving or boring or paving...thus don't get the second class table. I'm guessing no one will know what an 'EquipRtType' is either.
Just so you know, I have a bit of experience with equipment hierarchy and CMMS, having been a diesel mechanic and equipment reliability practitioner for a number of years, so I'm hoping I can put some of that old knowledge to good use here.

[Stephenson]

Hey Micron,

Thanks for the input and sorry about the lack of information. Let me try to make things more clear.

I'm developing this database to track equipment service, equipment use through employee time cards and help project managers to bid jobs. Time card records will develop invoices based on equipment used, job estimates will be based on equipment class. EquipRtType is short for equipment rate type. There are two rate types, hourly and daily. Daily is similar to any rental store and hourly is, well... by the hour. I have two tables for equipment class aside from having poor skills at table design is one is a list of equipment classes and the other is for recording the class and rate. Probably a poor design?

Example of what I'm looking for; Equipment example, Backhoe. Type: Loader, Class: 9,000 - 14,000 lbs, Manufacturer: John Deere, Model: 310HS. When recording a time card it would be selected as "John Deere 310HS". When bidding a job it would be selected as "Loader, 9,000 - 14,000 lbs".

I'm happy to have your experience in a similar field working with me.

[Micron]

The first sentence of your OP might betray your second. In other words, you may think you know what is needed in terms of tables, but the fact that the relationships are not presenting themselves may indicate you don't. Now that I have a better understanding, I'll look it over again. In the meantime you should consider if you have given enough thought to what you want to get out of the db before you think about what to put in it. Have you asked questions like 'would I want to see a report that would show me a cost/income comparison for a particular class? Or type? Or single piece? That question may not be applicable but is simply an example of the kind of precursory analysis that should happen. There are some data models here that you might want to look at. You can search the page for 'equipment' to make finding relevant ones easier. You can always test current design with queries. I advocate that if you cannot query what you need, there is something missing in or wrong with the table schema.

Please let us know if you want to put the thread on hold while you ponder the above.

[Stephenson]

Micron you make a very good point. I'm comfortable with the actual hierarchy of equipment, but translating it to database tables is definitely not "presenting itself". I've included a snap shot of my tables for equipment service as well as one for equipment use.
I intend to write (with help most likely) a query to give me the actual cost of ownership for my equipment. This will take into account the fields in my equipment table as well as any expenses for the equipment. I will brake this down to an hourly cost of ownership that I can compare against my equipment rate to view profit.
My equipment service module will keep track of all equipment service needs and intervals. I'll capture the current equipment use through multiple forms to help show when equipment service is due. Along with the service required there will also be records for OEM parts and alternate parts (aftermarket).
I would also like to generate reports listing owned equipment categorized by type, annual cost of equipment maintenance, equipment and their rates & rate type, repair orders, and many others that have slipped my mind.

Thanks again for the help. I'm very excited to put this database together.

[Micron]

job estimates will be based on equipment class...Class: 9,000 - 14,000 lbs

If you're going to estimate jobs based on equipment class, I may see a problem. I would not consider 9000 to be a class. Consider that you may rent a chain fall with 10,000 lb capacity. Are you going to price that out the same as a 10,000 lb loader or forklift. I think not. Would it skew aggregate cost or income data based on type when the chain fall goes out for $50 for the day but the loader goes out for $1000? You bet.

I'd recommend a Type / SubType sort of hierarchy such as Pump, Motor, Valve, LiftEquip, Excavation, etc. A sub-type table for one type (say, pump) could have hydraulic, water, air, etc. with a field to tie it to the parent type. A sub type might be piston, reciprocating, vane, diaphragm, etc. Since any additional sub types would be children of the level above, it would require a table for each child level with a field linking to its parent. All level 2 subs would go into this table, so some rows would relate to pumps, some to excavating equipment. This would play very well with combo boxes when you want to filter by type. It's not often I'd consider adding tables when additional records are needed, but this would be one of those occasions. The alternative is a sub table with lots of holes where the fourth level has no bearing on records at the third level when they're all in the same table.
So I'll ask if you looked at the recommended data models, and whether or not what I"m saying gives you reason to re-think things. Or is it full steam ahead, damn the torpedos?

[Stephenson]

Great input Micron,

I think I'm attempting to do as you suggest, but not carrying it out properly. Please correct me if I miss understand your suggestion. My table tblListEquipmentType would be for be the most general, just like your suggestion for Type. Table tblEquipCls would record the "SubType".
In my (poor) example the 9,000 lbs class would be a sub category of the equipment type loader. True, the class name is a bad one, but should not get confused for any other equipment outside of the loader type.
I've been scouring your link to see if I can find some examples that work for my situation. I believe I'm attempting to do what you've suggested, just not explaining it or implanting it the way I should.

Please let me know if my using equipment class as a sub to equipment type would work as you suggest for type and subtype.

Thanks,
John

[Micron]

General observations
- should keep notes to 255 characters or less, as opposed to long text (memo) type or keep memo type in its own table.
- rate doesn't belong in equipclass. If there are 2 or more rate types for a equipclass, all the other info has to be repeated, which sometimes is OK, but here, you already have that info in tblEquipCls.
- keeping costs like Insurance in in tblEquipment means you can only ever have the current cost - no history. It doesn't really belong there as it's not an attribute (characteristic) of the equipment. Year, price, license, VIN, yes. These are attributes belonging to the equipment. Example, if I am 6 ft tall, have brown hair, green eyes, etc. these are attributes of me. How much money I have in my wallet is not - it does not define me. You would not use that info to aid a search party looking for me (though someone on a dating website might think it's relevant ).

Moving on, say there are RtTypID's for hourly, daily, weekly, monthly; = 4 RtTypID's.
tblEquipClass should be tblRates. EquipClsID would be a FK in tblRates, joined to tblEquipCls.EquipClsID.
EquipClassID would be RateID. If a class had 2 rates, there would be 2 rows for it, with differing rateID's, the same EquipClsID and a rate for for each rate type.

tblListEquipMdl would be joined to tblEquipCls if you need it, but I don't see why. If your loader was a 950 Cat, why not just put 950 as the model, tblListEquipMfr.EquipMfrID (55) for Cat in tblEquipment along with tblEquipClass.EquipClsID as a FK?

A lot of words that you might not be able to fully digest. Let me know what you think or need next.
DO NOT make these changes in your current db - play with a copy. Ensure you understand the suggestions and give it some thought first. I might have suggested something that sounds good in my head, but might find it lacking if I had to make the relationships. Nor do I have the luxury of knowing your business needs as well as you do.

[Stephenson]

Thank you for the observations Micron. I will take some time to weigh them against my overall picture. I'll also take care of tblEquipment and reduce it to atributes only. I just hope it doesn't affect the equipment in the dating scene. Hahahaha


Could you elaborate a bit more on the following quote? (55)?

tblListEquipMdl would be joined to tblEquipCls if you need it, but I don't see why. If your loader was a 950 Cat, why not just put 950 as the model, tblListEquipMfr.EquipMfrID (55) for Cat in tblEquipment along with tblEquipClass.EquipClsID as a FK?

I agree when entering the model it should only be "950" as in your example. I guess I'm not clear how to achieve this or why it is not achieved by my current relationships.

Thanks,

John

[Stephenson]

Hey Micron,

I've been playing around with your suggestions. Please correct me if I'm wrong, you're suggesting not to link manufacturer to model. Instead link them both to tblEquipment as foreign keys. Before this post I had, had it this way, but when attempting to relate the tables in the way I see the equipment hierarchy; Equipment, Model, Manufacturer, Class and Type. If I relate them both to tblEquipment this would not conform to the hierarchy. Am I looking at this wrong?

Bellow is my latest attempt based off of your suggestions as well as the addition to table tblEquipStatus. I will use this to show if the equipment is owned, rented, sold...


I think I'm getting closer to a working solution.

[Micron]

First, let's clarify that we're talking about the first relationship diagram (pretty sure we are). My comment about not needing something was the table itself, not the link. When I look at it from the perspective I wrote about, I just don't see a need for it. The design is starting to get messy. Now you have EquipmentID in cost linked to EquipmentCostID in Equipment, yet you have an EquipmentID field in Equipment.

It's practically impossible to provide teaching in this setting, and it's not what the forum was designed for. When you get stuck, sure we're glad to help those who try to help themselves. I really think you should review these topics (links courtesy of ssanfu in this forum, although I have reversed their order from the post I got them from) then step back and re-examine your db based on what you learn. If you rush these tutorials, you will not be doing yourself any favor. If you need them, I have some decent links on naming convention, reserved words and autonumbers, to name a few.

Database Design Basics
How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

[ssanfu]

PMFJI,
I took the relationships in Post #10 and re-arranged the tables. I try to have the "one" table on the left and the "Many" table on the right. It seems easier (for me) to understand the relations and the data flow.
Note that I used part of my naming convention: "_PK" are primary key fields and "_FK" are foreign key fields.
The first field in a table is the PK field, followed by any FK fields and the rest of the fields in whatever order is deemed "best" : alphabetically, by datatype, by .......




Haven't tried to add the two relationship views from Post #5....yet

[Stephenson]

Micron,

Thank you for the links. I look froward to learning from them.

Steve,

I appreciate your input a am excited to review your work and see how I can apply your work and learn from it.

All of the help is really appreciated.

Thanks,

John

[Micron]

ssanfu, you have way too much time on your hands!
Do you actually use Access to create a bunch of tables, then create the relationships? That would seem to take a long time (and I can type). Or are you using some sort of db relationship building software to speed things up?

Anyway, I still think the model table is over-kill. That's like breaking down vehicles into cars and buses as equipment types, then having every model listed for every type and every class in the db. If this was about vehicles and Chev, Ford, Toyota, Volkswagen, Nissan etc. etc. have 150 models between them, that's what's going into this table. But it's not the quantity of records that makes this questionable in my mind. It takes more fields to tie this into the rest of the tables than there are fields that are not needed for linking. As you scroll through this table you could see EC750E, 950 and maybe PB255LN. No one will know the first is a Volvo excavator, the second a Cat and the third is a leaf blower - unless you use the notes. That would be a first for me - to use a notes field to explain what's in the data column. While I'm on the soap box, consider fuel type. Since that will be common as common to other types (or classes - I'm getting confuzzed now) - as the model data, why not put that in it's own table too? (being facetious).

I would just have a model field in the equipment table. As you scroll across a record you see equipment number, license, VIN, fuel type, purchase date... and model data.

[Stephenson]

I had not reviewed my work carefully enough, after copying tblEquipment to make tblEquipCost I did not replace EquipmentID as I should have. Thank you both for pointing this out. I like the idea of laying out the tables as you've suggested Steve. I have not read this method before, but it does make sense.

Micron if you could please provide the links for naming conventions and reserved words, I sure would appreciate it.

Thanks again,

John