Database design

I am a begginer and im trying to do a car dealership website. the website would show the cars the dealer has and allow the dealer to go online to add and delete caras as they get sold and the client to look upall the cars ( with pictures fromthe dealer )
i just need help with the logic for designing the databse.
im using ASP // Access todo it.
the problem is im coming up with too many tables andi dont knw if i should relate them all to each other . i want to have main categories defined by truck brand. but then i have tables for Air condigioining ( yes , no ) transmission ( auto , 4 5 6 speed ) brand name ( toyo, mitsu, and nine more ) truck models. chassis lengh, chassis height, engine size, etc. i dont know if i should combine more of this tables together or what s the best way to go . If any of you master gurus could help me i'd be thankful .

To make it easier you definitely need to remove a few tables. A table that simply has a yes/no value can be removed (air-conditioning), all you need is a field to represent the yes/no option. Transmission also should be a field in the cars table and not a separate table.

In the simplest form you need:

Manufacturer
Model


And that's it... especially with a web-based system, as the forms can handle the task of look-up tables.

Thank you for your help binky . im a beginner so im really overwhelmed with all this design stuff.
I am attaching a copy of my database so you or any one who wants to give an opinion can see it.

the requriements for the website are these:

Admin needs to be able to:

Add cars to database
delete cars from database
Update cars in database

be able to add new brands to the brands table. or new models to the models table.

In the add or delete cars there will be dynamic drop down menus so that the admin. can easily and with no mistakes update cars to the database.

The customers need to be able to:

-search cars through one main category which will be Brand names. so that if he hits on one link this will automatically load all -the cars of that brand into the page.
-after that page he will click on a link for the car he likes and see more details for that car
I hope I am not too confusing.
Thanx to everyone for all your help
My database file is attached for all to see and give me their opinion

A quick tip about database design that helps a lot, especially for those just getting started, is to start out simple. After briefly looking at your setup, I would begin with just two tables: Manufacturer and Accessories. I haven't used access much but i'm pretty sure it will create a primary key column for you on the creation of any new table unless you tell it not to. Start out with you manufacturer's table holding the car's make and model (these will be your columns), then relate your manufacturer's table to the accessories table (which will hold info on the car) Each car you have at the dealership should have its own unique key to differentiate itself from any other car. This way you can have a row in the accessories table for each specific car. From there, add columns to the accessories table for whatever you need i.e. AC, transmission, power windows, etc. etc. I hope this helps and makes any kind of sense (it's pretty late!).

thank you zippy goose. the problem i see by combining the the manufacturer's and model's table is that one manufacturer can have several models and so when i have to query this from my webpage then i would have to use a bunch of different queries because the manufacturers and models are in the sam table . i dont know if i explain myself right.

Table manufacturers/models

TableID manufacturers Models
1 toyota camry
2 mitsubishy eclipse
3 honda accord
4 toyota celica
5 honda civic
6 honda prelude
7 toyota supra.



you see what i mean ? but if i use to different tables and join them like this .

manufacturers---------= models



then i could have



manufacturereID manufacturer
1 honda
2 toyota

modelsID model manufacturerID (foreign key from manuf)
1 civic 1
2 camry 2
3 prelude 1
4 celica 2
5 supra 2
6 accord 1


yoyu see.. ? can u give me ur opinion ?

You might want to look for a package instead.

NOTES ON VIN:
In North America, a system is used that is far more stringent than the ISO Standards but is "backward compatible." Here, the VIN is divided into four sections:

The first three characters shall uniquely identify the manufacturer, make and type of vehicle (with the same exception of manufacturers that produce less than 500 vehicles). Effectively, this is the WMI. There are indeed examples of manufacturers who have more than one WMI that use the third character as a code for a vehicle category (for instance bus or truck). Just as often however this is not the case;

