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.

Any suggestions?

I reckon that year is an attribute specific to a car, not to a model of car; so I'd pick option 1 methinks

i reckon that year is an attribute specific to a model, not to a specific car, so i'd put it into the model table

...if you even need a model table!

why do you need separate tables for make and model? why not just one table for cars?

Lookups/heirachy?

What car do you drive Rudy?

what does lookups/hierarchy have to do with the question?

i drive a '69 vw beetle

Nothing specifically, just assuming that with the modelling described that was what the separate tables were for.
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?)

quite wrong

you said it yourself -- "A 69 veedub is not the same as a 66 dub."

you are talking about models, not my car versus todd's car

"a '69 beetle" is a model, not a specific car

if you are talking about my car versus todd's car, well, duh, every single instance of a car is different

"A 69 veedub is not the same as a 66 dub."
For such a classic car, yes this is true; but the model is nevertheless the same.

Type 1 VW.

Perhaps it's easier to make the distinction when looking at "newer" cars...
If we take my piece of crap car into consideration:
Make: Fiat
Model: Uno mk II
Year: 1992

Uno mk I was produced between 1983 and 1989
Uno mk II was produced between 1989 and 1995

A '93 Uno mk II is not identical to a '92, but it is still the same model car - years are almost like versioning in respect that each year minor changes are made to style/functionality etc.

okay, george, let's agree to drop this, you and i just aren't gonna agree

awohld, why do you need separate tables for make and model? why not just one table for cars?

Agreed
Cars(serialNumber, make, model, dateManufactured, dateRegistered, colour, salePrice, etc)

But as mentioned before, perhaps the make and model tables are used for lookups?

you can "look up" all cars of a certain model without a "lookup" table

Correct.
I guess I'm jumping the gun here - I'm thinking more in terms of the front end app - making sure users can't try sell a car whose make/model doesn't exist.

Like my 1987 volkswagon mondeo.

ignoring the one table vs multiple table issue...

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.

Mike

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

Last one I went to was Bristol Volksfest 2005.