logistical db question for conciseness

[jkatcher]

hi --

the site i'm working on is something like autotrader.com or vehix.com in which you select your state then the make, model, year, etc of your car, but it will also be for trailers. i was planning to make three tables for each (make, model & year w/ IDs in each) Will i need to make 50 (times 2) tables? i am having trouble logistically working this out.

for Arizona i.e.

az_trMake (make of trailer)
az_trModel (model of trailer)
az_trYear (year of trailer)
az_vMake (make of van)
az_vModel (model of van)
az_vYear (year of van)

for Arkansas etc.............

ak_trMake
ak_trModel
ak_trYear
ak_vMake
ak_vModel
ak_vYear

thank you for any thoughts you can share...!!!!!

[calua]

no, that would be unworkable. if i understood right, all you need is a table with the fields: make, model & year AND state (an id field wouldn't hurt either, as there can be more than one car of the same make, model and year in the same state).

is that it?

Caluã

[jkatcher]

yes, that's right. i'm not sure why i had the excessive tables in my head! once the seller has entered their information, the buyer will be able to browse by whatever they want (year, make, model, amenities, size, cubic space, etc.)

so you're saying that the state is a field within the table, along with the other attributes of the vehicle?

i would end up with two tables: van & trailer

within each there would be state, make, model, etc? that makes more sense...is that what you were thinking?

thank you for your help!!

[laserlight]

Ask yourself the question:
What is the relationship between a state and a type of car?

The answer is that it is a many-many relationship.
A type of car can be in many states, while a particular state can have many types of cars.
For a many-many relationship, 3 tables are required.

I think you need to work the relationships out further.
On the same table, you effectively have multiple make, model, and year fields.
This is bad practice.

Again, you need to determine their relationship.

This could be an example, assuming that make and model are specific to each vehicle type.
Table: states
state_id
name

Table: vehicle_types
vtype_id
name
make
model
year

Table: spec_vehicle_types
svtype_id
vtype_id
state_id

You might want to read Database Normalization And Design Techniques

[laserlight]

Oops, I neglected the fact that you wanted to deal with vans and trailers as well.
This means that one more table is needed:

Table: states
state_id
name //Arizona, Arkansas, etc

Table: generic_vehicle_types
gvtype_id
name //trailer, van, etc

Table: vehicle_types
vtype_id
gvtype_id
make
model
year

Table: spec_vehicle_types
svtype_id
vtype_id
state_id