If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed

Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something. You seem to be saying the opposite. Am I reading
you right?

Walter Mitty wrote:

If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed

Elsewhere, it's been said that a key should NOT carry any information
beyond identifying something. You seem to be saying the opposite. Am I
reading you right?

Of course.

Mr. Elsewhere is wrong.

The following links just scratch the issue. I suppose it's been going on
for more than 20 years.

http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327
http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-ii-7345
http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-iii-7365

"Marco Mariani" <ma... at (no spam) sferacarta.com> wrote in message

news:mlRZk.4018$J84.960 at (no spam) tornado.fastwebnet.it...



Walter Mitty wrote:

 If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
 Ed

Elsewhere, it's been said that a key should NOT carry any information
beyond identifying something.  You seem to be saying the opposite.  Am I
reading you right?

Of course.

Mr. Elsewhere is wrong.

The following links just scratch the issue. I suppose it's been going on
for more than 20 years.

http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327
http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-ii-7345
http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-iii-7365

You are conflating two issues.  The first is surrogate keys vs. natural
keys.  The second is keys that encode multiple attributes vs. information
free keys.

On the issue of surrogate vs. natural I agree with what I think you said:
natural keys are to be preferred, whenever they are reliable.

On the isuue of whether a key should merely identify or should be made out
of a composite that encodes several attributes, you are wrong.  The VIN is a
classic case of a key that was constructed the wrong way.  And the problems
with VIN make my case for me.

There is a place where composite keys are worthwhile:  when identifying
instances of a relationship.  Even in this case, the composition should be
acheived by composing a key from multiple columns, and not by creating a
single column that encodes multiple attributes.

Walter Mitty wrote:
 If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
 Ed

Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something.  You seem to be saying the opposite.  Am I reading
you right?

Of course.

Mr. Elsewhere is wrong.

The following links just scratch the issue. I suppose it's been going on
for more than 20 years.

http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-ii-7345http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-iii-7365

Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something. You seem to be saying the opposite. Am I reading
you right?
Of course.

Mr. Elsewhere is wrong.

Mr. Elsewhere?

I guess it's better than calling me the lame nickname of Mister Ed.

You are conflating two issues. The first is surrogate keys vs. natural
keys. The second is keys that encode multiple attributes vs. information
free keys.

of a composite that encodes several attributes, you are wrong. The VIN is a
classic case of a key that was constructed the wrong way. And the problems
with VIN make my case for me.

On Dec 4, 1:04 pm, "Walter Mitty" <wami... at (no spam) verizon.net> wrote:
....
There is a place where composite keys are worthwhile: when identifying
instances of a relationship. Even in this case, the composition should be
acheived by composing a key from multiple columns, and not by creating a
single column that encodes multiple attributes.

I'm not sure I see the difference here. Columns are just an
implementation of attributes, aren't they?
(maybe we should that this part of the discussion to
comp.database.theory? your call)

"Marco Mariani" <ma... at (no spam) sferacarta.com> wrote in message

news:mlRZk.4018$J84.960 at (no spam) tornado.fastwebnet.it...



Walter Mitty wrote:

If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed

Elsewhere, it's been said that a key should NOT carry any information
beyond identifying something. You seem to be saying the opposite. Am I
reading you right?

Of course.

Mr. Elsewhere is wrong.

The following links just scratch the issue. I suppose it's been going on
for more than 20 years.

http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327
http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-ii-7345
http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-iii-7365

You are conflating two issues. The first is surrogate keys vs. natural
keys. The second is keys that encode multiple attributes vs. information
free keys.

On the issue of surrogate vs. natural I agree with what I think you said:
natural keys are to be preferred, whenever they are reliable.

On the isuue of whether a key should merely identify or should be made out
of a composite that encodes several attributes, you are wrong. The VIN is
a
classic case of a key that was constructed the wrong way. And the problems
with VIN make my case for me.

There is a place where composite keys are worthwhile: when identifying
instances of a relationship. Even in this case, the composition should be
acheived by composing a key from multiple columns, and not by creating a
single column that encodes multiple attributes.

Ed Prochak wrote:

Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something. You seem to be saying the opposite. Am I reading
you right?
Of course.

Mr. Elsewhere is wrong.

Mr. Elsewhere?

I guess it's better than calling me the lame nickname of Mister Ed.

Sorry Ed, I was jesting. I meant "the people who told/wrote you,
Elsewhere, that bla bla..." are wrong. My coat is the same colour as
yours (*)