The second section consists of five characters (VIN positions 4-8) and identifies the attributes of the vehicle. For each type of vehicle (passenger cars, MPV's, trucks, buses, trailers, motorcycles, incomplete vehicles other than trailers), different information is required. For cars, MPV's and light trucks it is required that the first two characters of this section are alphabetic, the third and fourth shall be numeric and the fifth alphanumeric. This section is the VDS in ISO 3779 but there it comprises another position of the VIN;

The third section consists of one character which is the check digit, calculated over the other 16 characters of the VIN. This character can be numeric or the letter X;
The fourth section consists of eight characters on positions 10-17 of the VIN. The last five shall be numeric for cars, MPV's and light trucks and the last four shall be numeric for all other vehicles. The first character represents the vehicle model year, the second character represents the plant of manufacture. The third through eighth characters are a sequential production number (for manufacturers producing more than 500 vehicles per year). For other manufacturers, the sixth, seventh and eight positions represent the sequential production number.

This section confirms to the VIS in ISO 3779.

A portion of the VIN is the WMI (World Manufacturer Identifier) Code. SAE assigns this code to U.S. vehicle manufacturers. If you are a U.S. manufacturer, please contact:

Cathy Douds
WMI Coordinator
SAE International
400 Commonwealth Drive
Warrendale, PA 15096-0001
724.772.8511
724.776.4026 - fax
douds@sae.org

Related Standards:

There are several standards available on VINs and WMIs:

SAE - J187 - Truck Vehicle Identification Numbers
SAE - J218 - Passenger Car Identification Terminology
SAE - J272 - Vehicle Identification Number Systems
SAE - J273 - Passenger Car Vehicle Identification Number System
SAE - J853 - Vehicle Identification Numbers
SAE - J1108 - Truck and Truck Tractor Vehicle Identification Number Systems
SAE - J1044 - World Manufacturer Identifier
SAE - J1229 - Truck Identification Terminology
SAE - J1877 - Recommended Practice for Bar-Coded Vehicle Identification Number Label


SAE J129 - Engine and Transmission Identification Numbers
ISO 3779 - Road vehicles - Vehicle identification number (VIN) Content and structure
ISO 3780 - Road vehicles - World manufacturer identifier (WMI) code

You say that you're a beginner, but yes, you're right... If you had Honda Prelude and Honda Civic, for example, in a single manufacturer/model table you are getting data redundancy. Honda appears twice, and if you store contact details for your Honda supplier then you will be storing all that data twice. Two things happen here, one is that it makes for getting errors. People enter Honda one time, then honda the next... the lowercase 'h'may make all the difference. The other problem is that the database gets bloated and searches take longer.

The next stages that you detailed are better placed in the ASP forum, or have a look at the following:

http://www.w3schools.com

It should help you a lot with the project.

Nice to see you here, jcelko. Thanks for the detailed explanation!

Yeah, the model you made looks good, now you can make an accessories table and use your keys to relate the two. I'm assuming that the ModelID column is the unique key for each car? After reading jcelko's post, however, I would take his advice and look into using the VIN numbers. Making your own model is great, but car manufacturer's usually don't cater to us DB folks that well . When building the DB structure for a test equipment reseller, I was going crazy sometimes because there are sooo many different manufacturer's, product ID's, product names, and accessory parts, none of which relate at all to each other because they are all from different companies! I feel your pain, but yeah, look into the VIN stuff that seems like a good, scalable idea.

did you guys check the database attachment i posted prviously? what do you think ?

hey guys . i thoought I'd put all the fields i Came up with so you guys cn tell me what would be the wisest way to relate them and group them into tables. :

the fields are:

Stock number (unique identifier for every car)
car manufacturer
car model
transmission type
car height
car length
engine type
radio (yes or no value )
A/C (yes or no value )
Price
warranty info
Mileage
VIN
Gross Vehicle weight
Wheel Base
Big image
Small Image
Image height
Comments from dealer
Extras


I have them separated into :

----------------------------------
Inventory table :
InventoryID
StockNumberID
TruckID (foreign key)
ModelID(foreign key
Air
CDL
EngineID(foreign key)
Lift
Radio
TranID(foreign key
Height ID (foreign key
LenghtID (foreign key
WarrantyID (foreign key
Price
GrossVehicleWeight
VIN
Mileage
WheelBase
Extra
SmallImage
BigImage
ImageHeight
Comment
---------------------
Table Engine
EngineID
EngineType
----------------------------------
Table Truck Manufacturer
TruckID
Brand
----------------------------------
Table Truck model
ModelID
ModelName
TruckID
-------------------------------------
Table Transmission
TranID
TranType
---------------------------------------
Table Truck Height
HeightID
Height
-------------------------------------
Table Truck lenght
LenghtID
HorizontalLenght
------------------------------------
Table Warranty
WarrantyID
warrantyinfo
----------------------------

Can somebody take alook at my fields in my last post above? and tell me if my tables are correct?

Let me see... Theoretically yes, but it seems way too confused.

Take it down to a basic level.

truck_table
--------------
truck_id
model_name
manufacturer_id

manufacturer_table
------------------------
manufacturer_id
manufacturer_name
contact details etc.

okay... very basic... assuming the same engine types are used for different truck makes you can add engine_id to the truck_table, and add the following tables:

engine_table
----------------
engine_id
eng_man_id
bhp
torque
etc.

eng_man_table
-------------------
eng_man_id
manufacturer_name
contact details etc.

So we now have the engine types and engine manufacturers in tables. I suppose warranties are interchangable, so add warranty_id to the truck_table and add the following table:

warranty_table
-------------------
warranty_id
warranty_details etc..

Now, your height, length and transmission tables seem irrelevent as they would likely only have the id field and one other field. They may as well be included in the truck_table as height, length and transmission. I know some people will express the virtues of look-up tables, but your web form select box can act as a look-up table for the transmission etc.

And that should be that.

thank you binky for your help. i really appreciate it .