Beginner: Sanity Check

nsa10124

New member
Local time
Today, 18:18
Joined
Nov 10, 2017
Messages
7
Hello All,

I want to get a sanity check on how I am structuring a database I am building.

For example: I am building a database to manage inspections on various vehicles. Right now we are inspecting Cars, Trucks, and Buses, but in the future we might want to also inspect Motorcycles.
Each Vehicle type gets a different set of inspections, but in the future I might want to change these inspections, or add additional inspections.

I have attached a picture of the table in Excel.
And the Access Database that I think will be able to replicate this functionality.

Thoughts, Comments, Questions, Concerns??

Thanks!
Nathan
 

Attachments

  • Structure1.png
    Structure1.png
    6.7 KB · Views: 90
  • VehicleInspection.accdb
    VehicleInspection.accdb
    772 KB · Views: 89
Note: This is just an example, in the end application there would be tens-hundreds of vehicles and hundreds-thousands of different inspections.

Also, a more general question. What do you think of the overall structure of the example database?
 
Representing data in a database like Access is completely different than how you might do so in a spreadsheet. Before you proceed I strongly suggest you google and read up on 'database normalization' and 'entity-relationship model' (or entity relationship diagram), and begin to come to a greater understanding of how a database works.

A database system is a model of a particular reality. If you construct your model incorrectly, then it will not be suitable for your purpose, and you will waste hours and hours of your time, and never fully realize the benefit of a properly constructed database system.

hth
Mark
 
Thanks for the reply MarKK.
I have read-up on 'normalization' and I believe I have an okay grasp on that. As far as 'entity-relationship modeling' that is a new term for me, but I think I have a basic grasp of that as well.

Since I have never designed, or even seen the back-end of a database with the functionality that I have in mind, it is very difficult to evaluate how well I am doing. So I was really hoping for some verification if I am on the right path or not.
I.e. if you open the sample database do you say "What is this nonsense?" or do you say "Okay, it makes sense what you are doing."

Thank you,
Nathan
 
I'm out of time, gotta go away for the weekend. Please, if you don't get a good response from someone else on here, contact me early next week and point me at this again, OK? There are some refinements I thing you can make.
Mark
 
nsa10124,

When we talk about entity-relationship models, we are talking about building a logical / mathematical model of your business as a dynamic flow of data from beginning to end with many potential steps along the way.

Each type of "thing" you work on will be an ENTITY in the model. A table represents a list of entities of the same type. The idea is that if you treat two "things" the same way, even if they are different things, they are the same entity in the model. So if you treat trucks and SUVs exactly the same, then despite minor differences in their description, they are the same entity. And if you treat them radically differently, they are different entities. Although what I really think is that ALL vehicles have enough in common to be treated as the same entity but they might have a list of potential maintenance actions that depend on vehicle type as described in a secondary child table or reference table.

Here is an off-the-cuff example:

You have people who perform the maintenance. You might call them "mechanics" and have a table of mechanics. They are different people but the same entity.

You have vehicles to be maintained. You might call them "vehicles" and have a table of vehicles. They are different vehicles but the same entity - and different from the entity called "mechanics" because you do different things with mechanics vs. vehicles. Differences in treatment are the key to knowing they are different entities.

You have equipment that might be assigned to the use of individual mechanics. So you would have an equipment table representing a third type of entity. And, because you assign the equipment to mechanics, there is a natural relationship between members of the mechanics table and elements of the equipment table.

You might have a maintenance job where you assign a vehicle to a mechanic. This job table would be a trackable, if somewhat less physical, entity. You track it because you want to keep records of maintenance. So this job table would have relationships (plural) - to the vehicle being maintained and the mechanic who maintained it. (This is also an example of what Access calls a Junction table, which you can look up on this forum.) The job table would also have a status such as "Scheduled" or "Assigned" or "In Work" or "In Inspection" or "Maintenance Complete" ... as need for each job.

When I mentioned "data flow" issues, the data flow for your problem starts with your vehicle inventory, equipment inventory, and mechanic employees. The "flow" is based on the "job" order that brings mechanic (and his/her equipment) together with a vehicle.

Read over this and see if it suddenly snaps into focus as a logical description - at a very high level - of what your business does. It will get a lot more complicated, but remember the example of Julius Caesar - divide and conquer. Break it all down into simple steps.

Understand that I just planted seeds, but YOU have to build the garden. And, like any other gardener, you'll occasionally encounter a pile of poop to be managed. (How do you think gardens stay green?)
 
Doc Man,

Thank you for your response. I really appreciate it!
I looked over quite a few sites explaining the workings of entity-relationship models, and though they had many pictures to help explain, I still think your real world example is more intuitive.
"Junction table" is another new term for me, and just the clarification that they are commonly used is a big help for me. Now I feel a lot safer on starting to plant my garden.

Thank you,
Nathan
 
Nathan,
I have put some links together in this post that may be helpful.
Good luck.
 
I still think your real world example is more intuitive.

Glad to help. Just a word of warning. At some point, you will find an abstract entity or two. For example, whereas you can touch a vehicle or a tool or a mechanic, it is harder to actually touch a job. You might have a job order (paperwork), but the point is that an entity can be an action or set of actions - a maintenance job being the perfect example thereof. So if something seems NOT so intuitive, just be aware that specific and regularly performed actions CAN be entities if these actions are part of the daily flow of work for your job. Don't let something that appears counter-intuitive totally stop you.

Good look with that garden.
 

Users who are viewing this thread

Back
Top Bottom