(*) even though I have no experience with big databases or huge
applications, would never dream of touching a line of code that runs
inside an airport or train station, and cannot for the life of me
understand why a VIN pkey can have problems.

"Ed Prochak" <edproc... at (no spam) gmail.com> wrote in message

[snip]
On the isuue of whether a key should merely identify or should be made out
of a composite that encodes several attributes, you are wrong. The VIN is
a
classic case of a key that was constructed the wrong way. And the problems
with VIN make my case for me.

Enlighten me. Can you give a few examples of the problems? I know of
problems in the implementation of VIN (vehicles assigned the wrong VIN
codes). But I don't think that is the problem you mean.



There is a place where composite keys are worthwhile: when identifying
instances of a relationship. Even in this case, the composition should be
acheived by composing a key from multiple columns, and not by creating a
single column that encodes multiple attributes.

I'm not sure I see the difference here. Columns are just an
implementation of attributes, aren't they?
(maybe we should that this part of the discussion to
comp.database.theory? your call)

From the point of view of content, I would say,  yeah, let's move it over to
comp.database.theory.

But, if we do that, we're going to provoke responses from a certain
individual whose initials are BS.
And I'm not sure I can stand to read his exposition on this particular
subject.

I'll give you the short version:  Some of the attributes used to construct
the VIN, (IIRC) are mutable attributes.  Some of the people who use the VIN
figure out how to decompose the VIN into the component attributes.  Then,
they use the value of the attribute at the time of manufacture as if it were
the current value of the attribute, which it (sometimes) no longer is.  They
congratulate themselves on having  saved a table lookup (whoop-de-doo)  and
shrug off the fact that they deliver outdated results.

Admittedly this problem falls under the general category of VIN misuse,
which is like the comment you made about ID misuse in a different subthread.
All I'm gonna say is that the VIN invites misuse.  It's a trap for fools.
If someone such as you or I were to use it as if it were a meaningless
identifier,  we would avoid this trap,  at the cost of running a little
slower than the geniuses who skip the table lookup.

How does one handle cars manufactured before VINs came into use? How about
books before ISBNs began to be used? Which identification systems are
commonly used?

On Wed, 3 Dec 2008 21:43:57 -0800 (PST), Ed Prochak wrote:
[to be safe on the quotes I snipped all but the last one, mine.]
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed

On Dec 3, 10:48 am, paul c <toledobythe... at (no spam) oohay.ac> wrote:
Ed Prochak wrote:
On Dec 1, 10:07 am, paul c <toledobythe... at (no spam) oohay.ac> wrote:
Ed Prochak wrote:

...

Are these IDs known to users?
    If the answer to either of these is yes, then use the IDs..
...
If they are useful (pertinent if you like), they will become known..  The
only decision to make about such ID's is whether they have a pertinent
use in one of the applications.

Applications are not the users I was referring to. Applications (code)
are part of the system and must therefore have access to such ID's.
But that's not relevant to the original question.
ed

Alright, to be pedantic, if they are useful to users they will become
known by users.

Agreed.

If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed

"Elmer Fudd" <bitbucket at (no spam) 127.0.0.1> wrote in message
news:ovgohpxrg.kc4num0.pminews at (no spam) news.verizon.net...
How does one handle cars manufactured before VINs came into use? How about
books before ISBNs began to be used? Which identification systems are
commonly used?

On Wed, 3 Dec 2008 21:43:57 -0800 (PST), Ed Prochak wrote:

How does one handle cars manufactured before VINs came into use?

How about books before ISBNs began to be used? Which identification systems are commonly used?

How does one handle cars manufactured before VINs came into use?

You will be issued a VIN if you want to drive the vehicle on a public
road. If you build your own vehicle, then you inherit the VIN on the
engine block or you get issued one. If the manufacturer went out of
business before 1950, you get a dummy value from a DMV or national
agency. Hey, they want your money.

Here are some more details: http://www.vinguard.org/vin.htm

How about books before ISBNs began to be used? Which identification systems are commonly used?

Having owned some bookstores in the past, it is much the same scheme.
We used a dummy language sub-code of 99 (97 is Esperanto), a Dewey
Decimal Classification and then a sequence within the DDC.

In retail, if you make a product in-house (bakery in a grocery store),
there are "open codes" in the UPC so you can bar-code the goods.

If you look for it, you will find a standard or two that will help and
give you validation and verification rules.