Table design and normalisation

[IanHem]

I'm hoping someone can give me some advice regarding table design and entering data into the tables.

I'm trying to write a vehicle management database.

vehicles are unique.

vehicles belong to an operation (eg State or Country)

each operation may or may not have an operation manager, but an operation manger may manage more than one operation. They may manage for multiple customers

each operation may or may not have a fleet engineer but again a fleet engineer may consult more than one operation. They may consult for multiple customers.

A customer has one or more operations.

Here is my layout.

Thanks
an

 

[plog]

Looks good--it matches your description. The only suggestion I have is to eliminate the TableSalutation. Just type that data in, you're not saving anything by having that lookup table.

 

[RainLover]

I would like to see some Naming Conventions employed.

ID is not very descriptive.

SalutationID would be better and SalutationPK would be better still.

Just my opinion.

 

[vbaInet]

Can two or more drivers operate the same vehicle?

 

[jdraw]

Just as a comment - tableSalutation, since it is a set of lookup values that can be used in different situations, you could show 2 copies of the table instead of 2 joins to different tables.

 

[MarkK]

Your "people" tables should be one since they are exactly the same. Fleet engineer and ops manager are distinctions that should be made in a field in a table, not by spawning a new table. Have an "EmployeeType" or "JobType" field.

Also, I don't think your verbal description of how Customer fits into the picture is clear. Your table design indicates that the only relationship between the people and the Customer is via an Operator. Is that correct? Maybe it is.

 

[IanHem]

Thank you for your replies.

I think I'm right by saying that the tables FleetEng and OpManager need to be separate because these two people may collude on one operation but not on another.

It is true that the only connection between vehicle and customer is the Operation, its a company hierarchy Customer-Operation-Vehicle.

The salutation table is a list i stole somewhere off the web and let the access wizard build the table I agree it looks untidy and will most likely go.

A vehicle can in effect have more than one driver (My wife thinks so anyway).
This is only a contact detail that will be changed by the user.




Thanks

 

[vbaInet]

A vehicle can in effect have more than one driver (My wife thinks so anyway).

Only commenting on the part I asked, so I'm sure others will chip in with their suggestions:

