DB Design Help?

[imstillatwork]

I don't know why i am haveing a tough time on this one. i could just do it, but i want to do it the best way.

Its for cars; make, model, engine,

I'm just not convinced this is the way I should do it, because I also want to inlcude the years for the cars, and that vairies per model / engine combination

I was going to have a cars table that would have the following, but need help with getting the years in there also. Should Each row in the CARS table have a YEAR field, so a 1993 VW Corrado SLC and a 1994 VW Corrado SLC would have their own rows, even though they are basicly identica cars? or should I do a single row with a from and to year field?

i am leaning towards the one row for every configuration, since the data is small (mostly foriegn keys) and it would be precise and easy to look up between years, by model, etc..

Someone have any suggestions? please?

CARS
- id
- make_id
- model_id
- engine_id
- info


MAKE
- id
- name
- info

MODEL
- id
- name
- info

ENGINE
- id
- cylinders
- hp
- displacement
- trq
- etc...


Kevin

http://www.lot-o-nothin.com/cfml/

 

[dodge20]

Should Each row in the CARS table have a YEAR field, so a 1993 VW Corrado SLC and a 1994 VW Corrado SLC would have their own rows, even though they are basicly identica cars?

My suggestion is yes to this question. Putting in a date range for the year will likely cause problems in the future.

Another option would be to have a year table, but that may not be the best solution for you.

Dodge20

 

[vacunita]

That is the correct way of doing it.

Having a year for in the cars table is information needed in each particular car's uniqueness. That said:

A 1968 Dodge Charger is Radically different than 2005 Dodge Charger.

Having a Date per vehicle will not be considered redundant as it is needed for specificity purposes.
It will also help with searches for cars based on production eras or somethng similar.

So yes include the year in the Cars Table.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[imstillatwork]

I need to rethinnk this.... The DB should be centered on the PARTS, not the CARS!!!

PARTS
- id
- part#
- etc

CATEGORY
- id
- name
- parent_id (for cat/subcat)

PART_CAT
- part_id
- cat_id

PART_ENGINE
- part_id
- engine_id

PART_MODEL
- part_id
- model_id

I don't know. I'm making this worse than it is. I need to get home so I can actually play with it and not just think about it.


Suggestions welcome!







Kevin

http://www.lot-o-nothin.com/cfml/