DB question for the car-knowledgeable

I'm not so familiar with cars and I'm working on a data model to support the various pieces of information relating to cars.

• Here's an example car model: 2011 Nissan Sentra
• Here's an example car trim: 2011 Nissan Sentra 2.0

Are factory options such as "sun roof" a property of a "model" or a "trim?" It seems that a model has a finite list of possible factory options, but a trim is defined by the inclusion or 开发者_高级运维exclusion of certain factory options.

I'm trying to understand the relationship of factory options to model and trim -- from a data modeling perspective.

Ideas?

Personally I would think it's a property of trim. Your model will always be a "2011 Nissan Sentra". Your trim might then be "2011 Nissan Sentra Super Turbo Mode", and then that super turbo mode may or may not include the sunroof property.

Or maybe not, it could make sense that a model supports a sun roof, and therefore every trim can have it if the model supports it. But normally sun roofs can be fitted to cars anyway.

I think you need to dig into the data a bit more to see how it works. But I'm going with trim

From your description, I think you might need 3 relationships as a minimum:

• R1: Model-TrimLevel. Defines which trim levels are available on which models. Many:Many (i.e. each TrimLevel is available on many Models, each Model available in many TrimLevels)
• R2: TrimLevel-Option. Defines which options are included in each trim level. Again Many:Many (each TrimLevel includes many Options, each Option included in many TrimLevels)
• R3: Model-Option. Defines which options are available on which models. Many:Many (each Model available with many Options, each Option available on many Models).

Assuming I understand your question I suspect both R2 & R3 are necessary rather than either/or:

• R2 covers the case where options are included automatically by selecting a given trim level
• R3 allows options to be selected outwith the trim level.

For example: take the Ford Fiesta (Model) GL (TrimLevel). That comes as standard with alloy wheels (R2) but doesn't include leather interior. However a buyer could, if they so chose, add the leather option. Provided, of course, leather is an available option for the Fiesta (R3).

Where you go from there will really depend on your requirements. You'll probably need some queries over the relationships (e.g. what happens if someone selects an option that's already included in the trim level they've picked?).

You may also need to introduce attributes on the relationships. For example, leather is typically a cost option. On the Fiesta, it might be £1000. On the S-max it's £1200. That means you need to capture price as an extra attribute on R3. Equally, the inclusion of an option in a TrimLevel could be at no cost or a discounted cost. That means an attribute on R2.

In general the relationship complexity will depend on how much you need to represent. It's an interesting problem and can get quite complex. If you're interested you might want to take a look at Feature Modelling. It covers exactly this sort of configuration question in a much broader sense. (otoh, worth saying that you may well not need anything more than the simple 3 relationships above).

hth.

Aren't they a collection of Option on the Model?

The way I see it, having a collection of certain Option with the Model does not indicate a Trim level, but a Trim level always requires Model. Its a one-way relationship, even though there might be some sort of inference you could apply to the Option settings. ie: the Sux 2000 LX model always has a 6-cylinder engine, and the LE doesn't. You could infer that the 6-cylinder engine is the LX model, but that wouldn't hold for all cars.