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 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??
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.
if status in ( 4,13,-7) then ...
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.
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