Re: When is it a look up table and when is it an real entity?

Well, here are some things you could think about as you consider this decision:

Do you want to force users to select one of the colors in the table? Or can they type one in that doesn't match anything?

Assuming you're letting users use something not in the table -

    are new entries added to the table?

What if an entry in the table changes?

    (just as a "for instance", a manufacturer decides to rename his existing color "blue" to "deep blue" because he's also introducing a "pale blue".)

    Do you want all of the existing cars which are labelled "blue" to now become "deep blue"?

If you define a relationship and enforce referential integrity, all colors used must be in the table (but you can leave the field blank).

    To add a color, you must add it to the table. If you add cascade updates to your referential integrity, a change in the colors table will propagate into your vehicles table. Also (this doesn't apply to any of the questions above) if you add cascade deletes to your referential integrity, removing the color "red" from your colors table will remove all red cars from the vehicles table.

So, you see, it pretty much depends on how you want your application to work.

HTH

• Turtle

"Emily Jones" <emmersthejones_at_hotmail.com> wrote in message news:40c3a66a$0$58819$5a6aecb4_at_news.aaisp.net.uk...
> That subject line will have Joe Celko apoplectic!!
>
> Let's say I have this thing. Oh, I don't know, a car. No, a vehicle. Can
we
> agree that is an entity type?
> Some attributes might be VIN, Fuel, Color.
> Some entity instances might be:
> wvv2939-32-2993-2-90, Gasoline, Blue
> wvv3942-3249--2830-2, Diesel, Red
>
> I wanna make it easy to get the colors in. Keep a list of 'em somewhere.
So
> is that an entity that has a many to one relationship to vehicle? Or do I
> just have a standalone table somewhere with a list of colours, grab one
when
> I want it and shove it into the right vehicle field.
>
> Which approach best? How to decide.
>
> Yours, Em
>
>
>
>
Received on Mon Jun 07 2004 - 02:21:59 CEST