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

[Emily Jones]

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

[MacDermott]

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" <em************@hotmail.com> wrote in message
news:40***********************@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

[B. Austin]

Emily -

Well in 20 years of data modeling I have decided it is appropriate to show
'Color' as an entity in the model with a one-to-many relationship to 'Car'. Even
though the primary reason for its existence is only to translate codes into
their corresponding descriptions. Which implies I guess that it is also used to
validate user input.

I even made up a name for these special model objects: 'entribute'. Not exactly
an entity, but something more than just an attribute. Formal modeling theory
might have some official name for them?

Showing entributes in the model means that they should be documented just like
any other entity - always a good thing, especially for application-specific
things like 'Employee Type'. Also simplifies the process of forward-engineering
the database if you are using something like ERwin.

Of course having all these extra objects complicates the diagram somewhat. If
the diagram is large, I will produce two versions: one showing all the entities
and another showing just the base entities without the entributes.

Sometimes I think that there really is no such thing as a 'lookup table'. An
application may start off with a State table for validating State Codes, but
over time the table may evolve to include other things (Population, Number of
Counties), and at some point it will cross the fuzzy line dividing lookup tables
from 'real tables'.

HTH.

- Brian

On Mon, 07 Jun 2004 00:21:59 GMT, "MacDermott" <ma********@nospam.com> wrote:

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" <em************@hotmail.com> wrote in message
news:40***********************@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