Design question

[Steve Martino]

Hi forum, had a question about a database design (not sure if this is the right spot).

I would like to create a separate database for my business to manage vehicles.  I have 3 tables:  Vehicles, Service, Vendors, connected in the usual way. 

Vehicles Table: Has all the basic info about the vehicles.

Service Table:  Will be connected to vehicles to record service performed.

Vendors Table:  Info on the vendors/suppliers who provide the service.

The 2 parts that has me stumped. 

Part I

I want to be able, for each vehicle, to put in a preventive maintenance schedule.  Some examples of what I need to track are:

1.  Inspections due-regular vehicles are once a year, commercial vehicles, twice a year.

2.  Preventive maintenance schedule-oil change due by mileage/engine hours, transmission service by mileage, etc

3.  Tank certification, annually, or every 3 years depending on type of truck.

4.  Registration renewals

I would imagine these types of things should be on a separate table (Maintenance Schedule), connected to Vehicle Table. Maybe even add one of the calendar plug-ins, so I can see what/when a maintenance issue is due.

Part 2

I need a daily log (required by law on the commercial vehicles) that shows that visual inspections were made, tire pressure checked, etc.  I would also assume this should be a separate table connected to vehicles.  The main bit of information would be current mileage, which would be use in the Maintenance Table to notify me when a maintenance issue is due.

Am I going about this the right way?

Any thoughts/suggestions/samples are always appreciated.  Simple relationship screenshot attached.

Thanks

Steve

[comment]

Am I going about this the right way?

​I believe the answer is basically yes, except:

1. I am not sure I see the big difference between the daily inspection and any other type of service required;
2. Services should also be related to a TO of Maintenance Schedule (matching on Vehicle ID and Service Type).

[Agnes Riley]

Your relationship model should be based on the layouts you're going to view the records from.

So if you want to view the vehicles, have a Vehicle TO and connect all relevant tables to it.

And if you want a layout that shows the upcoming maintenance events for the cars, then start with that, then connect the cars, etc. to it.

Don't connect everything to everything.

 

You should schedule maintenance records for everything that needs it, and how often. So you could have one that's every day and yes, that goes into a different table. But the maintenance records should be in a different table from the schedule. All the schedule needs to have is the ID of the service and the date. And then have a join record attached to the vehicle, so you can create a schedule that repeats itself every day. I'd use a server script for that.

And this is when I'll say, everyone thinks it's easy to build a database since FM comes with templates, but they don't teach database theory or how to design.

So, some say, write down the nouns you want to track then you'll know how many tables you'll need. Then add the join tables if you need multiple connections. So, e.g. if you have multiple vehicles and multiple types of maintenance records, you'll need a join table to relate the right maintenance record to the right vehicle.

Hope this helps.

Agi

 

[comment]

everyone thinks it's easy to build a database since FM comes with templates, but they don't teach database theory or how to design.

​That's true. Alas, that's also about the only thing in your post that I agree with.

[Agnes Riley]

Care to elaborate? I'm always happy to learn.

Edited May 21, 2015 by Agnes Riley

[comment]

Care to elaborate?

​No, that's not how it works. I posted first - so if you are going to disagree with me, you need to state your reasons.

 

I will refer to this, though, because it doesn't contradict anything I've said:

Your relationship model should be based on the layouts you're going to view the records from.

​I am not sure what you mean by "relationship model". I thought this question was about the data model (that thing which is normally expressed by an ERD) - and this should definitely not be based on any user interface considerations.

[Agnes Riley]

I don't think I disagreed with anything you said. It was not my intention. I was just trying to explain some things in laymen's terms. If you think I caused more harm than not, I'm happy to delete my message.

Most people don't know what the term ERD (Entity Relationship Model) refers to. 

And depending on what modeling method he might chose my answer would be different. I went by what he started doing and merely suggested that he doesn't build many levels deep. And I could've elaborated more to inform him that FIleMaker would have to dig down to each table through the connection to get the data so it would take significantly longer, the more TOs he connects in one line horizontally.

Hence I recommended breaking it up into digestible chunks.

Still not sure how I offended you, but I apologize.

[Kevin Mullins]

On the database theory comment, I like the video series that Cris Ippolite has on Lynda.com, Relational Database Design with Filemaker.  No tutorial will ever replace experience, but I thought it was a good place to start.

http://www.lynda.com/FileMaker-Pro-10-tutorials/Relational-Database-Design-with-FileMaker-Pro/83839-2.html

He just updated the series to include fmp12 sample files and updated everything to keep it version independent.

[comment]

No offense meant or taken. However, I thought your contribution was posted in response to mine, and in disagreement with it. Apparently, it was posted in complete disregard of mine. Had I wanted to take offense, that would make a much better reason than any disagreement... 

[Rick Whitelaw]

"Don't connect everything to everything."

Agnes,

Not sure what you meant by that, but in terms of tables, all the tables needed should be related in this case. So in that sense everything is indeed connected to everything.

[comment]

To get this back to track: I believe this is what the ERD should look like:

Note:

The MaintenanceSchedule table should not contain a record for each instance of service that needs to be performed (perhaps the name is misleading and could be replaced by a better-fitting one). A record in this table should look something like this:

VehicleTypeID: 123 (represents "Acme Truck")
MaintenanceTypeID: 45 (represents "Tank Certification")
Interval: 3
Unit: Year

 

 

Edited May 22, 2015 by comment

[Jeff M]

I may be oversimplifying it, but if I were building it I think I'd let the services table handle my service, scheduled maintenance and daily logs. After all, those thing are just different types of service, right? Scheduled maintenance are just services that haven't occurred yet. Daily logs are just service that occurs daily. If you want to separate them, perhaps add a Service Type field that you can use for searches and portal relationships.

You are doing the right thing by thinking it through up front. FileMaker is more forgiving that most databases, but it's still best to build your data model first. Once you have that, the UI can be driven by what your user needs to see and how they will interact with it.

[Steve Martino]

Thanks for all the responses.  Everyone seems to have a little different take, so I welcome all the ideas.

My initial thought about more tables come from much reading on the forums about how 'narrow' tables are better for a number of reasons.

The 'maintenance' portion of this, to me, consists of 3 different kinds of things.

1.  Log-Some of these items are required by law to be checked before/after, and some are good practice.  Most of these don't 'feel' like maintenance, but yes some could lead to maintenance.

These are things like, checking the tire pressure, checking all the fluids.  I would consider this to be a one page layout consisting mostly of checkboxes.  These items just need to 'logged' that they were done, in case of audit and probably never need to be looked at/reported.

2.  Preventive maintenance falls under the category regarding annual/semi-annual inspections, which are calendar based, and fluid changes and other mechanical items-which are mileage based.  So I thought putting mileage on the daily log would/could  trigger a reminder on a calendar (dashboard or plug-in) of what's due.

But service also falls under scheduled maintenance, based on above, and unscheduled maintenance, based on emergency.

So let me put some more thought about this/the workflow, and play with it over the weekend and see what I come up with.

Thanks again

Steve