Help with tables for a car database (1 Viewer)

[sim18]

Hi all, thanks in advance.

I am creating a car and customer database. I will have a database full of different types of cars. I will also have customers, these can be linked to a certain car for example a customer is selling a car, or a customer has just bought a car.

I am wanting to be able to search for a car using cascading dropdown.

At the moment my database is like this:

CarMakeTable
----------------
carReg - PK
carMakeID
carMake

CarModelsTable
-----------------
modelID - PK
carModelsID - FK
carModel
carMakeID - FK

CustomerTable
----------------
custNameID - PK
custName
custNo
carReg - FK

At the moment in my cascading dropdown when I search for a car make for example 'Ford', the next dropdown is visible which is car model. I can then select a ford model, but I get multiple choices of the same for example the person can choose a KA or KA. I just want there to be one of each model.

Thanks to anybody who can help make my tables a lot better.

 

[Beetle]

Why do you have the field carModelsID as a Foreign Key in your CarModels table? Foreign Key to what?

Also, what is carReg? Is this the VIN? If so it really doesn't belong in any of those tables.

 

[Beetle]

You might want to look here for some example data models. Look under the Vehicles category.

 

[wmphoto]

It looks like you're combining the customer and the car they're selling into one table. I would probably have these as separate tables. That way you can link a single customer to multiple tables.

If you're wanting to track both the cars customers buy and the cars they sell, I would perhaps include a new table for changes of ownership, so that you might have a car which you sell to a customer, and then a few years later if the customer sells it back to you, you aren't having two separate records for the same car.

Records appearing twice in controls like drop-downs, forms etc. are usually caused by the record-set for the control or the query behind the form being based off the wrong table. Is there something on the many side of a many-to-one relationship with car models? And if so, could this have snuck into the data for your drop-down?

 

[sim18]

Hi all, thanks so much for the replies. I have had a look at the example data models they were a huge help!

I think I may have done the 'car' side of the database but may need some more help attaching the customers to it.

I have attached my database with the relationships as a JPG.

What I am trying to do is fill the database with cars that are for sale. (They don't have owners) There may be many types of the same car make and model and categories, but the registration will be different.

Then I want a customer to be able to sign up to my website. They can view each car by doing a search, if the customer buys one. I will remove this from the database. If the customer wants to put a car for sale I can add this to the database. The customer should also be able to edit the car specifications.

Thanks again