Design Flaw ?

[JimJones]

Hi,

I've been working on this database, where basically, I have 3 tables and 3 corresponding forms.

The first table: CustomerData has one primary key.

The Other, CustomerCarData has 1 pk, but is also the many side of 'Customer Data".

The 3rd table is 'Service'. with 1 pk, and
2 'many sides' (Customer, and CustomerCar).

OK basically it works. This is how it works:

A customer comes in to get their car fixed (oil change, whatever).
The customer can have many cars.

OK,

Each of their many cars, can have an infinite amount of service done. (one car gets oil change this month, next month the other car needs brakes . . . . .and so on).

What I've done is made "CustomerDat" the main form, and "Customer Car" and "Service Ticket" the subforms.
(actually "Service" is a subform of CustomerCarData, and then both are implanted to 'CustomerData'.

Would it make sense to tear all this down and make
'Service' the main form, and then add the other two as subforms?

Help truly appreciated,
Jim

 

[Parker]

I use a similar system for servicing our company fleet.

First thing I would say is you don't appear to have enough tables.

In which table do you keep the details of customers vehicles? and how many tables do you use for that?

The table structure for our vehicles is

TblRegistration (Reg. Number only)
TblMake (Make & Model)
TblTransmission (Transmission Type)
TblFuel (fuel Type)
TblColour

This is already more tables than you have in your entire db and you could have even more just for the vehicles. You should design your db so that each table holds unique information. That way you only input information once.

i.e you may have lots of customers that have Ford Mondeo's, more than one customer that has a vehicle with Automatic transmission, etc. But you should have only one customer that has a vehicle with the Reg. ABC123W.

 

[JimJones]

Hi,

Thanks for responding so quickly.

I know about tables.

The data in all of my tables is entered only once. This is done with the help of primary keys, and relating one table to another by a particular field.

I mentioned the "basic" layout of my database which has been in service for a while.

But I'm looking for some help with the question in my post.

Thanks,
Jim

 

[Parker]

Would it make sense to tear all this down and make 'Service' the main form, and then add the other two as subforms?

Sorry miss understood

I actually use our service form as the main form for this part of our opperation (but we don't normally service cars for other people, they all belong to us). I then show the vehicle reg on this form just as a visual verification that records are being added to the right vehicle.

How well this would work out if you are servicing vehicles for customers I don't know but I can't see any reason why it wouldn't work well. We just found it easier this way and also we are displaying less information on screen

The information on the service form is the one that needs constantly updating and the other information remains pretty well static but other than that I had no other reason for doing it that way