Data Structure advice

[Lol999]

I've been asked to amalgamate 4 Excel spreadsheets into a single database, and from that run automated queries to prompt the booking of vehicle servicing etc.
The spreadsheets had the following fields:

Driving Licence Chart
Name
Copy of Licence Held
Expiry Date
Points Held
Tow or Trailer




Vehicle Schedule
Vehicle Reg
Vehicle Make/Model
Alarmed
Tracker
Vehicle Locks
Road Tax Due
Dispensations
Date of Last MOT
MOT Due
Last Service Date
Service Due 1 yr or 15K
Roadside Assistance
Comments


The above spreadsheet had the following "sub sheet" for want of a better word (NOT an Excel user) which contained the following as a maintenance log:



Date
Supplier
Goods
Comments
Cost


and finally:





Fuel card List
Card Number
Check Digit
Card ID
Registration
Driver
Pin


Obviously the data is all related to some degree or another, so I have worked on a data structure which I think works and have attached below.
Could someone check it for me please?


Many thanks, Lol

 

[plog]

In your relationships there should only be one way to trace a path between tables. You've created a loop, that is incorrect. Drivers, Vehicle and Fuel_Card should not all link to each other. I don't understand your data, so I don't know which link should go, but I do know that is incorrect.

Can you explain in plain english (no database jargon) how the real word works for those 3 items?

Also, I see these smaller issues:

1. Name and Date are reserved words and shouldn't be used as field names. Change these both by prefixing them with what they are the name and date of.

2. You should use an autonumber primary key in tbl_Drivers. I'm betting [Name] is text. What happens when you get another John Smith as a driver? What happens when Karen Jones marries John Smith and takes his last name? etc. etc. Use numbers, not text for primary ids.

 

[Lol999]

Yeah the second link from Tbl_Vehicla to Tbl_Fuel_Card was a bit of madness on my part

I hear what you are saying about reserved names, that's all reversible.

I'll do a bit of re-labelling and repost with a description.

Cheers, Lol

 

[Lol999]

Okay, I've re-named some fields and redone the relationships.

The db is just to monitor who has which vehicle, which fuel card and to track vehicle servicing and MOT testing.

I just want to run simple queries to ascertain if any MOT, road tax or servicing is due. At some point management may want to see the driver history of a certain vehicle, or total costs so far for a vehicle or all vehicles. Nice simple stuff.

Cheers, Lol

 

[pbaldy]

Is there only one kind of service? I track oil changes, transmission service, etc, which occur on different intervals. When one is due is based on what the interval is for that type and when the last one was done.

 

[jdraw]

Is this requirement related to your other post??

Obviously the data is all related to some degree or another, so I have worked on a data structure which I think works and have attached below.

Your data is related by the facts of your business processes.

Is your definition of fuel card the same as wikipedia:

A fuel card or fleet card is used as a payment card most commonly for gasoline, diesel, and other fuels at gas stations. Fleet cards can also be used to pay for vehicle maintenance and expenses at the discretion of the fleet owner or manager.

 

[plog]

Really think about relationships. Here's what your relationships tell me:

A person can have multiple fuel cards. Why? Wouldn't a person just need 1?

A person can have multiple cars, but an individual car is only driven by 1 person? Seems odd, I mean a person can only drive one car at a time, wouldn't a free car be available to another person?

Also, you are not keeping a history of MOT (I do not work in your industry this means nothing to me), road tax nor servicing. If you think this may be wanted, you need to create the tables to accomodate these things.

 

[Lol999]

Is this requirement related to your other post??



Your data is related by the facts of your business processes.

Is your definition of fuel card the same as wikipedia:

A fuel card or fleet card is used as a payment card most commonly for gasoline, diesel, and other fuels at gas stations. Fleet cards can also be used to pay for vehicle maintenance and expenses at the discretion of the fleet owner or manager.

No, this is another project altogether. Yes, a fuel card is a payment card and is used in this instance to pay for fuel only.

 

[Lol999]

Really think about relationships. Here's what your relationships tell me:

A person can have multiple fuel cards. Why? Wouldn't a person just need 1?

