Database Design: relate to car's model or trim?

I'm re-asking a question that was previously deleted here in SO for not being a "programming question". Hopefully, this is a bit more "programming" than the last post.

First, a few definitions:

• model - 2011 Nissan Sentra
• trim - 2011 Nissan Sentra LX

Generally, a particular vehicle would have a list of, say, available colors or equipment options. So a 2011 Nissan Sentra may be available in the following colors:

• Black
• White
• Red

Then, the manufacturer may have made a special color only available to the 2011 Nissan Sentra LX trim:

• Pink with Yellow Polka Dots

If I were building a car website wherein I wanted to capture this information, which of the following should I do:

• Associate the colors to the model?
• Associate the colors to the trim?
• Associate the colors to the开发者_StackOverflow中文版 model and trim?

My gut feeling is that associating it to the model would be sufficient. Associating to trim would mean duplicates (e.g. 2011 Nissan Sentra LX and 2011 Nissan Sentre SE would both have "Black" as a color). Trying to associate colors to model and trim might be overkill.

Suggestions?

If there are special cases, as you say, where a manufacturer has made a special color only available to a specific trim, like "Pink with Yellow Polka Dots" for the "2011 Nissan Sentra LX trim"

and you want to have those special case stored, you should choose the 2nd option.

So, your relationships would be:

1 manufacturer makes many models

1 model has many trims

1 trim can have many colors and for 1 colour many trims have it
(so you'll need an association table for this relationship)

Manufacturer
    1\
      \
       \N
     Model
       1\
         \
          \N
         Trim          Colour
           1\           1/
             \          /
              \N       /M
              TrimColour

With additional information about colours:

One GeneralColour can be named as many Colours by different Manufacturers and one Manufacturer can "baptize" a GeneralColour with various Colour (names)

        Manufacturer         
         1/      1\           
         /         \         
        /N          \        
     Model           \     GeneralColour
       1\             \       1/
         \             \      /
          \N            \N   /M
         Trim           Colour
           1\           1/
             \          /
              \N       /M
              TrimColour

Thinking more clearly, the extra Manufacturer-Colour relationship is not needed:

Manufacturer
    1\
      \
       \N               
     Model                 GeneralColour
       1\                     1/
         \                    /
          \N                 /M
         Trim           Colour
           1\           1/
             \          /
              \N       /M
              TrimColour

If different trims for the same model may have different color options (as you imply) then you should associate the color to the trim, otherwise you will have incorrect/incompatible information. aka If "pink with yellow polka dots" is associated to the "2011 Nissan Sentra" model then you will incorrectly show it as an option for trims other than LX.

You're missing the association of the trim to the model; without that, I don't know that you can really properly complete your associations.

As requested in response to my comment...

I would just make 'color' a free-form text field, possibly with a pre-populated drop-down showing current popular colors in the database. The main advantage is that it makes your DB schema much simpler, and keeps your car model/color researchers from going insane. But it also allows for custom paint jobs that aren't available from the manufacturer at all.

manufacturers
-------------
id

models
------
id
manufacturer (FK to manufacturers.id)
model_name   (VARCHAR)

trims
-----
id
model  (FK to models.id)

cars
-------
id
trim   (FK to trims.id)
year   INT
color  VARCHAR

If I were building a car website wherein I wanted to capture this information

then you'd have to build a logical model that captured that information. (How hard was that?) And that means you have to model these facts.

• Some colors apply to the model.
• Some colors apply to the trim package.
• (And I'll bet I can find a manufacturer where some colors apply to the make.)
• (And I'll bet that all these colors also have something to do with the year.)

Capturing all the known requirements is one thing. Implementing them is another. Once you understand how the colors actually work,

• you're free to ignore whatever real-world behavior you want to.

But, as Dr. Phil often says,

• "When you choose the behavior, you choose the consequences."

Simplifying the known requirements--ignoring the fact that some colors apply only to one or two trim packages--means you design your database to deliberately allow invalid data. Your database might end up with information about a "Pink with Yellow Polka Dots" Nissan Altima, or a "Copper" 2002 Nissan Sentra. (I think Nissan introduced copper in 2004.)

So here's the real question.

• How much bad data can you tolerate?

That's always going to be application-dependent. A social media site that collected information about your car color would be a lot more tolerant of impossible color choices than a company that sells touch-up paint.