Storing year/make/model in a database?

Question

Here's what I'm thinking (excuse the Django format):

class VehicleMake(Model):
    name = CharField(max_length=50)

class VehicleModel(Model):
    make = ForeignKey(VehicleMake)
    name = CharField(max_length=50)

class VehicleYear(Model):
    model = ForeignKey(VehicleModel) # or ManyToManyField
    year = PositiveIntegerField()

This is going to be used in those contingent drop-down select boxes, which would visually be laid out like [- Year -][- Make -][- Model -]. So, to query the data I need I would first have to select all distinct years from the years table, sorted descending. Then I'd find all the vehicle makes that have produced a model in that year. And then all the models by that make in that year. Is this a good way to do it, or should I re-arrange the foreign keys somehow? Or use a many-to-many table for the years/models so that no year is repeated?

Answer 1

I'm by no means an expert but I think it would go something like this?

class VehicleModel( Model ):
    name = CharField( max_length=50 )
    year = PositiveIntegerField()
    manufacturer = ForeignKey( VehicleManufacturer )

class VehicleManufacturer( Model ):
    name = CharField(max_length=50)

There doesn't need to be a separate class/table for year, just insert them directly. Though I have not thought of all the possibilities, can you tell me if this would fit?

Answer 2

I am probably oversimplifying this, but what's wrong with having a simple table with make, model and year in it? Yes, the make string is going to be duplicated in every row, but realistically - how many rows you expect in that table?

class Vehicle( Model ):  
    name = CharField( max_length=50 )  
    year = PositiveIntegerField()  
    model = CharField( max_length=50 )  

Answer 3

I would go for:

  class VehicleModel( Model ): 
    name = CharField( max_length=50 ) 
    first-year = PositiveIntegerField()
    last-year = PositiveIntegerField() 
    make = ForeignKey( make ) 

class Make( Model ): 
    name = CharField(max_length=50) 
    manufacturer = ForeignKey( VehicleManufacturer ) 

class VehicleManufacturer( Model ): 
    name = CharField(max_length=50) 

This should allow you to group up Buick, Chevrolet, Cadilac etc. under "General Moters"

Also I think your UI logic is slightly flawed, This shouds like a job for a "walk right" menu, ie, you need a pulldown of all your manufacturers, when a particular manufacturer is selected you can present a sub-menu of models, when a model is selected you can then preset a sub menu of years.

Alternativly you should edit the contents of the "model" selection whenever a new manufacturer is selected, then whenever a model is selected the year selection should be updated.

As described your UI would allow me to enter a "Studebaker" "Model T" "2010"