Hello everyone,

I have to design a website for a car dealer. This dealer sells both new and used cars. So I have to design a database that manages the dealer's inventory. People can sell their car to this dealer through a form on the website.

I wanted to check with you guys that my database model is right before I start programming:

TABLE car_make (make_id, make_name)

TABLE car_model (model_id, make_id, model_name)

TABLE car (car_id, make_id, model_id, stockno, year, price, type, interior_color, exterior_color, fuel_type, transmission, doors, used_or_new, state_id)
[state_id means whether the car belongs to the dealer or is a potential car from a seller]

TABLE features (feature_id, feature_name)

TABLE car_features (car_id, feature_id)

TABLE pictures (picture_id, car_id, picture_small, picture_large)

TABLE people (people_id, name, phone, address, email)

TABLE people_car (car_id, people_id, buyer_or_seller)
[people cold be the buyers (clients) or sellers (traders)]

Now there will be a form in the website through which any website visitor may enter information about the car they may want to sell to this dealer. The form contains exactly the same fields as in the TABLE "car" + the TABLE features. Upon submittal of the form, I don't know whether I should include that form info directly into the database or create a new table that manages trades??

Any help would be greatly appreciated.

Thanks much

I know different states/provinces have different emissions standards, you may want to include which emission standards the car meets:

emissions_id state_id year

(I include year because standards change from year to year)

You may also want to check out the forms on a number of different sites and see what they include.

i would probably not have the makes and models table

i would not put trades in a separate table

if I do not include make and model tables, how could anyone search for a car of a particular make and model?

Thanks

like this:

thanks for your prompt reply.... you're saying that instead of creating 2 tables, I should just include 2 fields (car_make, car_model) in the car table, right?
Thanks

While I completely understand Rudy's rationale, I have to ask him a question... How do you validate that a user has entered a correct value for make? I've never heard of "foord"

Personally I like (and argueably overuse) "lookup tables" which use foreign key constaints. "Ford" is a perfectly god natural key to use; but having a "master make" table allows RI to be enforced.

Whatcha think Rude-meister?

A lot of these topics were discussed here about storing car info.

I prefer a lookup table for the car makes but suspect it will be difficult to do the same on models. You might want to have very loose matching on the models ie converting to lower case, and using like or the "sounds like" functions.

I notice you're big on id's in all your tables (status, features, make) - it may be a personal preference but I find it difficult difficult dealing with meaningless numbers when readable codes are easier to understand i.e.

Code:

if status in ( 4,13,-7) then ...
or
if status in ( 'IN STOCK', 'LOOKING FOR', 'SELLING' ) then

With complicated details like the emissions it may be better just ignoring that data all together and leave it to the salesman. I know I get flustered when I'm asked to fill in anything complicated like that into a form so coding for those details may, apart from being difficult, be counter productive.

Mike

to reply to this question, it is necessary to assume a priori that one wants to validate the value

and i like how you phrased it ... you've never head of "foord"

but are you certain that you can "pre-load" all the makes and models that you're going to get?

suppose your web site is a site where people can list their cars for sale, and other people can search the cars for sale, and you've got a revenue stream from that

are you seriously going to reject every make and model that you haven't heard of? wouldn't that be new-business-hostile...

okay, with that question out of the way, we can move on to validating, i.e. enforcing

well, george, i'm surprised you're asking the question

the answer is: with foreign keys!

Touché my Canadian friend, touché

I would typically do what George and Mike have suggested. I consider the lookup table in this instance to be more for the benefit of the application than the database - I don't consider it a data integrity issue but a data correctness issue. I would not restrict the values to only those in the table at the time of entry. If a user enters "foord" then they would be politely informed that we don't have a record of that manufacturer, but we have some similar such as.... "Are you sure this is your manufacturer?" If they say yes then add the value to the lookup table.

This just reduces the likelihood of typo errors. On the other hand, it requires more code in the app however it is the sort of thing you can write generic code for.

Just me $0.2c. Feel free to refund me if ya don't like it

Of course, if the OP had a requirement to record information about manufacturers then this becomes a totally different discussion....