1. Vehicle should be a separate table with the Vehicle Registration Number as the Primary Key.
2. Driver should also be a separate table (regardless of how much information you're storing for a driver). It looks like you might already have this since you have DriverID, but I don't see it in your relationships.
3. The Vehicle/Driver table which currently is TableVehicle should use DriverID and VehicleID as the Primary Key (or Composite Keys). That way two or more drivers can use the same vehicle.

 

[gemma-the-husky]

just an observation - I would not have vehicle reg as the PK. There is a possibility that the reg no for a vehicle changes, so I would have an autonumber key, and the regno as a separate indexed field.

I would also ucase regnos (just for appearance), and remove all spaces from any regno, as otherwise you will end up with multiple versions of the same regno

eg
LL61PEX, LL61 PEX, LL 61PEX, LL 61 PEX

 

[vbaInet]

Good point Dave! Is it possible for it to change for commercial vehicles as well? Possible but I haven't seen a case yet, unless it's an imported vehicle or there's something dodgy going on.

 

[RainLover]

1. Vehicle should be a separate table with the Vehicle Registration Number as the Primary Key.
2. Driver should also be a separate table (regardless of how much information you're storing for a driver). It looks like you might already have this since you have DriverID, but I don't see it in your relationships.
3. The Vehicle/Driver table which currently is TableVehicle should use DriverID and VehicleID as the Primary Key (or Composite Keys). That way two or more drivers can use the same vehicle.

vba

I don't agree with your opinion on the Primary. I would only use AutoNumber. This gives only One field on the Master Table and just the one field in any slave or if you prefer to call it the child.

I have seen your design used which has blown out to four fields in each table.

 

[RainLover]

just an observation - I would not have vehicle reg as the PK. There is a possibility that the reg no for a vehicle changes, so I would have an autonumber key, and the regno as a separate indexed field.

I would also ucase regnos (just for appearance), and remove all spaces from any regno, as otherwise you will end up with multiple versions of the same regno

eg
LL61PEX, LL61 PEX, LL 61PEX, LL 61 PEX

Dave I tend to leave these things alone because sometimes thing change. Where I am the use of 8 Alpha Numeric and three spaces is acceptable. So is just one letter if you could afford it. The norm is ABC 132

 

[vbaInet]

vba

I don't agree with your opinion on the Primary. I would only use AutoNumber. This gives only One field on the Master Table and just the one field in any slave or if you prefer to call it the child.

I have seen your design used which has blown out to four fields in each table.

I'm sure that they had their reasons why they decided to split it into 4 fields. I don't know how reg numbers are formed in Oz. Over here area code is the first two letters and that's pretty much it. So not one situation fits all.

It's down to how the reg number plates are represented that will dictate which approach to use. There's absolutely nothing wrong in using Text as a primary key. A primary key is there to enforce data integrity, so if the field never changes then it's valid to use it as a PK.

 

[plog]

I think I'm right by saying that the tables FleetEng and OpManager need to be separate because these two people may collude on one operation but not on another.

I know I said it looked good, but upon closer inspection MarkK is correct, these two tables should be one. They have the same structure and when that occurs, you just need one table.

Even though they can fulfil 2 different roles, it doesn't matter because when linking to them, you can accomodate that with the same table. In a query you would bring that table in twice and link one to one field and the other to the other field.

 

[vbaInet]

Over here area code is the first two letters and that's pretty much it.

Older number plates are different of course

 

[RainLover]

then it's valid to use it as a PK.

You are correct in that you are free to use whatever unique reference that you wish as a Primary Key. It could be Text, Numeric or a combination of both. But when you have the opportunity to use one only clean field why not use it. I have yet to see someone come up with a reason why something other than AutoNumber is better.

If you wish to try please stick to facts.
Please do not use the good old, he said she said. It just does not cut.

I have had this debate with the best of them. The only person who has given a good argument has been Galaxiom.

 

[RainLover]

I'm sure that they had their reasons why they decided to split it into 4 fields. I don't know how reg numbers are formed in Oz. Over here area code is the first two letters and that's pretty much it. So not one situation fits all.

It's down to how the reg number plates are represented that will dictate which approach to use. There's absolutely nothing wrong in using Text as a primary key. A primary key is there to enforce data integrity, so if the field never changes then it's valid to use it as a PK.

Mate

I am talking in general terms. I am not just limited to plates.

But to use plates as an example. Your plate is say a standard "WA 1234"
So you use that for your primary key. What do you do if your plates get stolen. You are forced to adopt a new Primary Key. I could not think (Maybe someone else could) of a situation where the changing of the primary key's value an acceptable practice.

 

[vbaInet]

What do you do if your plates get stolen. You are forced to adopt a new Primary Key. I could not think (Maybe someone else could) of a situation where the changing of the primary key's value an acceptable practice.

That's a valid point and this is what I was expecting as a reason not to use the reg number as a PK. PK's should be fields that don't change and (where possible) should be used to enforce referential integrity. The main reason why I mentioned it can be used as a PK (in combination with the DriverID) is to enforce referential integrity. But you've pointed out a scenario that could cause it to change.

I'm not against using AutoNumber as a PK and that wasn't my point.

 

[IanHem]

Some things to think about there.

Thanks The registration numbers are restricted to ucase without spaces to prevent errors.
Because registration numbers can change I won't use this as primary key.
I could of course use the VIN number of the vehicle because this is unique but its not a required field.

I understand now the need to put fleet engineer and operation manager in the same field I guess i can just have one table with an additional field of job type.

 

[RainLover]

I love the way you guys spend hours pondering over what you will use as the Primary Key.

While I just stick an AutoNumber in and I am done.

When it all boils down what do you prefer. When you got that then use it. But try not to have a composite field and make sure that it won't change and that you will not find a duplicate.