Natural keys vs Aritficial Keys
Walter Mitty
When I learned databases, about a quarter of a century ago, the preferred
practice was to use natural keys to refer to individual table rows, unless
there's some real good reason to go with an artificial key. I've run into a
few cases where I chose to use an artificial key, but most of the time I've
used a natural key. (I should clarify: if a university's registration
office assigns each student a StudentID, before entering that student into
the database, I'm treating that key as "natural" in the context of the
database, even though one might argue that it's "artificial" in some other
context.) This generally surfaced in the choice of one or more columns to
be declared as a PRIMARY KEY.
Every time the issue of natural keys comes up in Stackoverflow.com, the
prevailing view seems to be that the best primary keys are artificial and
opaque. And responses that take this view get the votes. They emphasize
efficiency (mainly efficiency in the index that you usually get
automatically when you declare a PRIMARY KEY). But data integrity seems to
be forgotten, here. The same is not generally tru when the conversation
turns to referential integrity. Slowly but surely the programming community
seems to heve been dragged, kicking and screaming, into turning over
referential integrity enforcement to the DBMS.
What I don't get is whether I've understood something that today's
trendsetters never learned or whether they are taking into consideration
matters that I never considered important, back in the day. I've read their
arguments carefully, and it seems to me that failure to declare UNIQUE and
NOT NULL constraints on the most widely used candidate keys is simply an
invitation to degradation of data integrity. PRIMARY KEY is a convenient
way to get those two constraints with one declaration.
Is data integrity less important than it used to be? Are most people
building databases that get embedded in some appplication to be sold to the
non technical buyer? Is something else going on that I'm unaware of? Can
some body explain to me what these people are thinking?
Walter Mitty
paul c
I don't think it's you who's confused.
Troels Arvin
> Every time the issue of natural keys comes up in Stackoverflow.com, the
> prevailing view seems to be that the best primary keys are artificial
> and opaque.
I've seen the same tendency at Wikipedia. I've tried to engage in
discussions about it there, but I've given up (sometimes, you just have
to give up on Wikipedia). In a discussion, I even provided benchmarks to
the most eager surrogate-proponent, showing worse performance for a type
of query when surrogate keys were used -- but this was ignored.
And I believe that the tendency is due to
- Misguidedness. When I started in the programming field,
I thought that every table should have a generated integer
"id" column; it somehow felt "nice". But after a dose of
education and experience, the fixed idea went away.
I think many people find it somehow comforting to have
an "id" handle, instead of appreciating the idea of
letting the data itself be the driving force. The
navigational versus the declarative approach.
- Deficiencies in object relational mappers which
either don't support multi-column keys, or don't make
it easy to handle multi-column keys.
- Some DBMSes which don't support CASCADE in definition
of foreign keys.
Some of the performance arguments may be valid: There are cases where
they are more efficient than natural keys, although the extent of it is
probably seldom measured, making it a premature optimization.
On the other hand, there are cases where the use of surrogate keys
results in _worse_ performance, due to the need for extra joins, for
example. And all else being equal, the surrogate columns add extra bytes
to the database; if all tables have unneeded surrogate keys, it can
actually amount to a sizeable amount of "dead" bytes in the database.
But the situation is also due to the SQL standard and the DBMS
implementors:
- Many people stay away from NATURAL JOIN because it's
seen as being dangerous, having the potential for being
source of bad, subtle problems if a column is renamed.
I wish the SQL standard had defined NATURAL JOIN to
be based on referential integrity definitions (foreign
keys) instead of naming conventions. Maybe the standard
could be extended with a new "NATURAL REFERENTIAL JOIN"
or simply "REFERENTIAL JOIN", so that joins could be
expressed in a terse and safe way, even where multi-column
keys are involved.
- I know of no DBMS which implement the obvious optimization
where a "costly" natural key based referential integrity
constraint (such as multi-column keys involving textual
columns) is handled behind the scenes through hidden
pointer-like (integer?) row IDs.
> Is data integrity less important than it used to be?
It seems so :-(
And not just integrity which is at risk: The less you tell the DBMS about
your data, the less it can use the knowledge for semantic query
optimization. Also, when you don't express known constraints about your
data, the application can't make use of this metadata knowledge for GUI
hints, etc.
--
Troels
Tony Toews [MVP]
>When I learned databases, about a quarter of a century ago, the preferred
>practice was to use natural keys to refer to individual table rows, unless
>there's some real good reason to go with an artificial key. I've run into a
>few cases where I chose to use an artificial key, but most of the time I've
>used a natural key. (I should clarify: if a university's registration
>office assigns each student a StudentID, before entering that student into
>the database, I'm treating that key as "natural" in the context of the
>database, even though one might argue that it's "artificial" in some other
>context.)
That's a decent natural key. But life is seldom so clear.
Consider names. My brother has the same birth date, first, middle and last name as
a career criminal in Canada. And we have a somewhat rare last name. It's a pain for
him to cross the border into the US. He allows himself an extra hour. Although
they must now have photo's in the US Customs computers as lately the delay as been
quite a bit shorter.
Consider business name. Duplicate business names can exist in adjacent provinces,
states or countries.
Now what happens when you get to the child tables? For example student/course
table. What do you use as a primary key? Student ID, Course Number? What if a
student takes the course a second time? Now the primary key needs three fields and
some logic to ensure the third field is unique. What about child tables of that
child table? Now it's a table with four fields, or more in the primary key.
>Every time the issue of natural keys comes up in Stackoverflow.com, the
>prevailing view seems to be that the best primary keys are artificial and
>opaque. And responses that take this view get the votes. They emphasize
>efficiency (mainly efficiency in the index that you usually get
>automatically when you declare a PRIMARY KEY). But data integrity seems to
>be forgotten, here.
How does this affect data integrity?
When there is a possibility of duplicate data we should be presenting the users with
the current data that is a close match so the user can decide if the
person/company/whatever is already in the system or not. For example the first two
letters of the first and last name give surprisingly few matches in a database I have
of 10,000 names. Even Jo Sm for Joan Smythe.
>The same is not generally tru when the conversation
>turns to referential integrity. Slowly but surely the programming community
>seems to heve been dragged, kicking and screaming, into turning over
>referential integrity enforcement to the DBMS.
I don't quite understand that the problem is with turning referential integrity over
to the DBMS.
I'm quite happy with an artificial primary key (autonumber in Access) and unique
indexes in data fields as appropriate.
That said Access forms and reports don't handle multifield artificial keys very well.
The wizards, for example, don't handle them at all. So you're first to labouriously
enter the field names by hande in the various properties such as a subforms Link
Child Fields and Link Master Fields properties.
I should point out that
1) I build simple and moderately complex applications in MS Access so that clearly
colours my viewpoints. A product many readers of my postings in this newsgroup hold
in derision.
2) I look at this from both the DBAs viewpoint and the developers viewpoint although
again many folks in this newsgroup would not consider me a DBA of any sort.
3) I've managed to irritate a number of the folks in this newsgroup with my postings
in the past. As a result I've been plonked by a few. Oh well.
3a) As a result if one or two of those read replies to me they will be complaining
that they were forced to read my opinion. <shrug>
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/
paul c
...
Basically, it's just a phony issue foisted by db semi-literates, usually
product-oriented to the extreme, like the kind who get into arguments
about whether table names should have an 's' at the end or who grew up
with 1980's 4GL's and their puerile successor products that couldn't
support composites. A table/relation/relvar should have exactly the
attributes and exactly the keys that the app needs. It is just
self-flagellation to ignore a dbms feature that will generate some of
them (assuming the dbms doesn't insist on them).
The so-called 'natural' key for all the airwaybills in a ULD on a
typical airline flight segment is about seven or eight attributes. When
the cargo master, at the last minute, offloads a container or two onto
the tarmac, you don't want a ponderous logic to un-assign it and put it
on the plane at the next gate. You need a form of indirection, for that
matter the same is needed in any robotic application such as a freight
warehouse.
Somebody in this thread suggested, if I took the meaning right, that a
design could actually profit from using a single attribute to stand in
for a composite key, where both are part of the design. I've seen such
systems and considered that to be perfectly reasonable, although one of
the big names in the field told me I was confusing physical with
logical. But I say the big names aren't right all the time. I think
there is such a thing as logical efficiency, not just physical
efficiency and sometimes it is a requirement that a 'time-critical'
massive update be coded as simply as possible, leaving the slow-poke
work-a-day requirements to handle the ponderous side. I admit that
doesn't happen very often now with all the fast machinery around. The
decision for me would be based entirely on whether the application
required it, and nothing to do with any dogma.
Roy Hann
> I'm confused about some recent trends in database design.
No you're not.
[snip]
> What I don't get is whether I've understood something that today's
> trendsetters never learned or whether they are taking into consideration
> matters that I never considered important, back in the day. I've read their
> arguments carefully, and it seems to me that failure to declare UNIQUE and
> NOT NULL constraints on the most widely used candidate keys is simply an
> invitation to degradation of data integrity. PRIMARY KEY is a convenient
> way to get those two constraints with one declaration.
That misses the point. PRIMARY KEY and UNIQUE are supposed to prevent
potentially contradictory facts. They have nothing to do with
addressability. If RDBMSs or SQL DBMSs needed the concept of an
address they would have it and it would be unique and you wouldn't
have to everywhere repeatedly assert the most obvious property of an
address is uniqueness.
I think the urge to want to address rows is not just a hankering for
network databases though. Programmers worry about what to do if a user
mis-keys a natural key value which then gets propagated to a number of
tables. If they have kept the natural key in one place it needs to be
fixed in only one place. I've had quite good luck weening colleagues
off spurious synthetic keys by encouraging them to declare their
foreign keys with ON UPDATE CASCADE. (One or two demand benchmarks
before they believe it is practically free, but generally they get it.)
I suspect that it became established "best practice" to use
synthetic keys everywhere back when few SQL DBMSs supported ON UPDATE
CASCADE and it has just stayed with us.
> Can some body explain to me what these people are thinking?
They aren't.
--
Roy
Brian Selzer
"Walter Mitty" <wam...@verizon.net> wrote in message
news:7ahPl.1492$5F2...@nwrddc01.gnilink.net...
I'm going to use a term that provokes derision from the all-natural crowd:
rigidity. If a natural key rigidly designates or describes something--that
is, if it permanently identifies somthing in the universe, then there is no
/logical/ reason to add an artificial key. But not every instance of a key
is a permanent identifier: consider a table where the entire heading is the
key. Do you permit updates to such a table? If updates are allowed, then
what does it "mean" when one occurs? Did what was represented in the
database merely change in appearance, or was it replaced by something else?
And if it were replaced, then wouldn't it have been better to issue a delete
followed by an insert instead? Wouldn't that more closely represent what
actually occurred?
The delay between the time that a row is read and the time that an update
occurs, and the possibility that a change initiated by another user can
occur during that interval justifies the introduction of an artificial or
surrogate key, but only in the event that instances of the natural key do
not rigidly designate or describe something in the universe.
Walter Mitty
"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:jf4s059mn7epor41g...@4ax.com...
Thanks for your response. You've given me a lot to ponder.
On the question of names, I consider names to be so different from natural
keys that I honestly didn't even give a thought to names when I first raised
the question of natural versus artificial keys. We all know the reasons why
names fail as keys, and you've outlined some of them. About all we can say
about names is that they were used of identification and reference purposes
in the era before computerization. When I say natural key, I mean something
that's immutable, not used twice in two contradictory fashions, and not
optional regarding the thing being identified. As I said in my OP, keys
viewed as natural by the database may well be viewed as artificial in some
larger system, one that includes applications and maybe even humans.
The student/course relation is a difficult example for considering
parent/child relationships, because it's really a junction box to capture a
relationship between many students and many courses. Is it a child of the
student, or is it a child of the course? This is actually the kind of thing
that resulted in so much trouble in the hierarchical and network databases
that existed in 1970 and shortly therafter. As far as relationship tables
go, I see nothing LOGICALLY wrong with assigning them a composite primary
key. As to whether a given student and a given course can have more that
one instance of a relationship between them, that's actually a matter of
how the subject matter organizes the data, isn't it? And wouldn't the
solution, in any event, be to come up with a ternary relationship in which
the relationship among a student, a course, and some third thing has at most
one row per instance?
This can only be discerned by studying the subject matter, and analyzing the
data. Adding a ID PK field to the binary relationship just allows you
postpone analysis until after the design and implementation are done. I try
to avoid doing that, for obvious reasons.
With regard to presenting the user with the opportunity to disambiguate
duplicate data, I think that's very relevant to a certain class of
problems. But there is another class of problems where is of the essence to
prevent such ambiguity from arising in the first place. I think it's that
latter class of problems that I was mostly concerned with, back in the day.
The question of whether Access wizards deal with composite keys well,
poorly, or not at all is a question that might influence the design of a
database intended to be used with an Access application. But the fact that
this benefit of a simple key attaches to a simgnle product (or a class of
products) ought to be made explicit. Instead, today's database experts are
telling neophytes that creating an autonumbered ID field is always a good
practice, regardless of the tools one is using, whether the database is
single user or multiuser, whether its embedded in a single application or
integrates the management of data across many applications, and so on.
There are consequences to permitting the same entity to be recorde twice in
a table, in two rows that differ only in the value of the ID field. I
believe Paul C outlined those difficulties in a separte response.
I've actually gotten beyond snobby attitudes towards Access. In my old age,
I decided to return and learn the product, and there are actually some
things about it that I like quite a lot. But respect should obscure the
fact that Ed Codd's proposal for a way to organize data in order to share
the data on an enterprise wide basis is fundamentally different from the
goals of the early versions of MS Access, which is to facilitate management
of data on a single user's personal computer. The difference between those
two goals is so profound that translating what works well in one setting
into the other ssetting can produce absurdities.
I hope I've established a tone here that permits us to explore big
differences without simply engaging in insult and invective. I look forward
to your reply.
Walter Mitty
"paul c" <toledob...@oohay.ac> wrote in message
news:0drPl.28343$PH1.24283@edtnps82...
Thanks for your reply. I can't tell you the number of times when I've read
one of your comments, agreed with it completely, and never bothered to write
a response.
In the case of stackoverflow, I don't feel quite comforatble dismissing
those who hold opinions that differe from mine as "semi literates". The
rest of what they write suggest that they have been building databases for
some time. At least, that's true for some of them.
I have to admit that I've got a firm opinion on the "tables should be
plural" controversy myself. Furthermore, my own practice is inconsistent
because when I use the "table_name AS table_alias" construct, the alias is
almost always singular, or an abbreviation of a singular, even though my
table names are almost always plural. I don't regard that opinion of mine
to be product driven, and I don't think it's much more than esthetic, and
ever so slightly mnemonic.
I agree that the big names aren't right all the time. The ones who really
deserve the respect they claim welcome somebody who questions the truth of
the wisdom they expound. If they are proven right, it just confirms the
value of their work. If they are proven wrong, everybody wins. The big
names who adopt the rhetoric of "because I'm an expert and you're not" have
gotten too big for their britches.
I also have seen cases where concocting a simple key to stand in for a very
complicated compound key makes sense. There's even an academic term for
that, at the conceptual level. It's called "reifying a relationship." The
confirmation number I get from Hertz rental, and that I use when I approach
the counter at my destination airport, is an example of such a key,
although it is obviously not hidden from the users. And the confirmation
now takes on the role of an entity, instead of a relationship,. But the
existence of such cases is a far cry from "always create an autosequnce
field, called ID, for every table".
I think the desire for a unique ID for every row in every table pursues the
same dream as accessing rows by address instead of by contents. And while
an ID column doesn't pin a row the way an address stored in other rows
would, it still reduces the relational model to the graph model in some way
that I can't describe.
My big bugaboo with the ID field is that if two rows have different IDs but
otherwise identical data, there tends to be a disconect between developers
and analysts as to whether this is harmful duplication or not. It gets even
worse if the two rows have different ID fields, identical key values, and
at least one non key value different. I've actually seen a case like this
in the field. In this case, the two rows are contradicting each other in
the anlayst's view, but not in the developer's view. There's something
wrong with that.
Again, thanks for a considered response.
Walter Mitty
"Troels Arvin" <tro...@arvin.dk> wrote in message
news:gukkfk$ohk$1...@news.net.uni-c.dk...
Thanks for your response. I think that both stackoverflow and Wikipedia
make it possible to confuse popularity with excellence.
Perhaps one just has to accept that some responses don't get the recognition
they deserve. By way of an analogy, in a democracy, sometimes the
electorate goofs.
But I think there's something more substantive going on here. I think that
the design center of database design has shifted from the support of an
enterprise need to manage and share data to an application's need for a
reliable persistent store. Most of the people voting are not DBA's or
database designers for an enterprise integrating database. They are
developers who are getting an application ready for sale. The measure of
"goodness" changes accordingly.
As far as efficiency goes, I used to make a dramatic difference between
inefficiencies that tacked an extra 10% onto a retrieval, and
inefficiencies that turned minutes of delay into hours of delay. I found
that keeping the design simple and sound, and keeping things flexible enough
to make fine tuning possible were much more important that squeezing every
last drop of performance out of every single design decision. Maybe today's
developers are like a horse owner trying to win the Kentucky derby, and
considering second place a "loss".
Speaking of efficiency, composite primary keys have almost no effect, if you
get the indexes right. A primary key on (StudentID, CourseID) is equivalent
to a key on (CourseID, StudentID). However an index on (StudentID,
CourseID) could have a very different performance impact than an index on
(CourseID, StudentID). Unfortunately, some people judge the performance
impact of a compound key based on the impact of the freebie index the DBMS
makes for you.
(I know, I know, "freebie" is the wrong word!).
I also remember a time when unique indexes used to perform much, much better
than indexes that permitted duplicates. This was a product specific
deficiency.
Thanks again.
Walter Mitty
"Brian Selzer" <br...@selzer-software.com> wrote in message
news:FjzPl.15750$jZ1....@flpi144.ffdc.sbc.com...
previous exchanges between you and me have arrived at such an impasse that
I'm not inclined to make the effort to reconcile your world view with mine.
I can see this response leading down the exact same trail that we've been
down so many times before.
Perhaps your reponse will provoke some useful discussion with other
participants. Thanks again.
Walter Mitty
"Roy Hann" <spec...@processed.almost.meat> wrote in message
news:VbWdnR8zmc-lHZPX...@pipex.net...
Thanks for your response. You are another regular whose responses often
provide me with valuable insight.
Based on the discussion in stackoverflow, I can neither confirm nor deny
your experience with CASCADE bringing people around. If you say that's been
your experience, then I'll believe you.
What I will say in response kind of generalizes your comment. Some design
decisions involve a trade off between ease of development now and ease of
data management later on. In that trade off, ease of development seems to
win every time. I can't prove this, but I think a lot of those developers
imagine a DBA to just a database baysitter, someone who makes sure the
database isbacked up, prevents hackers from storming the citadel, and does
little else. From my expeience in the 80s and 90s, that was only a small
part of the ways DBAs added value to data.
Thanks again.
Roy Hann
> My big bugaboo with the ID field is that if two rows have different IDs but
> otherwise identical data, there tends to be a disconect between developers
> and analysts as to whether this is harmful duplication or not.
There's no two ways about it. If the row would be duplicated if not for
the spurious distinction of a value that was generated only to ensure
distinction and *for no other reason*, it's logically still a duplicate
row.
By itself a duplicate is absurd but pretty harmless. The problem is
when one copy gets updated. Then you've got a contradiction and no
logical basis to prefer one over the other. That just can't ever be
right and it's not something that can ever be a matter of opinion or
taste.
--
Roy
Roy Hann
> Based on the discussion in stackoverflow, I can neither confirm nor deny
> your experience with CASCADE bringing people around. If you say that's been
> your experience, then I'll believe you.
Persuading a colleague who knows you and works with you, and persuading
some random Internet denizen who's goofing off to stir up trouble on
a forum, are two different challenges with different expected outcomes.
:-)
--
Roy
--CELKO--
states or countries. <<
Ever look up the DUNS? It is great for International use. Again, do
your Google Diligence, do your Wikipedia Diligence.
>> I'm quite happy with an artificial primary key (autonumber in Access) and unique
indexes in data fields [sic: columns are not fields] as appropriate.
<<
The Department of Redundancies Department, just like Monty Python :)
Multi-column keys are quite easy to use with a text editor. Assuming
that you don't name them all "id" in violation of basic data modeling
rules.
Tony Toews [MVP]
>>> Consider business name. Duplicate business names can exist in adjacent provinces,
>states or countries. <<
>
>Ever look up the DUNS? It is great for International use. Again, do
>your Google Diligence, do your Wikipedia Diligence.
And I'm sure there are lots of small businesses that don't have a DUNS.
>>> I'm quite happy with an artificial primary key (autonumber in Access) and unique
>indexes in data fields [sic: columns are not fields] as appropriate.
><<
>The Department of Redundancies Department, just like Monty Python :)
>Multi-column keys are quite easy to use with a text editor.
So you're going to build an app with 100 forms using a text editor? I'm not.
>Assuming
>that you don't name them all "id" in violation of basic data modeling
>rules.
No, I like usingTony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm
Which you have stated quite a while ago you don't like. Oh well.
cim...@hotmail.com
now and ease of
data management later on>
fundamentals of database management can only make development easier
to create and administer on a continuous basis. Even on non
relational systems such as SQL DBMS's.
Tony Toews [MVP]
>The so-called 'natural' key for all the airwaybills in a ULD on a
>typical airline flight segment is about seven or eight attributes. When
>the cargo master, at the last minute, offloads a container or two onto
>the tarmac, you don't want a ponderous logic to un-assign it and put it
>on the plane at the next gate. You need a form of indirection, for that
>matter the same is needed in any robotic application such as a freight
>warehouse.
Preumably though there are bar codes on the cargo containers and a simple form where
the cargo master can remove selected containers from the airplane.
A welding shop client that empoyed hundreds of welders built and assembled very
complex piping assemblies for refineries, power plants and oil sands plants. These
are designed to, usually, fit on a 50' flat bed trailer. The client number could be
in excess of 20 or 25 characters. The internal number they used went from 1 to
whatever. It in turn was prefixed with a job number which started at 1 and went to
whatever. The system printed multiple weather proof tag with the internal number as
well as the bar coded long client number.
(Occasionally they would have to rebuild a particular item. The gravel pad at one
client where these are stored is about a mile square. Well, if the plant has a
large expansion, and there's a lot of snow that winter, you can't find the
assemblies. Until the expansion is finished a year or two later and you're
looking at the excess assemblies which are laying on the gravel.. And the folks at
the plant getting paid $25 and $30 an hour love being told to go through all the
items on this gravel pad looking for particular assemblies. A great way to spend a
shift rather than hauling stuff around or whatever.)
paul c
...
> In the case of stackoverflow, I don't feel quite comforatble dismissing
> those who hold opinions that differe from mine as "semi literates". The
> rest of what they write suggest that they have been building databases for
> some time. At least, that's true for some of them.
There is really so little that's important to read and yet the vast
majority of practioners have not even done that, let alone spent any
time on the implications and the holes in the original theory. Maybe
one in fifty can discuss fundamentals without lapsing into jargon or
confusing theory with implementation. Academia is the same way, perhaps
worse, preferring to write papers before they understand their subject.
The big corporations have really been of no help at all. It was a
fluke that Codd got anywhere in the first place, at the time he was
being paid to study IMS and much of his time, energy and health was
dissipated fighting the corporate establishment and various commercial
sycophants and vested interests from all corners. I'd even say Date has
wasted many words for the similar reason.
The very noisy db literacy lowest-common-denominator crowd have been
distracting progress for most of my life, at times I've even been among
them, trouble is that most of them don't know they are in that segment
and don't understand even when they are told. It is a truism that the
stupid and the arrogant usually are incapable of recognizing their own
behaviour. That is why politeness has nothing to do with general
improvement. Much of all this is true of other fields too, but this one
has the advantage of very low cost for truly radical implementations,
one or two of which might wake people up, which is the only way out I
can see even if it's a long-shot. The chief value of the semi-literates
is in their exceptional ability to expose problems , although what they
call a problem is often not a real problem, occasionally they unearth a
nugget. When they occasionally tout a solution, it is generally an
accident.
Michael Schuerig
> What I don't get is whether I've understood something that today's
> trendsetters never learned or whether they are taking into
> consideration matters that I never considered important, back in the
> day.
I suggest you look closely at the URLs displayed in your browser when
visiting database-backed websites. You'll notice that numeric IDs are
ubiquitous. I think that is already a large part of the answer to the
question why artificial keys have become prevalent in certain quarters:
Meaningless numbers are just very easy to stick into URLs.
Michael
--
Michael Schuerig
mailto:mic...@schuerig.de
http://www.schuerig.de/michael/
Brian Selzer
<cim...@hotmail.com> wrote in message
news:1e18db80-b98e-48bf...@21g2000vbk.googlegroups.com...
I disagree that it is only poor design decisions that impose such tradeoffs.
You may be faced with the dilemma of biasing a database toward a particular
application, which just happens to be the application you're responsible
for, or expending extra man-hours making your application conform to an
unbiased database.
paul c
...
> the cargo master can remove selected containers from the airplane.
>
> A welding shop client that empoyed hundreds of welders built and assembled very
> complex piping assemblies for refineries, power plants and oil sands plants. These
> are designed to, usually, fit on a 50' flat bed trailer. The client number could be
> in excess of 20 or 25 characters. The internal number they used went from 1 to
> whatever. It in turn was prefixed with a job number which started at 1 and went to
> whatever. The system printed multiple weather proof tag with the internal number as
> well as the bar coded long client number.
>
> (Occasionally they would have to rebuild a particular item. The gravel pad at one
> client where these are stored is about a mile square. Well, if the plant has a
> large expansion, and there's a lot of snow that winter, you can't find the
> assemblies. Until the expansion is finished a year or two later and you're
> looking at the excess assemblies which are laying on the gravel.. And the folks at
> the plant getting paid $25 and $30 an hour love being told to go through all the
> items on this gravel pad looking for particular assemblies. A great way to spend a
> shift rather than hauling stuff around or whatever.)
The people who make international freight regulations remind me of the
Canadian food inspectors from thirty years ago who assigned government
grades for the mandated labels of canned vegetables - Canada Fancy,
Choice or Standard. At the unloading dock, they would put a pea in
their mouth and decide on the spot. Yanks had a gizmo called a
'tenderometer' whereas much in Canada depended on how hung-over the
inspector was. I used to know a bit of what went on at the IATA
so-called standards meetings and from their results I conclude the
meetings were much similar to those of the SQL standards committee,
basically add every arcane, legacy, idiotic feature that the sponsoring
companies had in their products or systems..
From what I saw fifteen or so years ago I think it was and probably
still is generally true that the 'business experts' and other 'users' in
the freight, customs, forwarding, warehousing and transportation
businesses are not capable of deciding all requirements because most of
them are stuck in the rut of their own past practices. When a designer
is exposed to this, a moment's thought should be enough to see that the
different parties operate from distinct concepts. Faced with this, a db
designer must ask questions like "what is a shipment?", "what is a
location?", as far as the system, not the application, is concerned, and
these questions need to be asked not to the individual parties but in
the context of everything the system must do. Some abstract key like
'shipment id' is sure to result, but the waybill numbers, flight segment
keys, container ids, order ids, et cetera, et cetera must also persist.
If what I remember resembles real life, what does the db designer do in
the face of enterprise experts who don't get it? There is only one way
to do it and that is to go over the heads of the experts. That is a
good way to risk contracts, but from what I saw the safest way to do it
was start from the very top. However, the technical lingo doesn't get
you anywhere in front of the board of directors. In my opinion,
practical db design for large apps requires people who have multiple
talents, enough gumption to make their point, but no more. An ignorant
CEO might palm you off to numbskulls or you might get lucky and meet an
underling who is actually running the business if not in name. Either
way it buys you time to either find another job or another contract. Or
one could just play it safe, go with the flow and preserve one's income
indefinitely, but of course no such person would be posting on c.d.t!
paul c
> paul c <toledob...@oohay.ac> wrote:
...
> Preumably though there are bar codes on the cargo containers and a simple form where
> the cargo master can remove selected containers from the airplane.
>
> A welding shop client that empoyed hundreds of welders built and assembled very
> complex piping assemblies for refineries, power plants and oil sands plants. These
> are designed to, usually, fit on a 50' flat bed trailer. The client number could be
> in excess of 20 or 25 characters. The internal number they used went from 1 to
> whatever. It in turn was prefixed with a job number which started at 1 and went to
> whatever. The system printed multiple weather proof tag with the internal number as
> well as the bar coded long client number.
>
> (Occasionally they would have to rebuild a particular item. The gravel pad at one
> client where these are stored is about a mile square. Well, if the plant has a
> large expansion, and there's a lot of snow that winter, you can't find the
> assemblies. Until the expansion is finished a year or two later and you're
> looking at the excess assemblies which are laying on the gravel.. And the folks at
> the plant getting paid $25 and $30 an hour love being told to go through all the
> items on this gravel pad looking for particular assemblies. A great way to spend a
> shift rather than hauling stuff around or whatever.)
i also meant to add that the above shows the beginnings of a case study
that might be useful in general, although it's not clear whether getting
rid of the union is a system requirement.
paul c
...
> I have to admit that I've got a firm opinion on the "tables should be
> plural" controversy myself. Furthermore, my own practice is inconsistent
> because when I use the "table_name AS table_alias" construct, the alias is
> almost always singular, or an abbreviation of a singular, even though my
> table names are almost always plural. I don't regard that opinion of mine
> to be product driven, and I don't think it's much more than esthetic, and
> ever so slightly mnemonic.
That gibes entirely with algebraic basics, and, I presume, calculus
basics, where such names are nothing more than syntactical
place-holders. Pathological mystics often claim otherwise!
Tony Toews [MVP]
>> (Occasionally they would have to rebuild a particular item. The gravel pad at one
>> client where these are stored is about a mile square. Well, if the plant has a
>> large expansion, and there's a lot of snow that winter, you can't find the
>> assemblies. Until the expansion is finished a year or two later and you're
>> looking at the excess assemblies which are laying on the gravel.. And the folks at
>> the plant getting paid $25 and $30 an hour love being told to go through all the
>> items on this gravel pad looking for particular assemblies. A great way to spend a
>> shift rather than hauling stuff around or whatever.)
>
>i also meant to add that the above shows the beginnings of a case study
>that might be useful in general, although it's not clear whether getting
>rid of the union is a system requirement.
Now this was about eight years ago so we didn't have the hand held devices with
convenient GPSs that are presumably available these days. It was quite a bit more of
a pain to assemble such a ruggedized device. I did a bit of research, not a lot,
and couldn't find anything other than external wire attached GPSs.
Nevertheless it was my suggestion to give the guys a device which had a hand held
computer of some sort with a bar code reader and a GPS. Every month or two send them
out scanning each bar code they could find. If they scanned the same item with the
bar code at each end well who cares. Then come back to the office, download the data
and now you know exactly where your inventory is.
paul c
...
> My big bugaboo with the ID field is that if two rows have different IDs but
> otherwise identical data, there tends to be a disconect between developers
> and analysts as to whether this is harmful duplication or not. It gets even
if it suits the app' and it has been explained to them then those people
should be fired or at least re-assigned. it makes absolutely no sense
to try to 'work' with people who don't get it. That is the worst kind
of work. The really thoughtful ones who object would have already come
up with a better design. The fact that so many organizations persist in
denying this is a tribute to the fallacious 'personnel' corporate
management philosophy of the 1960's. Most people who are thirty years
younger than i am don't understand that it takes two generations to
forget the mistakes. The db field today is subject to the problem
because it is younger than that and corporate-dominated.
paul c
> paul c <toledob...@oohay.ac> wrote:
>
>>> (Occasionally they would have to rebuild a particular item. The gravel pad at one
>>> client where these are stored is about a mile square. Well, if the plant has a
>>> large expansion, and there's a lot of snow that winter, you can't find the
>>> assemblies. Until the expansion is finished a year or two later and you're
>>> looking at the excess assemblies which are laying on the gravel.. And the folks at
>>> the plant getting paid $25 and $30 an hour love being told to go through all the
>>> items on this gravel pad looking for particular assemblies. A great way to spend a
>>> shift rather than hauling stuff around or whatever.)
>> i also meant to add that the above shows the beginnings of a case study
>> that might be useful in general, although it's not clear whether getting
>> rid of the union is a system requirement.
>
> Now this was about eight years ago so we didn't have the hand held devices with
> convenient GPSs that are presumably available these days. It was quite a bit more of
> a pain to assemble such a ruggedized device. I did a bit of research, not a lot,
> and couldn't find anything other than external wire attached GPSs.
>
> Nevertheless it was my suggestion to give the guys a device which had a hand held
> computer of some sort with a bar code reader and a GPS. Every month or two send them
> out scanning each bar code they could find. If they scanned the same item with the
> bar code at each end well who cares. Then come back to the office, download the data
> and now you know exactly where your inventory is.
>
> Tony
Now you are diverging, imho, magic wands and such 'devices' being the
system equivalent of what Edward de Bono might have called porridge
words. That stuff can never outlive a useful concept, call it the techno
trap if you want., .
Bob Badour
The fundamental problem Tony had was nobody ever put bin numbers on the
inventory bins. Solving the problem is as simple as adding a logical
identifier, any identifier, to largish areas of the inventory field.
paul c
most db experts can't say what the fundamental problem is when faced
with multiple systems. I don't like to argue with Bob B because i
usually lose the argument, but I don't know what inventory bins have to
do with the so-called unit load devices on 'planes. There is so much
bureaucratic fooferah to do with those it makes most heads spin, which I
could probably prove, having known a few airline employees. One outfit
did take inventory of the ULD's themselves, not their contents but their
ACP-based system would only allow that enquiry every Thursday at UDT+1
to UDT+1 plus thirty minutes, sorry I don';t know how to type UTC.
When you get into big inter-linked systems, nothing is simple, except
usually the experts who are paying you. you must abstract the parts to
make any sense of the whole. As much of a fan as i am of join as
fundamentalk techinique, there is more. most of the people i used to
know used join to simulate hierarchies. I don't know if it's still true
but most mechanized systems for 'bins' about fifteen years ago mostly
ignored bin identifiers, instead using imaginary locations which were
mapped from time to time to real locations. This was the only distinct
hardware could communicate. One European outfit fell into the trap of
assigning a unique db message to every possible event. It was full of
holes, eg., totally at a loss to decide what to do if a plane
originating in Newark heading for Zurich was re-routed because of a
storm. The inherent db solution was to eliminate the muriad messages
and replace them with insert, delete or even update to common 'tables'.
But of course, that implied a loss of 'jobs for the boys'!
Bob Badour
Was he not talking about a large gravel field where inventory was
stored? That inventory field was never divided into any sub-unit, which
I call an inventory bin. Regardless whether one uses GPS or spray paint,
if one doesn't put identifiers on anything, one cannot identify them.
That's why warehouses use bin numbers. His problem was the equivalent of
a warehouse full of stuff with no intenal subdivision and no way of
identifying where in the warehouse things were put.
That's a very basic problem and why men invented natural keys long
before the first computer was ever built.
Walter Mitty
That's data analysis. Back when I was doing data analysis, you had to ask
the same question to a handful of people, and often the same question in
different wordings to the same person again and again. I met someone who
was better at data analysis that I was, and later found out what the user
community thought of him: "He's not very bright, but at least he's
thorough". They didn't realize that he was actually very bright.
Good coders making coding look easy. Good analysts don't make analysis look
easy.
>
>
> If what I remember resembles real life, what does the db designer do in
> the face of enterprise experts who don't get it? There is only one way to
> do it and that is to go over the heads of the experts. That is a good way
> to risk contracts, but from what I saw the safest way to do it was start
> from the very top. However, the technical lingo doesn't get you anywhere
> in front of the board of directors. In my opinion, practical db design
> for large apps requires people who have multiple talents, enough gumption
> to make their point, but no more. An ignorant CEO might palm you off to
> numbskulls or you might get lucky and meet an underling who is actually
> running the business if not in name. Either way it buys you time to
> either find another job or another contract. Or one could just play it
> safe, go with the flow and preserve one's income indefinitely, but of
> course no such person would be posting on c.d.t!
It depends on who you mean by "the experts". Back in the day, the people I
really relied on were the "subject matter experts". I'm not talking about
IT experts here. I'm talking about the people who REALLY understand the
data, in the context of making the data do useful work. Such people often
do not know squat about database design. Why should they? The key to
picking their brain is to ask them about the data, not about the logical
structure of the data. Once you understand how the data really works, you
can switch from analysis to design.
At design time, you need to trust what you know about logical database
design (and also physical database design) and not rely on the wisdom of the
subject matter experts. Some good designers switch prematurely from
analysis to design, thinking they have grasped how the data really works
when they have not.
Bad design can ruin good analysis, but good design can't rescue bad
analysis. Analysis paralysis is a pitfall. But another pitfall is breezing
through analysis with just a hazy idea of what the system goals are. And
you are right that it is the system perspective, not the app perspective
that matters. I like the phrase "in the context of everything the system
must do." And I'm taking the system to mean a business system, that
contains people doing job functions, as well as machines carrying out
programmed actions. The people who understand data at a business system
level are rare.
Thanks for some good points.
Walter Mitty
"Bob Badour" <bba...@pei.sympatico.ca> wrote in message
news:4a0fb2e3$0$23741$9a56...@news.aliant.net...
...
> That's why warehouses use bin numbers. His problem was the equivalent of a
> warehouse full of stuff with no intenal subdivision and no way of
> identifying where in the warehouse things were put.
>
> That's a very basic problem and why men invented natural keys long before
> the first computer was ever built.
This captures the essence of the issue I raised very concisely.
One nitpick. A GPS device gives you a measure of where something is, not a
key to its location, unless I misunderstand how GPS works.
Using measures in place of keys is tricky, even though it looks simple at
first.
cim...@hotmail.com
> I disagree that it is only poor design decisions that impose such tradeoffs.
> You may be faced with the dilemma of biasing a database toward a particular
> application, which just happens to be the application you're responsible
> for, or expending extra man-hours making your application conform to an
> unbiased database.
--CELKO--
You can get one for free. In the commercial world, this is like those
website that tell people to get a free email when they need a customer
id. I had to get one as an individual when I taught classes at
DELL.
>> So you're going to build an app with 100 forms using a text editor? I'm not. <<
I don't understand that comment. Since I am the database guy, I don't
build application code at all! My job is to assure that
1) the database is accurate and current internally (hence constraints
and business rules are my job)
2) able to communicate with external data sources (hence the need for
industry standards. subscriptions to external data sources)
3) able to present data to the front end (report server, analytic
server, host programs regardless of language)
--CELKO--
Ever work with DBs that use hashing instead of tree indexes? Longer
keys are better because there is less chance of a hash collision.
There is also a lot of work being done on minimal perfect hashing
right now. This is going to be very important for VLDB because
unlike indexes, a hash requires only one probe.
Bob Badour
> Don't mind Celko, his a "cowboy dba".
While I am no fan of Joe's, I find your contribution below even less
coherent than his random error-filled crap.
> The fact is that you may need a surrogate key in a real world
> application (not theoretical).
What exactly is a theoretical application? How exactly have you confused
it with application of theory? The former is nonsense and the latter is
sense.
cim...@hotmail.com
> natural keys and surrogates, your application is going to be a mess.
> I highly recommend going with a surrogate like an identity column. If
> you ask Celko for a standard forum DDL where users have the ability to
> change the topic subject, he will never respond because he doesn't
> know how (without the use of surrogates).
The suggestion of using *only* identity columns is sufficient to
distinguish tuples is even worse than the nonsense CELKO usually pours
down on people.
Oh yeah btw, we already heard the argument of *leave that theoretical
crap out* before....
Tony Toews [MVP]
>The fundamental problem Tony had was nobody ever put bin numbers on the
>inventory bins. Solving the problem is as simple as adding a logical
>identifier, any identifier, to largish areas of the inventory field.
Go for it. Try putting bin numbers on a gravel pad a square mile in size. And
actually they do have lanes and such.
The problem is that folks unload the trucks in certain places and then somehow screw
up entering where it is. Or the wrong piece gets moved and then put back somewhere
else. Or other human reasons.
When you're dealing with 5,000 and 10,000 humans assembling a huge jigzaw puzzle
called a refinery, upgrade or oil sands plant stuff happens.
Tony Toews [MVP]
>One nitpick. A GPS device gives you a measure of where something is, not a
>key to its location, unless I misunderstand how GPS works.
>Using measures in place of keys is tricky, even though it looks simple at
>first.
"give the guys a device which had a hand held computer of some sort with a bar code
reader and a GPS. "
So you have three pieces of data, bar code number identifying the peice, location and
date/time.
Tony Toews [MVP]
>>> And I'm sure there are lots of small businesses that don't have a DUNS. <<
>
>You can get one for free. In the commercial world, this is like those
>website that tell people to get a free email when they need a customer
>id. I had to get one as an individual when I taught classes at
>DELL.
And it's not very common up here in Canada. No one has ever asked me for my DUNS.
>>> So you're going to build an app with 100 forms using a text editor? � I'm not. <<
>
>I don't understand that comment. Since I am the database guy, I don't
>build application code at all!
Multi column keys are significantly more work to use when building an app. At least
they certainly are in Access.
>My job is to assure that
>
>1) the database is accurate and current internally (hence constraints
>and business rules are my job)
>2) able to communicate with external data sources (hence the need for
>industry standards. subscriptions to external data sources)
>3) able to present data to the front end (report server, analytic
>server, host programs regardless of language)
Whereas I do everything. Find out what the users want, design the tables, fields
(columns), indexes and relationships and create the application.
Brian Selzer
<cim...@hotmail.com> wrote in message
news:046f52ca-325f-491f...@h23g2000vbc.googlegroups.com...
No. They're not.
paul c
To most people, "natural" means "physical" and ends up being quite
unnatural for some purposes.. When inventory or anything else "moves",
sometimes "logical" locations are more convenient, allowing the
replacement of one row to indicate the transfer or re-assignment of many
"things".
Gene Wirchenko
>--CELKO-- <jcel...@earthlink.net> wrote:
>
>>>> And I'm sure there are lots of small businesses that don't have a DUNS. <<
>>
>>You can get one for free. In the commercial world, this is like those
>>website that tell people to get a free email when they need a customer
>>id. I had to get one as an individual when I taught classes at
>>DELL.
>
>And it's not very common up here in Canada. No one has ever asked me for my DUNS.
When I contracted for Eaton's, I had to get a DUNS. It was very
easy to get.
[snip]
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
Roy Hann
> To most people, "natural" means "physical"
Really?! I can't possibly say you are wrong about that because I
wouldn't know, but you do surprise me.
> and ends up being quite
> unnatural for some purposes.. When inventory or anything else "moves",
> sometimes "logical" locations are more convenient, allowing the
> replacement of one row to indicate the transfer or re-assignment of many
> "things".
That seems very implausible in the real world. That is a logical model
in which if you move one thing, you must--infallibly--move all the
other things that were there too. That is, it is a world in which it is
impossible not to also move all the other things. Such situations do
exist, I grant you, but they are the exception not the rule. But no
one here (AFAIK) has ever said synthetic/surrogate keys are never
useful.
--
Roy
Bob Badour
And no synthetic key will solve the problem that someone was stupid
enough to fill a warehouse with no internal subdivision and no way to
identify where in the warehouse inventory was put. (In the example
cited, the warehouse being a huge gravel field.)
paul c
Every so often some crackpot claims to have discovered the perfect hash
but bigger keys usually need bigger buckets to reduce collisions. It
reminds me of the old joke about the guy who kept re-running his
compression program in hopes of reducing the file's size to one byte.
Hash indexes are okay for some special purposes, such as an atm
withdrawal system but they are more common in various interpreters.
Same goes for column-based stores, not to mention the trans-relational
"model". Meanwhile, cpu design has been stagnating for years, either
improving self-created problems or being almost totally reliant on
bio-feedback, optimizing patterns that are found in typical software
when the typical software uses only the features of the previous
hardware generation, then the cycle repeats with the software designers
doing the same thing in reverse. There's still no consumer machine with
a double-ended stack which is an obvious requirement with so many
systems being interpretive now, the Intel segmented memory which started
out as a hack for backwards compatibility would be tremendously useful
on today's big memories, as ironic as that may sound. Windows and
Linux didn't hide segments from the developer and there is no mainstream
cpu with any kind of memory association feature beyond traditional
physical addressing.
Walter Mitty
> To most people, "natural" means "physical"
That's not true in my experience. Most of the people I've dealt with tend
to think a vehicle's VIN is a natural key, and a bank account's account
number is a natural key. both of those entities are articificial to begin
with, and therefore all their attributes are artificial. Still, your mileage
may vary, if you've dealt with different people than I have.
Maybe the distinction between "natural" and "synthetic" would be clearer if
we changed the labels. How about "external" versus "internal"? This still
begs the question of whether I mean internal to the database or internal to
the application and the database. But maybe it's a step in the right
direction. How about "conceptual key" versus "logical key"?
paul c
If management is too stupid to sub-divide the yard, that doesn't prevent
a db from assigning a value that indicates pieces are adjacent, based on
sequence of initial movement, or whatever. Just like nothing prevents a
system from moving all employees in a department to a different
department by replacing a single row.
paul c
> "paul c" <toledob...@oohay.ac> wrote in message
> news:0KfQl.28513$PH1.15846@edtnps82...
>> To most people, "natural" means "physical"
>
> That's not true in my experience. Most of the people I've dealt with tend
> to think a vehicle's VIN is a natural key, and a bank account's account
> number is a natural key. both of those entities are articificial to begin
> with, and therefore all their attributes are artificial. Still, your mileage
> may vary, if you've dealt with different people than I have.
Most people I know don't abstract at all. What's natural to them
consists only of what they can see or have seen sometime, on a piece of
paper or somesuch.
Walter Mitty
> --CELKO-- wrote:
>>>> Speaking of efficiency, composite primary keys have almost no effect,
>>>> if you get the indexes right. <<
>>
>> Ever work with DBs that use hashing instead of tree indexes? Longer
>> keys are better because there is less chance of a hash collision.
>> There is also a lot of work being done on minimal perfect hashing
>> right now. This is going to be very important for VLDB because
>> unlike indexes, a hash requires only one probe.
>
> Every so often some crackpot claims to have discovered the perfect hash
> but bigger keys usually need bigger buckets to reduce collisions. It
> reminds me of the old joke about the guy who kept re-running his
> compression program in hopes of reducing the file's size to one byte.
Ha, ha!
My seven most used files are each one byte long, but you should see how
long the other 1000 files are!
I can't help but wishing the Claude Shannon's work was not more well
understood among software engineers.
paul c
...
>
> My seven most used files are each one byte long, but you should see how
> long the other 1000 files are!
>
> I can't help but wishing the Claude Shannon's work was not more well
> understood among software engineers.
>
Once, just to prove a point, I made a packaging utility to replace the
static portions of a bunch of system tables in an oddball product with
code. From the point of view of end-developers who used this system to
produce apps, that stuff was mostly irrelevant and pure overhead, which
was measurable for some apps. With every release, the utility would
generate a perfect hash, at the expense of using more storage. This was
practical because there were only several hundred keys and at that time,
memory latency wasn't the big factor it is today, where a 2GHz cpu might
be crippled by 500Mhz memory. A typical app could indirect or
"introspect" the "catalog" many times for each "key" and it was silly to
invoke caching for that purpose but behaviour needed to be identical to
"native" or un-optimized behaviour. Such a method of "compiling" tables
today might need to cater to latency more than I needed to then.
cim...@hotmail.com
> If management is too stupid to sub-divide the yard, that doesn't prevent
> a db from assigning a value that indicates pieces are adjacent, based on
> sequence of initial movement, or whatever. Just like nothing prevents a
> system from moving all employees in a department to a different
> department by replacing a single row.
databases when they are simply *adjacent* data bag code dumpers .
Call it the organizational counterpart analogy of your last assertion.
Regards...
Bob Badour
> --CELKO-- wrote:
>
>>>> Speaking of efficiency, composite primary keys have almost no
>>>> effect, if you get the indexes right. <<
>>
>> Ever work with DBs that use hashing instead of tree indexes? Longer
>> keys are better because there is less chance of a hash collision.
>> There is also a lot of work being done on minimal perfect hashing
>> right now. This is going to be very important for VLDB because
>> unlike indexes, a hash requires only one probe.
That's not exactly true. Most hash indexes are radix indexes. (Just
another of the brilliant observations we can thank Ron Fagin for.)
A true hash has the property that performance degrades when it becomes full.
> Every so often some crackpot claims to have discovered the perfect hash
> but bigger keys usually need bigger buckets to reduce collisions. It
> reminds me of the old joke about the guy who kept re-running his
> compression program in hopes of reducing the file's size to one byte.
> Hash indexes are okay for some special purposes, such as an atm
> withdrawal system but they are more common in various interpreters. Same
> goes for column-based stores, not to mention the trans-relational
> "model". Meanwhile, cpu design has been stagnating for years, either
> improving self-created problems or being almost totally reliant on
> bio-feedback, optimizing patterns that are found in typical software
> when the typical software uses only the features of the previous
> hardware generation, then the cycle repeats with the software designers
> doing the same thing in reverse. There's still no consumer machine with
> a double-ended stack which is an obvious requirement with so many
> systems being interpretive now, the Intel segmented memory which started
> out as a hack for backwards compatibility would be tremendously useful
> on today's big memories, as ironic as that may sound. Windows and
> Linux didn't hide segments from the developer and there is no mainstream
> cpu with any kind of memory association feature beyond traditional
> physical addressing.
Beyond a double-ended stack and segmented memory, what else do you
recommend?
paul c
...
> Beyond a double-ended stack and segmented memory, what else do you
> recommend?
Those were two of my pet peeves from years ago, can't remember the rest
of my long list from those days, let it lapse out of frustration, but
basically I was a fan of complex instructions. The OS developers bugged
me just as much, twenty years ago every big dbms development shop was
re-inventing or working around mis-conceived OS features, they probably
still are.
paul c
> Bob Badour wrote:
> ...
>> Beyond a double-ended stack and segmented memory, what else do you
>> recommend?
>
> Those were two of my pet peeves from years ago, can't remember the rest
Oh, just remembered another one - fixed-point decimal arithmetic!
Bob Badour
What do you need that for?
Tony Toews [MVP]
>And no synthetic key will solve the problem that someone was stupid
>enough to fill a warehouse with no internal subdivision and no way to
>identify where in the warehouse inventory was put. (In the example
>cited, the warehouse being a huge gravel field.)
No, not having internal subdivisions was not the problem. People errors were the
problem. Which happens in every project large or small. Trouble is these are among
the larger projects. One job site at which I worked had 15,000 construction people
on site building the plant.
paul c
To get the same answer as the lawyer with his amortization tables.
paul c
> Bob Badour <bba...@pei.sympatico.ca> wrote:
>
>> And no synthetic key will solve the problem that someone was stupid
>> enough to fill a warehouse with no internal subdivision and no way to
>> identify where in the warehouse inventory was put. (In the example
>> cited, the warehouse being a huge gravel field.)
>
> No, not having internal subdivisions was not the problem. People errors were the
> problem. Which happens in every project large or small. Trouble is these are among
> the larger projects. One job site at which I worked had 15,000 construction people
> on site building the plant.
>
> Tony
Just two questions for no particular reason: 1) was that on the coast?
2) did you use Access?
Bob Badour
Integers are integers no matter the base.
paul c
Bob, how come you're not watching the hockey game? Not the best, I
admit, but it;s all we have to watch today. Besides, what do integers
have to with decimals?
Bob Badour
Um, because I have absolutely no interest in hockey? Might there be any
other reason?
> Besides, what do integers
> have to with decimals?
Everything. Fixed point decimals are always an integral multiple of
something.
Tony Toews [MVP]
>>> And no synthetic key will solve the problem that someone was stupid
>>> enough to fill a warehouse with no internal subdivision and no way to
>>> identify where in the warehouse inventory was put. (In the example
>>> cited, the warehouse being a huge gravel field.)
>>
>> No, not having internal subdivisions was not the problem. People errors were the
>> problem. Which happens in every project large or small. Trouble is these are among
>> the larger projects. One job site at which I worked had 15,000 construction people
>> on site building the plant.
>>
>> Tony
>
>Just two questions for no particular reason: 1) was that on the coast?
No, this was in Alberta, Canada.
>2) did you use Access?
Yes. That particular app mentioned in the last sentence was tracking the progress of
300 electricians per 12 hour shift so well within Access's capabilities. Once the
job was done the app and data was archived and likely never used again.
paul c
Okay, I see we're verging back into theory.
Bob Badour
Did we ever leave?
David BL
>
> Most people I know don't abstract at all.
Do you work in a morgue?
paul c
I'm staring to think so.
paul c
See immediately previous reply.
David BL
LOL. Anyway, my point was that all conscious humans, even
particularly challenged ones utilise abstractions in one form or
another.
I recall reading about an experiment on mice that demonstrated that
they could at some level understand the abstract concept of numbers by
counting the objects presented on a card in order to avoid a nasty
electric shock by conditionally jumping over a barrier. To be judged
successful the mouse had to be able to count objects it had never seen
before. It was claimed that mice can count up to 3.
Roy Hann
> To be judged
> successful the mouse had to be able to count objects it had never seen
> before. It was claimed that mice can count up to 3.
Count, or classify at a glance into none, a bit, quite a bit, lots? I
am wary of calling that "counting" and I'd want to know more
about the design of the experiment to be persuaded that any counting
was going on.
But perhaps I am being unromantical. My disbelief in psychics, deities,
wonder-foods, morphic resonance, Gaia, etc. has earned me that
accusation before now. :-)
--
Roy
Walter Mitty
"David BL" <dav...@iinet.net.au> wrote in message
news:368e6bbc-8b2d-465a...@i28g2000prd.googlegroups.com...
In a different experiment, they showed that crows can count up to 7.
Gene Wirchenko
>"David BL" <dav...@iinet.net.au> wrote in message
[snip]
>> I recall reading about an experiment on mice that demonstrated that
>> they could at some level understand the abstract concept of numbers by
>> counting the objects presented on a card in order to avoid a nasty
>> electric shock by conditionally jumping over a barrier. To be judged
>> successful the mouse had to be able to count objects it had never seen
>> before. It was claimed that mice can count up to 3.
>
>In a different experiment, they showed that crows can count up to 7.
2-bit unsigned and 3-bit unsigned.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
paul c
...
>
> In a different experiment, they showed that crows can count up to 7.
>
Thousands of human were counting for thousands of years before it
occurred to one of them to try to count to zero.
Gene Wirchenko
--CELKO--
You might want to look at the research by those "crackpots" that keep
getting published in ACM and IEEE journals. Not my idea of
crackpots. Just as a starter try "Practical minimal perfect hash
functions for large databases" (http://portal.acm.org/citation.cfm?
id=129623&dl=).
The bucket size is not the problem. Finding a hashing function that
can run fast enough and be minimal and perfect has been the
challenge. Cheaper storage and faster processors have made this
approach practical. Have you ever worked with Teradata?
>> Same goes for column-based stores, not to mention the trans-relational "model". Meanwhile, CPU design has been stagnating for years, ... <<
Not a big fan of the multi-core chips that are going to be standard in
a few years? Having seen WX2 and SAND, I like the vertical database
architectures. I have not played with Vertica yet. It works because
it can be compressed and made parallel
I would not fault the CPUs, but the lack of functional programming
languages (Erlang, Haskell, F#, etc) for operating systems that could
take advantage of the newer hardware's parallelism.
--CELKO--
To easily conform to EU and GAAP rules for currency calculations at
the hardware level. Most SQL is still used commercially and it is
nice if you can work with COBOL, the most popular programming language
on Earth.
toby
> "Walter Mitty" <wami...@verizon.net> wrote:
> >"David BL" <davi...@iinet.net.au> wrote in message
>
> [snip]
>
> >> I recall reading about an experiment on mice that demonstrated that
> >> they could at some level understand the abstract concept of numbers by
> >> counting the objects presented on a card in order to avoid a nasty
> >> electric shock by conditionally jumping over a barrier. To be judged
> >> successful the mouse had to be able to count objects it had never seen
> >> before. It was claimed that mice can count up to 3.
>
> >In a different experiment, they showed that crows can count up to 7.
>
> 2-bit unsigned and 3-bit unsigned.
Then there's the tern-ary number system...
toby
You can find plenty of VAXes on ebay.
Look, here's one: http://cgi.ebay.ca/ws/eBayISAPI.dll?ViewItem&item=150330289821
Jonathan Leffler
> I'm confused about some recent trends in database design.
>
> When I learned databases, about a quarter of a century ago, the preferred
> practice was to use natural keys to refer to individual table rows, unless
> there's some real good reason to go with an artificial key. I've run into a
> few cases where I chose to use an artificial key, but most of the time I've
> used a natural key. [...]
Somebody mentioned in an answer on StackOverflow that (Ruby on) Rails
requires all tables to contain a column called 'id' (and, by
implication, that column should be an 'identity' column or
auto-generated value). (I've not verified that this is so.)
I've also seen numerous suggestions that modern web-ish environments
(Rails, etc) get confused by composite keys.
In all cases, the problem seems to be a lack of understanding.
--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/
publictimestamp.org/ptb/PTB-6210 tiger128 2009-05-21 06:00:05
967144408DF12A71FE74F813A530EC74
Michael Schuerig
> Walter Mitty wrote:
>> I'm confused about some recent trends in database design.
>>
>> When I learned databases, about a quarter of a century ago, the
>> preferred practice was to use natural keys to refer to individual
>> table rows, unless
>> there's some real good reason to go with an artificial key. I've run
>> into a few cases where I chose to use an artificial key, but most of
>> the time I've
>> used a natural key. [...]
>
> Somebody mentioned in an answer on StackOverflow that (Ruby on) Rails
> requires all tables to contain a column called 'id' (and, by
> implication, that column should be an 'identity' column or
> auto-generated value). (I've not verified that this is so.)
Yes, this is true with Rails out of the box, but there is an extension
available at
http://compositekeys.rubyforge.org/
that allows the use of composite keys. I haven't used it myself, yet, so
can't comment on how well it works.
> I've also seen numerous suggestions that modern web-ish environments
> (Rails, etc) get confused by composite keys.
As far as Rails is concerned, it is far easier to go with the flow of
simple, numeric ids. Looking at the URLs I encounter in applications all
over the web makes me suspect that with other platforms it is similar.
> In all cases, the problem seems to be a lack of understanding.
Here you appear to take a shortcut in the argument. Would you mind to
elaborate?
Lack of understanding on whose part? Are you sure you're not mistaking a
trade-off between ease of development, given the constraints of a
specific platform, and adherence to data modeling principles as lack of
understanding? I can be fully aware of doing something wrong, but do it
nevertheless when it is the less wrong of two wrong alternatives.
Michael
--
Michael Schuerig
mailto:mic...@schuerig.de
http://www.schuerig.de/michael/
Walter Mitty
"Jonathan Leffler" <jlef...@earthlink.net> wrote in message
news:Df6dnZMSF6ohZonX...@earthlink.com...
> Walter Mitty wrote:
>> I'm confused about some recent trends in database design.
>>
>> When I learned databases, about a quarter of a century ago, the
>> preferred
>> practice was to use natural keys to refer to individual table rows,
>> unless
>> there's some real good reason to go with an artificial key. I've run
>> into a
>> few cases where I chose to use an artificial key, but most of the time
>> I've
>> used a natural key. [...]
>
> Somebody mentioned in an answer on StackOverflow that (Ruby on) Rails
> requires all tables to contain a column called 'id' (and, by
> implication, that column should be an 'identity' column or
> auto-generated value). (I've not verified that this is so.)
>
> I've also seen numerous suggestions that modern web-ish environments
> (Rails, etc) get confused by composite keys.
>
> In all cases, the problem seems to be a lack of understanding.
>
> --
> Jonathan Leffler
Thanks, Jonathan. This goes a long way towards clearing up my confusion.
If the application development tools are biased towards auto-generated
simple id columns, then it's only to be expected that people who drift into
database design would adopt that practice, and think of it as a de facto
standard.
So people who argue for modeling with natural keys, as I do, are out of step
with the rest of the parade. Oh well. At least I know where the other
opinion is coming from. And, at the end of the day, non trivial design
problems generally have more than one acceptable solution. If people can
get acceptable results with Rails out of the box, who am I to argue?
Dave Hughes
> Walter Mitty wrote:
>> I'm confused about some recent trends in database design.
>>
>> When I learned databases, about a quarter of a century ago, the preferred
>> practice was to use natural keys to refer to individual table rows, unless
>> there's some real good reason to go with an artificial key. I've run into a
>> few cases where I chose to use an artificial key, but most of the time I've
>> used a natural key. [...]
>
> Somebody mentioned in an answer on StackOverflow that (Ruby on) Rails
> requires all tables to contain a column called 'id' (and, by
> implication, that column should be an 'identity' column or
> auto-generated value). (I've not verified that this is so.)
>
> I've also seen numerous suggestions that modern web-ish environments
> (Rails, etc) get confused by composite keys.
Yes - Django's default ORM also defaults to auto-generated surrogate
keys for everything (it can handle non-auto-generated keys, but only
with a single column). However, many of the Python based web
environments are now moving to the rather impressive SQLAlchemy ORM
which handles "natural" keys just fine (including composites). I think
I'm right in saying Pylons and Turbogears already default to SQLAlchemy.
So, it's not all doom and gloom for those who prefer natural keys :)
Cheers,
Dave.
Bob Badour
Mindless, automatic use of a numeric id without regard to the logical
requirements is NEVER the lesser evil. The suggestion that it might be,
itself, demonstrates a lack of understanding of: logical vs. physical
and the integrity function of data management for a start.
Bob Badour
But how many "Great Blunders" does the SQLAlchemy ORM make?
Michael Schuerig
>>>In all cases, the problem seems to be a lack of understanding.
>>
>> Here you appear to take a shortcut in the argument. Would you mind to
>> elaborate?
>>
>> Lack of understanding on whose part? Are you sure you're not
>> mistaking a trade-off between ease of development, given the
>> constraints of a specific platform, and adherence to data modeling
>> principles as lack of understanding? I can be fully aware of doing
>> something wrong, but do it nevertheless when it is the less wrong of
>> two wrong alternatives.
>>
>> Michael
>
> Mindless, automatic use of a numeric id without regard to the logical
> requirements is NEVER the lesser evil.
Where do you get the "mindless"? Innuendo instead of argument, I gather.
If you're sure you are right, I'd like to know your reasoning why it is
always bad to use a tool that encourages numeric ids, notwithstanding
the advantages that tool might provide in other regards.
cim...@hotmail.com
<<I'd like to know your reasoning why it is
always bad to use a tool that encourages numeric ids>>
simple decent knowledge of what the relational model is. Considering
id's as a solution is a problem of ignorance of the consequence of
such choice in relational theory.
Bob Badour
> Bob Badour wrote:
>
>>>>In all cases, the problem seems to be a lack of understanding.
>>>
>>>Here you appear to take a shortcut in the argument. Would you mind to
>>>elaborate?
>>>
>>>Lack of understanding on whose part? Are you sure you're not
>>>mistaking a trade-off between ease of development, given the
>>>constraints of a specific platform, and adherence to data modeling
>>>principles as lack of understanding? I can be fully aware of doing
>>>something wrong, but do it nevertheless when it is the less wrong of
>>>two wrong alternatives.
>>>
>>>Michael
>>
>>Mindless, automatic use of a numeric id without regard to the logical
>>requirements is NEVER the lesser evil.
>
> Where do you get the "mindless"? Innuendo instead of argument, I gather.
Where is the mindfulness in "Rails requires all tables to contain a
column called 'id'" ?
> If you're sure you are right, I'd like to know your reasoning why it is
> always bad to use a tool that encourages numeric ids, notwithstanding
> the advantages that tool might provide in other regards.
First, we are not talking about a tool that "encourages" numeric ids. We
are talking about a tool that mindlessly requires them.
The appropriate tool for data management is a data management system.
Among the principal functions of a data management system are the
integrity function and the manipulation function. Compensating for the
loss of the integrity function is a tall order that neither rails nor
any ORM even begin to fill.
Your suggestion that these products have anything that even remotely
begins to compensate for the loss of the integrity function merely tells
us you are ignorant of that function in the first place and confirms the
OP's suggestion regarding a lack of understanding.
Michael Schuerig
> Michael Schuerig wrote:
>
>> Bob Badour wrote:
>>>Mindless, automatic use of a numeric id without regard to the logical
>>>requirements is NEVER the lesser evil.
>>
>> Where do you get the "mindless"? Innuendo instead of argument, I
>> gather.
>
> Where is the mindfulness in "Rails requires all tables to contain a
> column called 'id'" ?
You wrote your accusation in response to a message where I refered to an
extension that makes composite primary keys available in Rails. If you
want that limitation lifted so bad, just use this extension.
>> If you're sure you are right, I'd like to know your reasoning why it
>> is always bad to use a tool that encourages numeric ids,
>> notwithstanding the advantages that tool might provide in other
>> regards.
>
> First, we are not talking about a tool that "encourages" numeric ids.
> We are talking about a tool that mindlessly requires them.
See above.
> The appropriate tool for data management is a data management system.
> Among the principal functions of a data management system are the
> integrity function and the manipulation function. Compensating for the
> loss of the integrity function is a tall order that neither rails nor
> any ORM even begin to fill.
Would you say that these principal functions are essential for, say,
blogs, wikis, or social networking sites such as Flickr?
> Your suggestion that these products have anything that even remotely
> begins to compensate for the loss of the integrity function merely
> tells us you are ignorant of that function in the first place and
> confirms the OP's suggestion regarding a lack of understanding.
No, we're talking at cross-purposes and you're unwilling to acknowledge
it, insisting that your constraints apply always and everywhere.
There are application domains where quick development is more important
than 100% data integrity. There are applications, where high performance
is more important than immediate global consistency. You put down your
technical considerations categorically, whereas in practice they tend to
be toned down by business considerations. Like it or not, and whether I
like it or not, for that matter. There is a difference between
understanding the rules and abiding by them at all times.
Bob Badour
> Bob Badour wrote:
>
>>Michael Schuerig wrote:
>>
>>>Bob Badour wrote:
<snip>
>>The appropriate tool for data management is a data management system.
>>Among the principal functions of a data management system are the
>>integrity function and the manipulation function. Compensating for the
>>loss of the integrity function is a tall order that neither rails nor
>>any ORM even begin to fill.
>
> Would you say that these principal functions are essential for, say,
> blogs, wikis, or social networking sites such as Flickr?
Only if they have data to manage and only if they want to work reliably
and well.
>>Your suggestion that these products have anything that even remotely
>>begins to compensate for the loss of the integrity function merely
>>tells us you are ignorant of that function in the first place and
>>confirms the OP's suggestion regarding a lack of understanding.
>
> No, we're talking at cross-purposes and you're unwilling to acknowledge
> it, insisting that your constraints apply always and everywhere.
Unwilling? If you don't need to manage data, don't use a data management
system. In which case, I suggest you post in a newsgroup unrelated to
data management.
If you need to manage data, it's stupid to cripple the primary functions
of the data management system which is exactly what ORMs and rails do.
The concept is fundamentally flawed. I am not going to counter basic
ignorance piecemeal. Go learn the fundamentals and then come back.
Roy Hann
> Would you say that these principal functions are essential for, say,
> blogs, wikis, or social networking sites such as Flickr?
To repeat, no one here says synthetic keys are always wrong. Those
things you list may well benefit from them.
What IS wrong is insisting that ALL things ALWAYS benefit from them.
Listing a few examples where they are beneficial doesn't change that.
--
Roy
Walter Mitty
"Roy Hann" <spec...@processed.almost.meat> wrote in message
news:9NidnRjYr5xoOIjX...@pipex.net...
An even stronger claim would be the claim that all designs that are NOT
based on a synthetic auto-generated key are somehow defective designs. I've
not seen this claim in exactly these words over in stackoverflow.com. But
some of the comments seem to me to imply that the author of the comment
holds this view.
A comment that is often stated is that the author's personal preference is
to ALWAYS assign an auto-generated integer field as the first field of every
table, and to name this column ID. Let's leave aside the use of the word
"field" rather than column, and the universal name ID. What seems to be
missing in the ensuing discussion is that always using an autogenerated
field for reference reduces addressing by content to a parody of addressing
by location. All of the defects of graph based database organization
resurface in a different guise.
Michael Schuerig
> Michael Schuerig wrote:
>
>> Would you say that these principal functions are essential for, say,
>> blogs, wikis, or social networking sites such as Flickr?
>
> To repeat, no one here says synthetic keys are always wrong. Those
> things you list may well benefit from them.
Bob Badour just wrote in response to me:
> If you need to manage data, it's stupid to cripple the primary
functions
> of the data management system which is exactly what ORMs and rails do.
> The concept is fundamentally flawed. I am not going to counter basic
> ignorance piecemeal. Go learn the fundamentals and then come back.
I took him as claiming exactly what you say no one does claim.
EOD for me, it's going nowhere.
Bob Badour
> Roy Hann wrote:
>
>>Michael Schuerig wrote:
>>
>>>Would you say that these principal functions are essential for, say,
>>>blogs, wikis, or social networking sites such as Flickr?
>>
>>To repeat, no one here says synthetic keys are always wrong. Those
>>things you list may well benefit from them.
>
> Bob Badour just wrote in response to me:
>
>
>>If you need to manage data, it's stupid to cripple the primary functions
>>of the data management system which is exactly what ORMs and rails do.
>>The concept is fundamentally flawed. I am not going to counter basic
>>ignorance piecemeal. Go learn the fundamentals and then come back.
>
> I took him as claiming exactly what you say no one does claim.
>
> EOD for me, it's going nowhere.
Don't put words in my mouth, idiot. What I said says exactly what I
said: no more and no less. ORMs and rails cripple the primary functions
of a data management system: the integrity function and the manipulation
function for a start. If one has a need for data management, it is
incredibly stupid to cripple those functions.
My statement says nothing about choosing keys.
I repeat: If one doesn't have a need for data management, one ought to
discuss that somewhere it is relevant and on-topic--not here. If anyone
is talking past anyone, it is you. Is there an application that has no
need for data management? I neither know nor care. The topic is out of
scope in a data management forum.
If you want to get started on a basic education, the design criteria for
choosing keys are: uniqueness, irreducibility, simplicity, stability and
familiarity. The answer to: "Simplicity is a design criterion" is "Well,
duh!" That doesn't address the other 4 criteria nor does it address the
frequent conflicts among the criteria. Go away and learn. Come back when
you get a clue.
Bob Badour
From your blog: "I have (read) copies of five earlier of Celko’s books
on my shelf"
There's your problem right there. Instead if reading a moron like Celko
and making fun of the way he refers to his betters, you would learn a
lot more by ignoring him and reading his betters instead.
paul c
...
> An even stronger claim would be the claim that all designs that are NOT
> based on a synthetic auto-generated key are somehow defective designs. I've
> not seen this claim in exactly these words over in stackoverflow.com. But
> some of the comments seem to me to imply that the author of the comment
> holds this view.
That is the ultimate implication of many of these cookie-cutter
doctrines, aimed at people who can't bake in the first place. I also
blame supercilious managements. As for the systems they depend on, what
makes me think I must be watching the Three Stooges let loose in db
land, is supporting generated keys on base tables but not on views. I
don't object to a system optionally providing keys, for the same reason
as I don't object to users defining keys when it suits their purpose.
As long as a system has a formal definition and not an informal mystical
one, doesn't bother me. Candidate key generation for views seems so
useful to me, eg., for quota queries and a couple of other reasons that
I forget at the moment that I can't for the life of me see why it should
be deprecated in and of itself. Reminds me of one of the britcoms I was
watching last night where the union thought to sabotage management by
blocking the toilets and one of the actresses mispronounced on purpose,
saying that was cutting off our noses to spite our feces.
paul c
...
> EOD for me, it's going nowhere.
Like most of the db world.
Marshall
>
> ORMs and rails cripple the primary functions
> of a data management system: the integrity function and the manipulation
> function for a start.
A quibble: there is something that comes ("for a start") before
even integrity and manipulation, and that is being able to
design the schema appropriately and correctly. ORMs
cripple even that.
Marshall
Bob Badour
I would argue that's part of the mechanism by which they cripple the
primary functions, but I agree: They limit the statements one can make
in the first place.