I'm looking to store the following: Make, Model, Year, Sale Price, Serial Number.
I know I will have the following table relationships:
1. Table "Make" with a ONE to MANY relationship to table "Model"
2. Table "Model with a ONE to MANY relationship to table "Car". Table "Car" contains "Sale Price" and "Serial Number"
But where should I put year into there?
Here are my three ideas:
1. Put year it into the "Car" table as a data field.
2. Have a "Year" table with a MANY to MANY relationship to a table named "Year_Model" which links tables "Year" and "Model" together.
3. Have a "Year" table with a MANY to MANY relationship to a table named "Year_Make" which links tables "Year" and "Make" together.
what does lookups/hierarchy have to do with the question?
Nothing specifically, just assuming that with the modelling described that was what the separate tables were for.
Originally Posted by r937
i drive a '69 vw beetle
The year is specific to your car, not to the model.
In this example, beetles are Type 1 Volkswagons. But you and I both know that year plays a very big part for the car. A 69 veedub is not the same as a 66 dub. (Am I right in thinking '67 was a big year of change for the beetle?)
I think as far as what you asked, the question would be: Is year information associated with model or is it a piece of data that belongs to each car.
If you want to have a Mustang for each year that they make one ('69 Mustang.... 2007 Mustang) then it is part of the model and each car will have that model/year linked to it. (you could have year ranges as george mentioned if there are no changes for a span of years)
If you just want to indicate a model and store which year the car was then that is fine. This is probably a good way unless you plan to store model differences later that would mean a 1969 Mustang was different than a 2007 Mustang.
Good god - some thing I agree with Rudy on! I've owned my 1972 beetle for 20 years - it tends to be cosseted more than driven though.
I agree with George though that the manufacturer should be in it's own table. I guess most car web site databases do have manufacturer in their own table as you normally get a list of manufacturers to choose from when searching for cars. It would also be useful if you wanted to store the contact details etc for the manufacturer.
But we've both jumped the gun Mike - we have no idea how this system will be used - let alone whether it has a FE or not
I love dubs - have done ever since we went on a camping holiday and got half way there when the darn thing broke down... We never did get to go camping.
I haven't been to any of the shows in over 2 years now though - I was supposed to go to one up in Leeds 2 weeks back but something came up. One of our friends is a trader and she gets a load of free tickets to these things and offered them up! Shame I missed another good one