A person can have multiple cars, but an individual car is only driven by 1 person? Seems odd, I mean a person can only drive one car at a time, wouldn't a free car be available to another person?

Also, you are not keeping a history of MOT (I do not work in your industry this means nothing to me), road tax nor servicing. If you think this may be wanted, you need to create the tables to accomodate these things.

Yeah i need to re-daw the relationship between fuel card table and drivers table because a fuelcard will have many users during its life.
People cn have many vehicles because as their role in the company changes, or due to logistical requirements, they may exchange vehicles with someone else for a while or permanently.
I also had the epiphany in the shower at 6.30am about keeping a history of MOT and servicing in a separate table as currently it is a field in the main vehicle table.
However, since this will be a physically small database, I can't see there ever being more than 20 vehicles, then storage is not really going to be an issue nor is data processing times.

This is great thanks, you're getting me to think and re-rationalise both the data and my approach, exactly what I wanted!

many thanks, Lol

 

[Lol999]

Problem solved, data structure re-evaluated.

Thanks for the input.

Regards, Lol

 

[jdraw]

lol999,
It could be helpful to others if you were to post your revised structure,

Glad you have it resolved,

 

[Lol999]

Okay, after a bit of a brain ache the penny dropped once more and I came upon the attached structure.
It works for me, allows the logging of vehicle and fuel card assignment as well as handling data on drivers and vehicles.

Regards, Lol

 

[BeeJayEff]

Why do you have Driver_ID in Tbl_Fuel_Card ?
I suspect the Primary key for Tbl_Fuel_Card should be Card_Id (else what is that field for ?) and that should be linked to tbl_Fuel_Card_Issue.
You might want a Date_Returned in Tbl_Vehicle_Issue.
What is the difference between MOT_Date and MOT_Due ? (Service likewise).

 

[plog]

Tbl_Fuel_Card
~It shouldn't have Driver_ID in it.
~What is Card_ID?

Tbl_Fuel_Card_Issue
~Can a Fueld card ever be turned in? Given to another? If so, you need a Date_Unallocated.

Tbl_Maintain
~Date is a reserved word, don't use it as a field name. Instead prefix it with what it is the date of.


Also, you aren't keeping a record of what vehicles are being assigned to what drivers. The only connection between Vehicles and Drivers is the Issue table, if no issue, you don't know who had the vehicle. Don't know if you need that, but seems like you would.

 

[Lol999]

Well spotted on the Driver_ID
I hear you on the Tbl_Fuel_Card Primary key, however, the company is using that data for reasons I cannot ascertain at the moment so for now it stays
Don't need a returned date at this time for the vehicle since they are generally long term assigns. MOT_date is the date the current MOT was performed, MOT_Due as it suggests, same for Services.
Cheers, Lol

 

[Lol999]

@ plog: the table Tbl_Vehicle_Issue keeps a record of the "keeper" of each vehicle as it occurs and hence a history.

Cheers, Lol

 

[BeeJayEff]

I hear you on the Tbl_Fuel_Card Primary key, however, the company is using that data for reasons I cannot ascertain at the moment so for now it stays

That wouldn't be a good enough reason for me. What you as db designer choose to use for primary keys is up to you. I visualise Card_Number as being a long field similar to those on a credit card. I would certainly not want to have that as a pkey.
I have developed the habit of using {Entity}_Id - e.g. Driver_Id, Transaction_Id, as the primary key for each table and then using for example fkDriver_Id in any table which links using that field. I find it helpful in immediately identifying primary and foreign keys. Just a suggestion.
Using Vehicle_Reg as a "natural" pkey is fine until you have someone who insists on putting their private plate on the vehicle.
As per plog's comment, you might well want a Date_Returned field in tbl_Vehicle_Issue, if you want to find out who is liable for that speeding ticket when the vehicle has been returned and maybe not yet re-allocated.

 

[Lol999]

@BeeJayEff - I request information but it trickles slowly and I have to work within the constraints of how they operate. It is what it is,I have no reputation to maintain, no commercial business to satisfy, this is a little pro bono work.
I see your point on the van return date though.

Cheers, Lol