Little design mistakes that can easily be avoided (1): Concatenated keys and addition of columns
Cimode
One of the most frequent misconception I face when working is about
how concatenation became a synonym for addition of attributes.
Lately, I have had the following example in a board...
COL1 COL2
ABC DEF
AB CDEF
It came to my attention lately that several developers make a strong
confusion onto how unicity should be implemented. (at least the one
that know that a concatenated keys exists!)...These developers usually
implement unicity by making a sum between the values in the two
columns and implementing unicity on that sum.
They end up with a statement such as (assuming we want to insert 'AB'
in COL1 and 'CDEF' in COL2)
if not exists(select COL1, COL2 from sometable where COL1+COL2 =
'ABCDEF')
begin
INSERT....
end
else
begin
REJECT
end
But looking at the example what should be done is rather...
if not exists(select COL1, COL2 from sometable where COL1= 'ABC' AND
COL2='DEF')
begin
INSERT....
end
else
begin
REJECT
end
I thought it would be useful to bring that to the attention of this NG
readers equivalent misconceptions...What about numbering them and
posting them from time to time...My guess is that a list on *little
mistakes that can be easily avoided* theme could within a year
constitute a nice guideline. Any thougths?
David Cressey
"Cimode" <cim...@hotmail.com> wrote in message
news:1179840361.1...@n15g2000prd.googlegroups.com...
I prefer the title "Stupid Database Tricks". This title is derived from
David Letterman's "Stupid Pet Tricks". I hope you can see behind the
levity, and catch some real serious points here. Sometimes, humor is the
only sane response to absurdity.
A few sample stupid database tricks:
We back up our database every day. But, come to think of it, we have never
actually tested our backups.
We found that views run awfully slow, so we forbid views at our shop.
We found that indexes slow things down, so we don't allow more than one
index on each table.
We found that indexes speed things up, so we have an index on every column
of every table!
Data normalization? We don't have time for all that theoretical crap!
We're working under a deadline here! Talk to us after the version 1 rollout
is complete.
Using our model, you can accomodate any new data that comes along with zero
changes to the metadata. That way, the DBA can't act as an obstacle to
progress!
Our problem is a really simple one, so we store everything in one giant
table!
You get the idea...
Cimode
[Snipped]
> I prefer the title "Stupid Database Tricks". This title is derived from
> David Letterman's "Stupid Pet Tricks". I hope you can see behind the
> levity, and catch some real serious points here.
The point here is not about a setting up a hack list but rather to
trigger more serious thinking. Manipulation should be in the toolset
to fight ignorance. Pointing out misconceptions does not seem to me
as teaching a hack. I guess the limit is blurry.
> Sometimes, humor is the only sane response to absurdity.
Nice. That's my quote of the day ;) I would add *sarcasm* to the
list.
> A few sample stupid database tricks:
>
> We back up our database every day. But, come to think of it, we have never
> actually tested our backups.
> We found that views run awfully slow, so we forbid views at our shop.
> We found that indexes slow things down, so we don't allow more than one
> index on each table.
> We found that indexes speed things up, so we have an index on every column
> of every table!
I would not put the above in a Design section but on a *Making data
administration life less miserable when using direct image systems *.
Do you get the idea?
> Data normalization? We don't have time for all that theoretical crap!
> We're working under a deadline here! Talk to us after the version 1 rollout
> is complete.
Bringing back some bad irritating memories from a previous life. At
IBM Global Services, I made *Most hated person of the company* at
least 3 month in row.(over 450 000 people that's an achievement don't
you think?)
For whatever relief that may bring, know that I am in position where
people do not take chances to bring that kind of argument against
me. All I can say here is that I have the opportunity to practice as
a DBA on *very* sensitive systems (Defense). It's good to know that
*some* people do not play anymore at computing. (but I am worried
about current trends)
> Using our model, you can accomodate any new data that comes along with zero
> changes to the metadata. That way, the DBA can't act as an obstacle to
> progress!
> Our problem is a really simple one, so we store everything in one giant
> table!
>
> You get the idea...
I would not call it an idea. ;) (hope you get the fun)
Regards...
Gene Wirchenko
>One of the most frequent misconception I face when working is about
>how concatenation became a synonym for addition of attributes.
>Lately, I have had the following example in a board...
>
>COL1 COL2
>ABC DEF
>AB CDEF
>
>It came to my attention lately that several developers make a strong
>confusion onto how unicity should be implemented. (at least the one
>that know that a concatenated keys exists!)...These developers usually
>implement unicity by making a sum between the values in the two
>columns and implementing unicity on that sum.
>
>They end up with a statement such as (assuming we want to insert 'AB'
>in COL1 and 'CDEF' in COL2)
>if not exists(select COL1, COL2 from sometable where COL1+COL2 =
>'ABCDEF')
>begin
>INSERT....
>end
>else
>begin
>REJECT
>end
This error has a brother who affects numeric columns.
(45+79#4579)
[snipped correct way]
>I thought it would be useful to bring that to the attention of this NG
>readers equivalent misconceptions...What about numbering them and
>posting them from time to time...My guess is that a list on *little
>mistakes that can be easily avoided* theme could within a year
>constitute a nice guideline. Any thougths?
I think it is a good idea.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
Gene Wirchenko
[snip]
>Data normalization? We don't have time for all that theoretical crap!
>We're working under a deadline here! Talk to us after the version 1 rollout
>is complete.
We do not go for theory; we are practical. (As if it is
practical to ignore work that other people have already done for you.)
[snip]
>You get the idea...
... like a horse's head in my bed.
Bob Badour
Every table will have a numeric id column, and this column will be the
primary key.
For performance, we enforce integrity in the application.
Duplicate removal is too expensive so we allow duplicates.
We back up our database every day -- reusing the same media every day.
We don't make backups because we our database handles recovery.
Joins are too expensive so we denormalized our data where we no longer
need to do any joins.
Integrity enforcement is too expensive so we don't do any.
Marshall
>
> Every table will have a numeric id column, and this column will be the
> primary key.
I don't know why, exactly, since it's not like it's the worst one
mentioned, but that one DRIVES ME CRAZY!
I hear people say this, and I want to say oh, I see: you're
just the stupidest freaking idiot ever, is that it? Of course
instead I say something about that being contraindicated.
My extra special favorite: there's a table that's *just* two
foreign keys to two different tables; a basic join table. The
pair (key1, key2) is unique of course. And oh yeah, we
have to add an additional numeric primary key.
Grrrrr.....
> For performance, we enforce integrity in the application.
Aiiieeee!!!
Marshall
Gene Wirchenko
>On May 22, 1:48 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>>
>> Every table will have a numeric id column, and this column will be the
>> primary key.
>
>I don't know why, exactly, since it's not like it's the worst one
>mentioned, but that one DRIVES ME CRAZY!
>
>I hear people say this, and I want to say oh, I see: you're
>just the stupidest freaking idiot ever, is that it? Of course
>instead I say something about that being contraindicated.
>
>My extra special favorite: there's a table that's *just* two
>foreign keys to two different tables; a basic join table. The
>pair (key1, key2) is unique of course. And oh yeah, we
>have to add an additional numeric primary key.
>
>Grrrrr.....
An auto-incrementing numeric primary key, right?
>> For performance, we enforce integrity in the application.
If it is not too much trouble.
>Aiiieeee!!!
This episode of Stupid Database Tricks have brought to you by the
ninth and fifth letters of the alphabet.
hast...@hotmail.com
>
> Every table will have a numeric id column, and this
> column will be the primary key.
>
Dear Bob,
I'm pretty new to this group, and thus not aware
of the previous discussions on this subject.
Please pardon what could be a FAQ.
I am of the opinion that a table modeling
things out there in the "real world" should
have a synthesized primary key column,
mainly because the mapping between the
database row and the "real" thing is usually
pretty complicated, or turns out to be, sooner
or later.
That primary key should never be shown to
the user.
Of course, the rule does not apply to all relations.
If this is the position you think is a stupid trick,
I would be very grateful to have a reference
explaining why it is a mistake.
Thanks much for your time
--- Raoul
Roy Hann
news:1179892760.2...@q66g2000hsg.googlegroups.com...
> On May 22, 1:48 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>>
>> Every table will have a numeric id column, and this column will be the
>> primary key.
>
> I don't know why, exactly, since it's not like it's the worst one
> mentioned, but that one DRIVES ME CRAZY!
>
> I hear people say this, and I want to say oh, I see: you're
> just the stupidest freaking idiot ever, is that it? Of course
> instead I say something about that being contraindicated.
I did a database design briefing for a regional outpost of one of the big
three consultancies just the week before last, and I actually spent quite a
lot of time preparing them to be receptive to my arguments debunking the
need for a numeric ID column in every table.
Well damn me; some B***TARD had got there before me and implanted the idea
that "sequentially" numbering the rows in a table is not just OK but is in
fact "best practice" (that was his exact phrase)! And once he'd put that
completely bogus label on it (with no justification that they could remember
or explain to me), then as far as they were concerned it would be reckless,
negligent, and nothing short of unprofessional to deviate.
No amount of explaining or probing or proposing problems with it would shake
them from the idea that a numeric ID column is the hallmark of a good
design. They were eager to seem educated and well-informed, and taking a
positive step (conspicuously adding a column) was, to them, a more obvious
way of demonstrating their expertise than the passive (and invisible) step
of not adding one. Psychology. Humph.
I was gutted. I'd gone in there prepared to do battle against people
advocating object-relational mapping frameworks that insist on row numbers,
and it still wasn't enough.
Roy
Cimode
The explanation is in the subject. Read it carefully and you will
understand what it's all about.
Regards...
Cimode
The explanation is in the subject. Read it carefully and you will
Cimode
ML: Marchall
GW: Gene Wirchenko
BB: Bob Badour
RH: Roy Hann
DC: David Cressey
CD: Cimode
How about this summary (based on the above comments in the thread,
these are known issues reminded in this thread)...I thought about 4
categories (I guess I am going to mount some web site soon):
CATEGORY 1: Little Design Mistakes that can be easily avoided
--> CONCATENATED KEYS VS ADDITION OF COLUMNS (CD, ML)
--> MISTAKING NUMERIC ID's (Surrogate key wannabe!) with PRIMARY KEYS
(BB, ML)
--> LETTING THE APPLICATION *HANDLE* THE BUSINESS LOGIC(BB)
--> AVOIDING SUBJECTIVITY INTO DESIGN(RH)
CATEGORY 2: Making your data administration life (under direct image
systems) less miserable
--> GETTING RID OF DATA REDUNDANCY (BB)
--> TESTING YOUR BACKUPS(DC)
--> USING VIEWS(DC)
CATEGORY 3: Straightforward fallacies (Could be called Snake Oils's
salesman argument)
--> DENORMALIZATION IMPROVES PERFORMANCE(DC)
--> BULLSHIT IS BEST PRACTICE (RH)
CATEGORY 4: Patterns of Behavior for Identifying the Ennemy and
Defeating him (How to kick snake oil's salesman B*TT)
--> ON HOW TO DEFEAT A SNAKE OIL'S SALESMAN ARGUMENT FOR KEYS(RH)
David Cressey
"Marshall" <marshal...@gmail.com> wrote in message
news:1179892760.2...@q66g2000hsg.googlegroups.com...
> >
> > Every table will have a numeric id column, and this column will be the
> > primary key.
>
> I don't know why, exactly, since it's not like it's the worst one
> mentioned, but that one DRIVES ME CRAZY!
Drives me crazy, too. I'm not sure why. Maybe it's because it's such
persistent mythology. I may have discovered at least one origin of this bad
design habit.
My latest interest is, of all things, MS Access. And I'm starting out with
Access 97. It turns out that, if you build a database with one of the
database wizards, this is usually what you get.
Also, if you design your own database and create your own tables, there
comes a time, for each table, where MS Access intervenes, and tells you
that you haven't assigned a primary key, and that one is recommended, and
that Access will do it for you, if that's ok.
If you say "yes" (who wouldn't?) what you get is precisely this: an extra
numeric column, auto assigned, and declared as the key of the new table.
Actually, I can live with this, for so called "entity tables". It would
be better for the newbie DBD to discover a natural key, and declare that,
but it's not so bad. For "relationship tables", the primary key ought to
be a compound key made up of two or more foreign keys. But that isn't what
Access does.
Now let me answer the question, "who wouldn't"? Someone who has learned
database design before building their first Access database, that's who.
But Access makes it seductively simple to design and build your own
database, without even a minimal understanding of the consequences of your
design choices.
There are probably other tools, besides Access, that help to propagate this
bad design habit. But, of them all, Access is probably pretty widespread.
Roy Hann
news:E5U4i.13768$mD.8066@trndny02...
>
> "Marshall" <marshal...@gmail.com> wrote in message
> news:1179892760.2...@q66g2000hsg.googlegroups.com...
> database design before building their first Access database, that's who.
Actually, although I obviously agree that row numbers are spurious, I think
you are mistaken about who wouldn't use them, and unless you meet it head-on
you will lose the argument every time.
The most compelling argument in favour of arbitrary row numbers being used
as keys and foreign keys is that they can be used to avoid propagating
user-entered values through multiple tables. If I allow a user to key in a
patient ID number say, and then propagate that key to dozens or hundreds of
other test and treatment tables, then I also have to provide application
code to reliably and completely propagate any subsequent correction to all
those tables too. But if I record the patient ID in just one table and map
it to a system-generated number, and use the system-generated value as the
key and foreign keys in all other tables, I would only need to make the
correction in one place.
The proper response to this sillines is to point out that in SQL when one
declares a foreign key constraint, it should be declared ON UPDATE CASCADE.
Then all the problems go away.
Roy
Brian Selzer
(1) the database is temporal--particularly historical,
(2) some constraints are temporal,
(3) keys may be updated and either (1) or (2),
(4) the schema may evolve and (1), or
(5) you want to mitigate the impact of (3) or (4).
"Marshall" <marshal...@gmail.com> wrote in message
news:1179892760.2...@q66g2000hsg.googlegroups.com...
> On May 22, 1:48 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>>
>> Every table will have a numeric id column, and this column will be the
>> primary key.
>
> I don't know why, exactly, since it's not like it's the worst one
> mentioned, but that one DRIVES ME CRAZY!
>
> I hear people say this, and I want to say oh, I see: you're
> just the stupidest freaking idiot ever, is that it? Of course
> instead I say something about that being contraindicated.
>
> My extra special favorite: there's a table that's *just* two
> foreign keys to two different tables; a basic join table. The
> pair (key1, key2) is unique of course. And oh yeah, we
> have to add an additional numeric primary key.
>
Only if you need to track changes to that table over time. Of course, then
it wouldn't be a simple junction table, since you have to also add an
interval attribute also.
Roy Hann
news:d9V4i.6084$4Y....@newssvr19.news.prodigy.net...
> Now, now, Marshall. System-generated surrogates can simplifiy things if
>
> (1) the database is temporal--particularly historical,
> (2) some constraints are temporal,
You are describing a possible workaround, not a desirable feature. One
should make that clear. Also it is not clear this is even a good
workaround. I suspect it is a very inadequate one.
> (3) keys may be updated
Wrong. Seriously wrong. See my earlier response to David Cressey re ON
UPDATE CASCADE.
> and either (1) or (2),
> (4) the schema may evolve and (1), or
I suspect this is wrong too, though to be honest I've no idea what you have
in mind here.
Roy
Brian Selzer
"Roy Hann" <spec...@processed.almost.meat> wrote in message
news:gradnembFKkVusnb...@pipex.net...
> "Brian Selzer" <br...@selzer-software.com> wrote in message
> news:d9V4i.6084$4Y....@newssvr19.news.prodigy.net...
>> Now, now, Marshall. System-generated surrogates can simplifiy things if
>>
>> (1) the database is temporal--particularly historical,
>> (2) some constraints are temporal,
>
> You are describing a possible workaround, not a desirable feature. One
> should make that clear. Also it is not clear this is even a good
> workaround. I suspect it is a very inadequate one.
>
Workaround? Perhaps. I suspect that you're wrong, though. See below.
>> (3) keys may be updated
>
> Wrong. Seriously wrong. See my earlier response to David Cressey re ON
> UPDATE CASCADE.
>
Cascading updates can cause other problems, like deadlocks. Multiple
assignment is a better solution, but there are problems with it as well.
There are other solutions to the problem that are implementation specific,
but whether or not key updates are cascaded or not isn't the issue: the
issue is that if keys can be updated in a temporal database, then you might
end up rewriting history! See below.
>> and either (1) or (2),
(1) If a key can be updated in a temporal database, then any query that
involves a state of the database at some previous point in time will be
incorrect. To fix this there are three alternatives (that I know of) (a)
introduce a system-generated surrogate that is guaranteed to never change
(or be reused), (b) introduce additional attributes to record the original
key value and add a transition constraint that prevents those attributes
from being updated (but see (2) below), or (c) maintain a timestamped
key-change log. Since natural keys can involve several attributes, using a
surrogate can significantly reduce the amount of storage for both data and
indexes, so unless there is a need for the original key value beyond that
for entity continuity, a surrogate can significantly improve performance in
a temporal database. (That's one of the reasons that they're used in data
warehouses!) Queries involving a timestamped key-change log can be very
involved and are much more susceptible to human error since they involve
traversing a linked list. At a minimum, they involve an additional
theta-join, which can significantly affect performance, especially for large
data sets. As a result, system-generated surrogates can reduce the
complexity (Big O) for such queries.
(2) Unless your database engine supports FOR EACH ROW triggers, not all
temporal constraints can be enforced for set-based updates--particularly if
the update involves prime attributes. One way to fix this is to add a
system-generated surrogate.
Bob Badour
> "Brian Selzer" <br...@selzer-software.com> wrote in message
> news:d9V4i.6084$4Y....@newssvr19.news.prodigy.net...
>
>>Now, now, Marshall. System-generated surrogates can simplifiy things if
>>
>>(1) the database is temporal--particularly historical,
>>(2) some constraints are temporal,
>
> You are describing a possible workaround, not a desirable feature.
Correction: He is talking nonsense again.
David Cressey
"Roy Hann" <spec...@processed.almost.meat> wrote in message
> "David Cressey" <cres...@verizon.net> wrote in message
> news:E5U4i.13768$mD.8066@trndny02...
> >
> > "Marshall" <marshal...@gmail.com> wrote in message
> > news:1179892760.2...@q66g2000hsg.googlegroups.com...
> > Now let me answer the question, "who wouldn't"? Someone who has learned
> > database design before building their first Access database, that's
who.
>
> Actually, although I obviously agree that row numbers are spurious, I
think
> you are mistaken about who wouldn't use them, and unless you meet it
head-on
> you will lose the argument every time.
Well, I'm retired, so I don't have to win arguments anymore.
Roy Hann
>
> "Roy Hann" <spec...@processed.almost.meat> wrote in message
> news:gradnembFKkVusnb...@pipex.net...
>> "Brian Selzer" <br...@selzer-software.com> wrote in message
>> news:d9V4i.6084$4Y....@newssvr19.news.prodigy.net...
>>> Now, now, Marshall. System-generated surrogates can simplifiy things if
>>>
>>> (1) the database is temporal--particularly historical,
>>> (2) some constraints are temporal,
>>
>> You are describing a possible workaround, not a desirable feature. One
>> should make that clear. Also it is not clear this is even a good
>> workaround. I suspect it is a very inadequate one.
>
> Workaround? Perhaps. I suspect that you're wrong, though. See below.
I'll leave it to someone who really knows what they're talking about to
settle this. All I know is that my not-very-recent reading of a systematic
approach to temporal databases concluded the need to engineer temporal
support into the DBMS. Hence my characterization of this as a workaround.
>>> (3) keys may be updated
>>
>> Wrong. Seriously wrong. See my earlier response to David Cressey re ON
>> UPDATE CASCADE.
>
> Cascading updates can cause other problems, like deadlocks.
That is a spurious objection. Deadlocks are always possible in a multiuser
system and cannot be eliminated even in principle. We always have to expect
to have to handle a deadlock every time we touch the database.
Furthermore, deadlocks are--in principle--easily handled (provided you know
what updates the transaction includes, and I admit SQL makes that very
hard).
> Multiple assignment is a better solution,
I would very much like a DBMS that supported multiple assignment, but I
don't see why you think it would be a "better" solution. Better at what?
I see how it would be worse. In terms of reliable coverage it is no better
than a series of single assignments: you have to specify which tables to
update, and that means relying on future developers retrofitting any
necessary additional table references everywhere the multiple assignment is
used.
> but there are problems with it as well.
Agreed, so what benefit makes it worth suggesting?
> There are other solutions to the problem that are implementation specific,
> but whether or not key updates are cascaded or not isn't the issue: the
> issue is that if keys can be updated in a temporal database, then you
> might end up rewriting history! See below.
I can easily see how one *could* choose a table design that attempts to
support temporal queries with a non-temporal DBMS where this would be a
problem. But I don't see why one necessarily always has to choose a design
that would be subject to that kind of anomaly.
I do notice that we are probably arguing at cross-purposes here. Marshall
was objecting to designs that *always* automatically include system
generated values; you are saying they *sometimes* have their uses. I
actually agree with that.
Roy
paul c
> Now, now, Marshall. System-generated surrogates can simplifiy things if
It looks as if you have slipped in the word "surrogate", which is not
what the others seem to be talking about. I believe Codd introduced
that word to stand for a value that a user can never see. If users
operate only at a logical level, then a surrogate must be a physical
feature.
(The use that is most obvious to me is to give physical indirection
which might allow a very fast update, eg., re-assign all waybills to a
different plane shortly before take-off, trading for slower queries.)
p
Bob Badour
Thank you for your question Raoul. I cannot give your question an
adequate response in a usenet message. Instead, I refer you to Fabian
Pascal's _Practical Issues..._ book.
The design criteria for candidate keys are: uniqueness, irreducibility,
simplicity, stability, familiarity. Sometimes the criteria contradict
one another requiring the designer to make tradeoffs. Sometimes the
familiar unique irreducible key is not stable enough or simple enough
for practical use -- leading a designer to introduce an arbitrary simple
stable attribute to act solely as a key. Please note that doing so is a
thoughtful design process.
I find any argument that resorts to "the real world" suspect. A dbms is
a formalism for symbolic manipulation. The utility of the formalism
depends on a number of factors including the appropriateness of the
design, the accuracy of the data and the correctness of the derivation
rules.
I ask you to contemplate the significance of "The Information Rule" ie.
that the dbms will represent all information as explicit values in
relations. If the user never sees some of the values, then the user can
never use them. Is using the information not the fundamental role of a user?
Consider as well why familiarity is a design criterion for candidate
keys and how that relates to the information rule.
Cimode
wrote:
> "Brian Selzer" <b...@selzer-software.com> wrote in message
[Snipped]
> I do notice that we are probably arguing at cross-purposes here. Marshall
> was objecting to designs that *always* automatically include system
> generated values; you are saying they *sometimes* have their uses. I
> actually agree with that.
>
> Roy
warning against the unfounded/unjustified urge of calling auto
incremental id's that are anything but primary keys. The reason is
simple: As direct image systems have poor support of concatenated
key's implementation, developpers end up creating meaningless pointers
without implementing systematically the 1:1 cardinality between the
surrogate key to be and the natural primary key...As a consequence,
identity is basically left to randomness, not to mention that a bias
is induced that sets become ordered to be processed...
It would be reasonnable to assume that a trdbms could or should infer
at design time some surrogate key implementation solely based on the
choice of a natural key. Such system couls be systematic if and only
if, it actually implements the full set of requirements (among which
the 1:1 cardinality forced between the natural key and the surrogate
key) to allow it to become a true surrogate key implementation and not
leave it as surrogate wanabe
I sometime come to the conclusion that surrogate keys should actually
become internal to the dbms and out of the designer''s view. I look
at it this way: either one implements *fully* the surrogate key
requirements for being a true surrogate key, either not use it at all
and favor a natural key (that a trdbms should support)
Just a matter of staying on topic, I add some interesting issues you
are bringing.
CATEGORY 1: Little Design Mistakes that can be easily avoided
--> CONCATENATED KEYS VS ADDITION OF COLUMNS (CD, ML)
--> MISTAKING NUMERIC ID's (Surrogate key wannabe!) with PRIMARY KEYS
(BB, ML)
--> LETTING THE APPLICATION *HANDLE* THE BUSINESS LOGIC(BB)
--> AVOIDING SUBJECTIVITY INTO DESIGN(RH)
--> NOT IMPLEMENTING THE FULL SET OF CONTRAINTS WHEN IMPLEMENTING A
SURROGATE KEY(CD)
Bob Badour
> "Marshall" <marshal...@gmail.com> wrote in message
> news:1179892760.2...@q66g2000hsg.googlegroups.com...
>
>>On May 22, 1:48 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>>
>>>Every table will have a numeric id column, and this column will be the
>>>primary key.
>>
>>I don't know why, exactly, since it's not like it's the worst one
>>mentioned, but that one DRIVES ME CRAZY!
>
> Drives me crazy, too. I'm not sure why. Maybe it's because it's such
> persistent mythology. I may have discovered at least one origin of this bad
> design habit.
Allow me to suggest why it drives folks crazy: it represents a complete
abdication of thought. As such, one cannot use reason to reach those who
espouse it because that too requires thought.
paul c
The insidious aspect of this I used to notice is that there is usually
some marketeer running the outpost who's smart enough to know that such
"practice" has a good chance of increasing billings. I saw one
multi-user app where the next shipment number was used as a reference
and so needed to be known at commit time, convoy phenomenon quickly took
effect and app crawled to a halt soon after testers logged on and the
marketeers saw the opportunity to sell additional stress testing to the
project, more consultants too, not to mention the re-write.
I really do believe that "successful" "big three" outposts are mindful,
at least subconsciously, that ignoring elemental features usually
creates profitable complications.
p
hast...@hotmail.com
>
> Thank you for your question Raoul. I cannot give your question an
> adequate response in a usenet message. Instead, I refer you to Fabian
> Pascal's _Practical Issues..._ book.
>
Thanks for your kind answer, Bob.
Fabian's book has been ordered,
and your post saved for further
re-reading
Have a very nice day
--- Raoul
Cimode
> Brian Selzer wrote:
> > Now, now, Marshall. System-generated surrogates can simplifiy things if
> > ...
>
> It looks as if you have slipped in the word "surrogate", which is not
> what the others seem to be talking about. I believe Codd introduced
> that word to stand for a value that a user can never see. If users
> operate only at a logical level, then a surrogate must be a physical
> feature.
excuse for absurdity. I wander sometime who first mentionned it. I
do also believe that surrogate key implementations really are aimed to
be internal and foreign to user view (including designer's view).
[Snipped]
> p
daveb
"Roy Hann" <spec...@processed.almost.meat> wrote in message
In my experience, the mind-set of developers who auto-id all tables causes
them to avoid identifying or enforcing appropriate natural keys (whether
through ignorance or malice, I would hesistate to say). Consequently, there
will be patients with more than one patient ID. So now the application must
provide a way to merge them; I submit that this is a much more difficult
task.
DBMS_Plumber
> Allow me to suggest why it drives folks crazy: it represents a complete
> abdication of thought. As such, one cannot use reason to reach those who
> espouse it because that too requires thought.
Let me go on the record as supporting Bob's sentiment here 100%.
And I'll up the ante by bashing the hand that feeds me. The way
foreign keys are implemented in SQL DBMS products is really sub-
optimal. Currently, if you declare a table to have a compound primary
key, and then foreign keys from that primary key, the DBMS will store
the 'value' of the key / FK attributes multiple times: once for the
keyed table, probably multiple times for each of the dependent
tables.
In hindsight, a much more sensible approach would have been to use the
underlying physical properties of the record store to maintain the
relationship. A set of 'key' attributes values needs only to be stored
and indexed once with physical access handled as it is currently.
>From a logical point of view, nothing changes. But taking this
approach would remove a lot of the physical efficiency arguments DBA's
typically use to justify system generated surrogate keys.
Oh well. Mebbe we'll get around to making this change one day.
DBMS_Plumber
> abdication of thought. As such, one cannot use reason to reach those who
> espouse it because that too requires thought.
Let me go on the record as supporting Bob's sentiment here 100%.
hast...@hotmail.com
>
> In my experience, the mind-set of developers who auto-id all tables causes
> them to avoid identifying or enforcing appropriate natural keys (whether
> through ignorance or malice, I would hesistate to say). Consequently, there
> will be patients with more than one patient ID. So now the application must
> provide a way to merge them; I submit that this is a much more difficult
> task.
Interestingly, I happen to work in the medical field. It is not
unheard of that
the customer organization reuses patient id (and, more frequently,
order
and sample ids)
It is also not unusual for these ids to be assigned hours after
analysis
result have been entered in the database, notably in case of
emergency.
We happen to model such ids as references - that is textual
identifications which are unique in a given period of time.
--- Raoul
-CELKO-
Hey, I have looong rants on this topic. I like to pull out the Dr.,
Codd quotes about what a surrogate key really is and then mention that
the Kabbalah also thinks that everything is keyed by God with a 17-
digit Hebrew number as its true name.
>> I may have discovered at least one origin of this bad design habit .. Access 97. It turns out that, if you build a database with one of the database wizards, this is usually what you get. <<
I like to to think that it is an attempt to mimic a pointer chain file
system in SQL, but without the double linkage, utility programs, etc.
that made them actually work.
But you are probalby right given the level of Newbie coders today who
learns on ACCESS ..
David Cressey
"-CELKO-" <jcel...@earthlink.net> wrote in message
news:1180059023.9...@k79g2000hse.googlegroups.com...
> >> Drives me crazy, too. I'm not sure why. Maybe it's because it's such
persistent mythology. <<
>
> Hey, I have looong rants on this topic. I like to pull out the Dr.,
> Codd quotes about what a surrogate key really is and then mention that
> the Kabbalah also thinks that everything is keyed by God with a 17-
> digit Hebrew number as its true name.
>
> >> I may have discovered at least one origin of this bad design habit ..
Access 97. It turns out that, if you build a database with one of the
database wizards, this is usually what you get. <<
>
> I like to to think that it is an attempt to mimic a pointer chain file
> system in SQL, but without the double linkage, utility programs, etc.
> that made them actually work.
Good thought. A lot of people who propound this stupid trick as a "best
practice" are thinking in terms of chains of pointers. It's the revenge of
the CODASYL Zombies.
Roy Hann
> Good thought. A lot of people who propound this stupid trick as a "best
> practice" are thinking in terms of chains of pointers. It's the revenge
> of
> the CODASYL Zombies.
I don't buy it. *I* was a network DB coding zombie in my youth and it was
precisely because I endured that pain for several years that I fell
instantly and hopelessly in love with the relational model when I first
encountered it. I don't think I am likely to be very different from anyone
else from that era. It is highly implausible to me that anyone who worked
with that shit would want to perpetuate it after being exposed to a better
way.
It is just the new, naive manpower flooding into the workforce who keep
rediscovering and re-entrenching the obvious approach. Who is going to
think of looking for plywood if they can plainly see a pile of logs and they
need a cabin quick?
Roy
David Cressey
"Roy Hann" <spec...@processed.almost.meat> wrote in message
> "David Cressey" <cres...@verizon.net> wrote in message
> news:FDt5i.8082$hw.4094@trndny08...
> > Good thought. A lot of people who propound this stupid trick as a "best
> > practice" are thinking in terms of chains of pointers. It's the revenge
> > of
> > the CODASYL Zombies.
>
> I don't buy it. *I* was a network DB coding zombie in my youth and it was
> precisely because I endured that pain for several years that I fell
> instantly and hopelessly in love with the relational model when I first
> encountered it. I don't think I am likely to be very different from
anyone
> else from that era. It is highly implausible to me that anyone who worked
> with that shit would want to perpetuate it after being exposed to a better
> way.
>
I am sure you speak for many, if not most CODASYL DBMS programmers. without
contradicting you, I'm going to say for sure that the opposite camp
existed, and has existed continuously to this day.
In the mid 1980s, I was fortunate enough to be called to teach DEC Rdb/VMS
programming and design. Some of the people who knew VAX DBMS were the
hardest to reach in the course of a one or two week course. They simply saw
the relational model as missing the pointers without which they couldn't see
the sets.
But the real problem was with the VAX DBMS instructors (of whom I was never
one). These people kept getting bad reviews when teaching Rdb. I finally
got to observe one of them at work. She was teaching Rdb design, but she
was still selling network design, at an almost subverbal level.
In the 1990s, I got to do short term contracts working on lots of Rdb
databases that were in sad shape. I can't tell you the number of them that
had been designed by reinventing plex structures. They ran slow, and they
inflicted all the pain on Rdb programmers that CODASYL had inflicted on the
previous generation.
> It is just the new, naive manpower flooding into the workforce who keep
> rediscovering and re-entrenching the obvious approach. Who is going to
> think of looking for plywood if they can plainly see a pile of logs and
they
> need a cabin quick?
>
I'm going to suggest that, at a certain point of discovery, a seasoned
CODASYL Zombie *is* new, naive manpower. He's been born again, but he's
still naive. Kudos to you for not staying in that stage of development very
long. From what you write, I'm guessing you passed through that phase in
anywhere from 15 minutes to two weeks.
Gene Wirchenko
[snip]
>Good thought. A lot of people who propound this stupid trick as a "best
>practice" are thinking in terms of chains of pointers. It's the revenge of
>the CODASYL Zombies.
"CCCHHH-AAAIII-NNNSSS!"
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
vldm10
> On May 22, 1:48 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>
>
>
> > Every table will have a numeric id column, and this column will be the
> > primary key.
Regardless of what is intention here, it seems you and B. Badour
misunderstand some important aspects of "one-column-key" as well as
some aspects of "numeric". And of course key can be "alphabetic".
> I don't know why, exactly, since it's not like it's the worst one
> mentioned, but that one DRIVES ME CRAZY!
This meditation is somehow paradoxical regarding that you
misunderstand some basic things:
> I hear people say this, and I want to say oh, I see: you're
> just the stupidest freaking idiot ever, is that it? Of course
> instead I say something about that being contraindicated.
> My extra special favorite: there's a table that's *just* two
> foreign keys to two different tables; a basic join table. The
> pair (key1, key2) is unique of course.
Here, you misunderstood - what is not primary key. Pair (key1, key2)
is not primary key because it is not unique. Briefly your conclusion
seems to arise from a misunderstanding concerning the nature of the
simplest DB cases. It seems to me also, that this is source of your
confusion regarding "two foreign keys".
> And oh yeah, we
> have to add an additional numeric primary key.
>
> Grrrrr.....
>
> > For performance, we enforce integrity in the application.
>
> Aiiieeee!!!
>
> Marshall
Vladimir Odrljin
Marshall
> On May 22, 11:59 pm, Marshall <marshall.spi...@gmail.com> wrote:
>
> > On May 22, 1:48 pm, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>
> > > Every table will have a numeric id column, and this column will be the
> > > primary key.
>
> Regardless of what is intention here, it seems you and B. Badour
> misunderstand some important aspects of "one-column-key" as well as
> some aspects of "numeric". And of course key can be "alphabetic".
>
> > I don't know why, exactly, since it's not like it's the worst one
> > mentioned, but that one DRIVES ME CRAZY!
>
> This meditation is somehow paradoxical regarding that you
> misunderstand some basic things:
>
> > I hear people say this, and I want to say oh, I see: you're
> > just the stupidest freaking idiot ever, is that it? Of course
> > instead I say something about that being contraindicated.
> > My extra special favorite: there's a table that's *just* two
> > foreign keys to two different tables; a basic join table. The
> > pair (key1, key2) is unique of course.
>
> Here, you misunderstood - what is not primary key. Pair (key1, key2)
> is not primary key because it is not unique.
If the table has only two columns, as I specified, then it is
necessarily unique, by the definition of relation. Even
it it wasn't, as may be the case in badly executed SQL
tables, there is still no value in adding an additional
column which will contain no further information.
> Briefly your conclusion
> seems to arise from a misunderstanding concerning the nature of the
> simplest DB cases. It seems to me also, that this is source of your
> confusion regarding "two foreign keys".
You sure use that word "misunderstand" a lot.
Marshall
Brian Selzer
>> Here, you misunderstood - what is not primary key. Pair (key1, key2)
>> is not primary key because it is not unique.
>
> If the table has only two columns, as I specified, then it is
> necessarily unique, by the definition of relation. Even
> it it wasn't, as may be the case in badly executed SQL
> tables, there is still no value in adding an additional
> column which will contain no further information.
>
The problem is not that the key values aren't unique within a single
extension of the database, it is that a key value may identify one object in
the universe in one extension and a different object in another. A
surrogate key solves this problem because there is a bijective mapping
between the values in the surrogate domain and all possible objects in the
universe of discourse. In addition, every other domain defined in the
database necessarily maps injectively into the surrogate domain. As a
result, Kripke semantics can be used to deal with the set of all valid
extensions and the set of transition constraints, which I believe is
isomorphic to the accessibility relation in a Kripke Frame. Also, it is my
understanding is that a system with a fixed domain is more expressive than
one with a world-relative domain. So I would argue that there is indeed
value in adding an additional column.
[snip]
vldm10
It seems, this time you misunderstood my comment, which says that you
derive general conclusion which is based on the simple DB cases.
a)
The relationship between two entities is determined with the nature of
this relationship in real situation i.e. in real application. It is
not determined for example by you. ( "the table has only two columns,
as I specified, then it is necessarily unique, by definition of
relation." ?)
Now, one can design DB solution for the relationship as:
1) "the table which has only two columns......"
or as
2) " adding an additional column...."
In case 2) one maybe doesn't want to expose "two columns". Or maybe
some other company is responsible for maintenance of "two columns" and
one want to maintain only things for which s/he is responsible and for
what has information. Or one wants that "numeric key" be public so
that people can trace the events regarding this "number".
Here you draw conclusion about solution 2), based on solution 1).
However, solution 2) is more general - theoretically and
practically.
b)
If your "objections" are related to my solution (which is on www.dbdesign10.com)
than I must tell you that you misunderstand it.
My solution is not based on "two columns" or on "additional column" or
on "numeric primary key".
For example my solution is not based on surrogate key. I don't know
what surrogate key is. More important there is no effective algorithm
or solution which show how to construct surrogate key in general
case.
In my solution I gave effective method how to construct any entity or
relationship so that they have simple key. This construction has
semantic, conceptual and logical level.
Roughly and briefly, this construction is based on two identifiers:
1. the identifier of the state of an entity or relationship
2. the identifier of the entity or relationship.
It depends on real application and on DB design solution how these
identifiers can be applied.
(Some other things also can be applied - as "knowledge columns" ...)
So in your trivial case you can use only "two columns", and keep in
head some things.
Maybe you want to understand what is "an additional column". Then in
your trivial case and with some intentions defined in a) it is just as
it name says - the identifier of the relationship.
If you maybe want to know theoretical explanation about the identifier
of relationship - you can find it on my web site. This is regarding my
solution.
But in my opinion the most important thing here is solution for
complex and advanced DBs. My intention was to give good solutions for
these DBs. This is also the reason why I think that your trivial case
and draw general conclusion from it has no sense.
Off course, I am aware that it is possible that I made some mistakes
or that I have some deep misunderstandings in my solution. But in that
case I want that it be shown exactly and concretely. This is the
reason why I made my solution public, put on the web and this user
group.
> > Briefly your conclusion
> > seems to arise from a misunderstanding concerning the nature of the
> > simplest DB cases. It seems to me also, that this is source of your
> > confusion regarding "two foreign keys".
>
> You sure use that word "misunderstand" a lot.
>
> Marshall- Hide quoted text -
>
> - Show quoted text -
Marshall
I was entirely prepared to blow you off until I got to your
last paragraph. Sigh. Now I feel compelled to respond.
> It seems, this time you misunderstood my comment, which says that you
> derive general conclusion which is based on the simple DB cases.
I don't see any way that you can extrapolate what my reasoning
process is from two messages. I also note that you appear to
be assuming that I'm aware of other writings of yours, whereas
I'm only currently familiar with the two in this thread: June 5 and
June 9.
I do not derive general conclusions from simple cases. However,
I feel strongly that understanding of simple cases is a prerequisite
for understanding of complex cases.
Specifically, I am speaking of the simple case where there is
a table that has two columns, each of which is a foreign key
to a column in a different table. I am only speaking of this case,
so whatever you may say about *other* cases, it may or may
not be valid, but it will not address what I'm saying in any way.
In this case, which is a minimal and common model for a many
to many relationship, the two columns together are unique.
This follows immediately from the definition of what a relation is.
Well, I already said that, so I guess I don't expect you to
get it just because I said it again. In any event, if you wish
to *refute* what I said, you have to address specifically
the case I describe. If you want to talk about something
else, fine, but it won't be a refutation of what I wrote.
> a)
> The relationship between two entities is determined with the nature of
> this relationship in real situation i.e. in real application. It is
> not determined for example by you. ( "the table has only two columns,
> as I specified, then it is necessarily unique, by definition of
> relation." ?)
>
> Now, one can design DB solution for the relationship as:
>
> 1) "the table which has only two columns......"
>
> or as
>
> 2) " adding an additional column...."
>
> In case 2) one maybe doesn't want to expose "two columns". Or maybe
> some other company is responsible for maintenance of "two columns" and
> one want to maintain only things for which s/he is responsible and for
> what has information. Or one wants that "numeric key" be public so
> that people can trace the events regarding this "number".
> Here you draw conclusion about solution 2), based on solution 1).
> However, solution 2) is more general - theoretically and
> practically.
None of the above is relevant to what I wrote, either in my previous
post or above in this post.
> b)
> If your "objections" are related to my solution (which is onwww.dbdesign10.com)
> than I must tell you that you misunderstand it.
Well we are back to that word "misunderstand."
Anyway, I haven't read your solution, and have no opinion of it.
> My solution is not based on "two columns" or on "additional column" or
> on "numeric primary key".
> For example my solution is not based on surrogate key. I don't know
> what surrogate key is. More important there is no effective algorithm
> or solution which show how to construct surrogate key in general
> case.
These statements are not self-consistent. If you don't know what
a surrogate key is, you cannot know whether any solution you have
is similar or not. Likewise, you cannot know whether it is possible
to construct a surrogate key in the general case.
> In my solution I gave effective method how to construct any entity or
> relationship so that they have simple key. This construction has
> semantic, conceptual and logical level.
> Roughly and briefly, this construction is based on two identifiers:
> 1. the identifier of the state of an entity or relationship
> 2. the identifier of the entity or relationship.
Your 1.) only makes sense if the "entity or relationship" has state.
Some don't.
Every proposition (let's use that instead of the cumbersome "entity
or relationship") is unique. What the characteristics are depends
on the proposition, but it is always the case that the union of
the attributes of a proposition are unique. If we have proposition
A and proposition B, and these two propositions have the same
set of attributes, and for each attribute, the value of the attribute
in A equals the value of the attribute in B, then A = B.
Again, this is definitional, and not subject to argument. If you
want to supply definitions for different terms, that's fine, but
you have to tell us what those definitions are.
> It depends on real application and on DB design solution how these
> identifiers can be applied.
> (Some other things also can be applied - as "knowledge columns" ...)
> So in your trivial case you can use only "two columns", and keep in
> head some things.
> Maybe you want to understand what is "an additional column". Then in
> your trivial case and with some intentions defined in a) it is just as
> it name says - the identifier of the relationship.
> If you maybe want to know theoretical explanation about the identifier
> of relationship - you can find it on my web site. This is regarding my
> solution.
> But in my opinion the most important thing here is solution for
> complex and advanced DBs. My intention was to give good solutions for
> these DBs. This is also the reason why I think that your trivial case
> and draw general conclusion from it has no sense.
I have not drawn any general conclusions; I have only drawn
conclusions about the specific case of a many-to-many relationship
modeled as a two-foreign-key table.
As far as my leaning about your ideas, I must say "no thank you."
I already know "theoretical explanation about the identifier
of relationship" and you don't seem to.
> Off course, I am aware that it is possible that I made some mistakes
> or that I have some deep misunderstandings in my solution. But in that
> case I want that it be shown exactly and concretely. This is the
> reason why I made my solution public, put on the web and this user
> group.
HTH.
Marshall
Keith H Duggar
> Marshall wrote:
> > If the table has only two columns, as I specified, then it
> > relation. Even it it wasn't, as may be the case in badly
> > executed SQL tables, there is still no value in adding an
> > additional column which will contain no further information.
>
> The problem is not that the key values aren't unique within a single
> extension of the database, it is that a key value may identify one object in
> the universe in one extension and a different object in another. A
> surrogate key solves this problem because there is a bijective mapping
> between the values in the surrogate domain and all possible objects in the
> universe of discourse.
There is no such "object" as an "object". If you stop thinking
in terms of "objects" and "entities" then you will stop having
the fake problems which lure you to the surrogate ID crutch.
It's be disccused here many times that "object" is a non-idea,
a semantic vacuum, a useless crutch substituting thought that
leads only to confusion and problems. You can see here, now,
the mis-steps one takes when they confuse their thinking with
"entity" or "object" non-sense.
As an exercise, try rewriting your paragraph above without using
"object", "entity", "thing" or other such void concepts. In other
words, try to actually say some"thing" ;-)
Keith -- Fraud 6
David BL
> If we have proposition
> A and proposition B, and these two propositions have the same
> set of attributes, and for each attribute, the value of the attribute
> in A equals the value of the attribute in B, then A = B.
You need to word that a little more carefully
Consider predicates
P(x,y) = x < y
Q(x,y) = x > y
Then P(1,2) and Q(1,2) are different propositions even though they
have the same values for all the attributes.
Brian Selzer
"Keith H Duggar" <dug...@alum.mit.edu> wrote in message
news:1181500919.2...@n4g2000hsb.googlegroups.com...
> Brian Selzer wrote:
>> Marshall wrote:
>> > If the table has only two columns, as I specified, then it
>> > [value1,value2] is necessarily unique, by the definition of
>> > relation. Even it it wasn't, as may be the case in badly
>> > executed SQL tables, there is still no value in adding an
>> > additional column which will contain no further information.
>>
>> The problem is not that the key values aren't unique within a single
>> extension of the database, it is that a key value may identify one object
>> in
>> the universe in one extension and a different object in another. A
>> surrogate key solves this problem because there is a bijective mapping
>> between the values in the surrogate domain and all possible objects in
>> the
>> universe of discourse.
>
> There is no such "object" as an "object". If you stop thinking
> in terms of "objects" and "entities" then you will stop having
> the fake problems which lure you to the surrogate ID crutch.
>
I would have to stop thinking altogether: without objects there can be no
conception; without objects there can be no perception; without objects
there can be no discourse!
(Perhaps I should have used the term "individual" instead of "object" to
avoid confusion.)
> It's be disccused here many times that "object" is a non-idea,
> a semantic vacuum, a useless crutch substituting thought that
> leads only to confusion and problems. You can see here, now,
> the mis-steps one takes when they confuse their thinking with
> "entity" or "object" non-sense.
>
Formal semantics involves interpretation--that is, mapping terms within
sentential formulae to objects in the universe of discourse. Without
objects, all you have left is a meaningless collection of symbols organized
in a meaningless way.
> As an exercise, try rewriting your paragraph above without using
> "object", "entity", "thing" or other such void concepts. In other
> words, try to actually say some"thing" ;-)
>
They are not void concepts.
> Keith -- Fraud 6
>
David BL
> "Keith H Duggar" <dug...@alum.mit.edu> wrote in >
> > There is no such "object" as an "object". If you stop thinking
> > in terms of "objects" and "entities" then you will stop having
> > the fake problems which lure you to the surrogate ID crutch.
>
> I would have to stop thinking altogether: without objects there can be no
> conception; without objects there can be no perception; without objects
> there can be no discourse!
So true
Jan Hidders
>
> b)
> If your "objections" are related to my solution (which is onwww.dbdesign10.com)
> than I must tell you that you misunderstand it.
Hello Vadim. I just saw that reference of yours and wondered what it
was exactly about. I'm a bit timepressed so didn't have really time to
read it properly or plough through this thread, but could you briefly
explain what this is about or what the problem is that you are trying
to solve? At first sight it looks as if you are reinventing the
semantics of binary NIAM/ORM but that could be just because I didn't
look hard enough.
Just curious,
-- Jan Hidders
Marshall
> "Keith H Duggar" <dug...@alum.mit.edu> wrote in > > Brian Selzer wrote:
> >> Marshall wrote:
> >> > If the table has only two columns, as I specified, then it
> >> > [value1,value2] is necessarily unique, by the definition of
> >> > relation. Even it it wasn't, as may be the case in badly
> >> > executed SQL tables, there is still no value in adding an
> >> > additional column which will contain no further information.
>
> >> The problem is not that the key values aren't unique within a single
> >> extension of the database, it is that a key value may identify one object
> >> in
> >> the universe in one extension and a different object in another. A
> >> surrogate key solves this problem because there is a bijective mapping
> >> between the values in the surrogate domain and all possible objects in
> >> the
> >> universe of discourse.
>
> > There is no such "object" as an "object". If you stop thinking
> > in terms of "objects" and "entities" then you will stop having
> > the fake problems which lure you to the surrogate ID crutch.
>
> I would have to stop thinking altogether: without objects there can be no
> conception; without objects there can be no perception; without objects
> there can be no discourse!
Well, that seems a bit overstated. I think abstract algebra shows
us that it's possible to work usefully with a formal system even
without having a specific model in mind.
I'm unclear exactly how to read your use of the word "extension"
above.
Do you mean the semantic connection with the universe of discourse?
Or a specific database value or instance? Hair extensions? (j/k)
Probably a more specific, less ambiguous term would be better.
It also seems you're suggesting that surrogate key should
be *globally* unique. Do you mean that? If so, yuck.
> (Perhaps I should have used the term "individual" instead of "object" to
> avoid confusion.)
I don't see how one could improve on "proposition" and "predicate."
Marshall
vldm10
Hello Jan. You can call me Vlado, because it is pretty different from
Vadim.
But this is not so important. However, maybe Vadim can give more
details.
I didn't read "NIAM/ORM" and I have started with these ideas long time
ago, in my projects. So you can give me some web addresses for these
theories and I will read it and tell you my opinion.
I think that it is new because I didn't notice these solutions
anywhere.
On the other hand I noticed only unsuccessful tries as it is for
example in RM/T.
Anyway if you are interested we can analyze important parts.
For example we can discuss my b) part from my post in this thread, on
which you replied.
I am also very limited with time, I have 3-5 hours free weekly, but I
will try to find time.
Vladimir Odrljin
Jan Hidders
>
> Hello Jan. You can call me Vlado, because it is pretty different from
> Vadim.
My apologies, I've been away for a while and mistook you for someone
else.
> I didn't read "NIAM/ORM" and I have started with these ideas long time
> ago, in my projects. So you can give me some web addresses for these
> theories and I will read it and tell you my opinion.
See <http://www.orm.net> for a starting point. It's probably the data
modelling technique / notation with the most research and research
literature behind it. If you are serious about thinking about a new
data model you should know it, and probably also about anything that
goes on in the ER conferences:
<http://er2007.massey.ac.nz/index.shtml?topics>
-- Jan Hidders
David BL
> On Jun 11, 1:40 am, "Brian Selzer" <b...@selzer-software.com> wrote:
> > "Keith H Duggar" <dug...@alum.mit.edu> wrote in >
> > > There is no such "object" as an "object". If you stop thinking
> > > in terms of "objects" and "entities" then you will stop having
> > > the fake problems which lure you to the surrogate ID crutch.
>
> > I would have to stop thinking altogether: without objects there can be no
> > conception; without objects there can be no perception; without objects
> > there can be no discourse!
>
> Well, that seems a bit overstated. I think abstract algebra shows
> us that it's possible to work usefully with a formal system even
> without having a specific model in mind.
I've never come across any mathematical system that doesn't have
"things". This is irrespective of the fact that the axiomatic
approach often states properties about a set of things without
uniquely defining them.
There are good reasons to avoid surrogate IDs. I'm coming to think it
has little to do with how one conceptualises in terms of entities, and
has more to do with the idea that a relation should preferably have a
simple, sensible and stable intensional definition from which its
extension can be uniquely determined (and could depend on time)
through knowledge external to the database.
intensional definition: S1 = The names of the studio albums of the
Gorillaz
extensional definition: S2 = { "Gorillaz", "Demon Days" }
The introduction of surrogate IDs for the studio albums is
incompatible with extensions that are uniquely determined from simple
intensional definitions. It has *nothing* to do with whether one
conceptualises in terms of studio albums as entities.
That being said there seem to be cases where surrogate IDs are
inevitable. Eg an electronic circuit needs every node to be uniquely
identified.
vldm10
Thank you for these web addresses. I checked "NIAM/ORM" and didn't
find
anything reinvented from my side. Although, regarding that you are
knowledgeable person I would like to know what is in your opinion
reinvented (or looks like it) in my "model". You can roughly present
it or one by one.
Vladimir Odrljin.
Jan Hidders
> [...] Although, regarding that you are
> knowledgeable person I would like to know what is in your opinion
> reinvented (or looks like it) in my "model". You can roughly present
> it or one by one.
The ideas of :
1. Adding a special key to relations that identifies the entities or
relationships represented by the tuples in the relation.
2. Using the presence of this key to split everything as much as
possible into binary relations.
These are old ideas, and have already been rejected before. I have not
seen any argumentation by you that explains why this would now have
become a good idea.
Btw. did you realize that for a relation R(K, A_1, ..., A_n) where {K}
is a superkey, it always holds that the relation equals the joins of
R(K, A_1), ..., R(K, A_n)? Any assumptions about mutual independence
are not necessary. Also note that such a relation is not necessarily
in 5NF if {A_1, ..., A_n} are mutually independent, or even in 4NF,
for that matter.
-- Jan Hidders
David Cressey
"Jan Hidders" <hid...@gmail.com> wrote in message
news:1181739221.3...@j4g2000prf.googlegroups.com...
Just as a tangential note, the above provides a simple answer for those who
wish to avoid SQL NULLs. If we decompose the relation (relational table)
according to the equality outlined above, a "missing value" for any of the
attributes A_1 .. A_N can be expressed as a missing row in the decomposed
relation. While missing values in a row provoke endless controversy in the
discussion of SQL tables, missing rows hardly cause a ripple.
In order to be strict about it, we need one more relation consisting only
of the superkey K. That relation can serve as a reference base for the K in
all the other relations.
This doesn't add anything to the point you were making, Jan. I just wanted
to note it in passing.
vldm10
> On 13 jun, 10:41, vldm10 <vld...@yahoo.com> wrote:
>
> > [...] Although, regarding that you are
> > knowledgeable person I would like to know what is in your opinion
> > reinvented (or looks like it) in my "model". You can roughly present
> > it or one by one.
This is not about reinventing ORM. Anyway I have first time in this NG
the questions that go in some deeper parts of my "model".
One important thing in model is representation of data in form of
"simple key + one attribute" or as sometime is described as the total
decomposition. This form of data has enormous importance for practical
issues
> The ideas of :
> 1. Adding a special key to relations that identifies the entities or
> relationships represented by the tuples in the relation.
Here I believe that you misunderstand what is key. I have
1. the identifier of the state of an entity or relationship
2. the identifier of the entity or relationship.
I will use shortcut IdSt for first and IdEt for second
Usually key is IdSt. But IdSt always goes in pair with IdEt.
These two identifiers have strong semantic and conceptual base.
In simple DBs we can use only IdEt as key. For example if entity has
only one state i.e. if entity is always same, then IdSt = IdEt i.e.
there is no IdSt.
> 2. Using the presence of this key
The idea here is not:
>to split everything as much as
> possible into binary relations.
Here we need method to do this "decomposition" effectively and
completely. In fact here we don't have decomposition, we have
construction.
> These are old ideas, and have already been rejected before. I have not
> seen any argumentation by you that explains why this would now have
> become a good idea.
>
> Btw. did you realize that for a relation R(K, A_1, ..., A_n) where {K}
> is a superkey, it always holds that the relation equals the joins of
> R(K, A_1), ..., R(K, A_n)? Any assumptions about mutual independence
> are not necessary. Also note that such a relation is not necessarily
> in 5NF if {A_1, ..., A_n} are mutually independent, or even in 4NF,
> for that matter.
This is about normal form which I defined and I would like to
emphasize same aspects and intentions of it.
1) It is simple.
2) It is the construction rather then the decomposition.
So I don't want first to construct a relation and then if it is
wrong to make decomposition.
I want immediately good construction.
In this sense it is irrelevant for this construction that R should
be in 5NF or 4NF. It is important
that R1, .. Rk are in all normal forms immediately, and good for
many other things. This construction
also means that I don't need to decompose a relation first in 4NF,
then in 5NF and then in my
Simple Form. Construction doesn't need any sequence of the
decompositions. It do only one - Simple Form.
3) there are two conditions here
(i) That key is simple.
(ii) That attributes are mutually independent - this is in fact
about entity.
So entity is involved here as a set of mutually independent
attributes Here entity is an
abstraction as it is set i.e. relation. I gave some relationship
between these abstractions in 4.3.
So I believe that these abstractions are very "connected".
4) There is one condition more here which is not mentioned. It is
that one attributes always belong
to one entity. Also the attributes A1,...,An. are all belongs to
one entity. This condition can be derived
from other things in this "model"
So this definition is rather convention about above conditions.
Definitely, I don't have good examples and this is what confused
people. I am very short with time and I need 3-5 days to make two good
examples.
>
> -- Jan Hidders
Vladimir Odrljin
Brian Selzer
I guess "extension" isn't the right word, although Codd used it in this
sense, and it does represent a set of propositions that are true under a
given interpretation for a particular state of affairs. An extension in
this sense is a set of relations that satisfies all of the state constraints
specified in the schema of a database. Database instance or state or value
would therefore be synonymous.
The schema of a database describes the set of all possible database values;
whereas the body specifies which of the possible database values is actual,
and indirectly, due to the closed world and domain closure assumptions,
which objects in the universe actually exist. So, under a given
interpretation, the schema of a database specifies what *can be* true,
whereas the body--the actual database value--represents what *is* true.
Note also that there can only be one database value that represents what
*is* true without introducing a contradiction.
The need for surrogates boils down to this:
possibly A and possibly B does not imply possibly (A and B).
The schema of a database is a modal predicate. The extension of that
predicate is a set of positive modal formulae. The problem is that the
presence of a compound key forces the modal predicate to be second-order,
since it must range over all possible objects, including those identified by
a compound key value. So, the extension of such a predicate is not a set of
propositions, but a set of predicates, and the extensions of those
predicates may intersect. So unless
possibly A and possibly B implies possibly (A and B),
a compound key value (A, B) may identify a different object at different
possible database values. It follows then that different compound key
values may identify the same object at different possible database values.
The use of surrogate keys ensures that the modal predicate is first-order,
eliminating any ambiguity.
> It also seems you're suggesting that surrogate key should
> be *globally* unique. Do you mean that? If so, yuck.
>
I'm not sure what you mean by *globally* unique. A surrogate key value
should permanently identify an object in the universe. All surrogate keys
should be drawn from the same domain. If that equates to *globally* unique,
then that's what I mean.
>
>> (Perhaps I should have used the term "individual" instead of "object" to
>> avoid confusion.)
>
> I don't see how one could improve on "proposition" and "predicate."
>
Neither a proposition nor a predicate can stand on its own. Without an
interpretation, each is meaningless.
>
> Marshall
>
Jan Hidders
>
> Here I believe that you misunderstand what is key. I have
> 1. the identifier of the state of an entity or relationship
> 2. the identifier of the entity or relationship.
> I will use shortcut IdSt for first and IdEt for second
> Usually key is IdSt. But IdSt always goes in pair with IdEt.
> These two identifiers have strong semantic and conceptual base.
Could be, but they also might be complete nonsense. For the moment it
is absolutely unclear to me what you mean by these terms. So how about
you giving some proper definition and/or explanation of what they
mean?
Let's, for simplicity, restrict ourselves to entities for the moment.
I understand what it means if entities have certain attributes which
have certain values. The state of an entity is usually defined as the
total description of its attributes, i.e., a function that maps
certain attribute names to their values. Such a state does not have an
identifier, or even need one, since it identifies itself. So that
raises the following questions:
- How do you define the state of an entity?
- How do you define the identifier of the state of an entity?
Ideally these definitions should be in terms of entities and their
attributes. If you are going need other concepts then please define
and explain them properly first.
The notion of identifier of an entity (in a certain entity set) is
usually defined as a set of attribute names such that at no point in
time there can be two entities in the entity set for which the values
of the attributes in this set is the same. Are you using that
defintion, or another one? If so, please define and explain it, also
ideally in terms of entities and their attributes.
Let's restrict the discussion to this for the moment. Once this is
cleared up there is the chance of proceeding in a meaningful way.
-- Jan Hidders
vldm10
> On 14 jun, 12:55, vldm10 <vld...@yahoo.com> wrote:
>
>
>
> > Here I believe that you misunderstand what is key. I have
> > 1. the identifier of the state of an entity or relationship
> > 2. the identifier of the entity or relationship.
> > I will use shortcut IdSt for first and IdEt for second
> > Usually key is IdSt. But IdSt always goes in pair with IdEt.
> > These two identifiers have strong semantic and conceptual base.
I have a impression that our discussion start to be circular. I
started it with suggestion that first we can analyze my two
identifiers, you answered with a suspicion about reinventing ORM. I
checked ORM and found for example that ORM don't talk about
attributes. I talk pretty about the attributes. Then you switched to
critic of my normal form (which I named: Simple Form). I answered on
some your points; you switched focus to two identifiers. Let's try to
focus on every important thing, one by one. I appreciate your
comments, as I told you here - there are knowledgeable and right.
>
> Could be, but they also might be complete nonsense. For the moment it
> is absolutely unclear to me what you mean by these terms. So how about
> you giving some proper definition and/or explanation of what they
> mean?
The names of these identifiers are with exact meaning. This is reason
why the names are so long.
You can find it on my website, where I presented this model. So
theoretical background you can find there (No needs to write it
twice). All terms are very precisely defined or I gave their
construction.
> Let's, for simplicity, restrict ourselves to entities for the moment.
> I understand what it means if entities have certain attributes which
> have certain values. The state of an entity is usually defined as the
> total description of its attributes, i.e., a function that maps
> certain attribute names to their values.
As far as know this mapping defined P. Chen. This is definition of an
attribute, it is not definition of the state of an entity.
>Such a state does not have an
> identifier, or even need one, since it identifies itself. So that
> raises the following questions:
> - How do you define the state of an entity?
> - How do you define the identifier of the state of an entity?
> Ideally these definitions should be in terms of entities and their
> attributes. If you are going need other concepts then please define
> and explain them properly first.
>
> The notion of identifier of an entity (in a certain entity set) is
> usually defined as a set of attribute names such that at no point in
> time there can be two entities in the entity set for which the values
> of the attributes in this set is the same. Are you using that
> defintion, or another one? If so, please define and explain it, also
> ideally in terms of entities and their attributes.
>
> Let's restrict the discussion to this for the moment. Once this is
> cleared up there is the chance of proceeding in a meaningful way.
>
> -- Jan Hidders
I want to emphasize one important thing. When I speak about DB
Solution in my model (on my website or in this discussion) I always
think that the solution is in form of "totally decomposed" or more
precisely DB solution is set of the binary relations in the form:
simple key + one attribute. I named this form: Simple Form. Here, I
believe some misunderstandings can be caused by different view of DB
solution.
I also didn't think to make new DB model, rather some new
constructions and principles. That is why I put model in quotes, but I
will use just model, without quotes - to simplify writing.
Vladimir Odrljin
Jan Hidders
> On Jun 14, 10:23 am, Jan Hidders <hidd...@gmail.com> wrote:
>
> > On 14 jun, 12:55, vldm10 <vld...@yahoo.com> wrote:
>
> > > Here I believe that you misunderstand what is key. I have
> > > 1. the identifier of the state of an entity or relationship
> > > 2. the identifier of the entity or relationship.
>
> > [....] So how about
> > you giving some proper definition and/or explanation of what they
> > mean?
>
> The names of these identifiers are with exact meaning. This is reason
> why the names are so long.
> You can find it on my website, where I presented this model. So
> theoretical background you can find there (No needs to write it
> twice). All terms are very precisely defined or I gave their
> construction.
Well, what you give is a description of how they behave, but you do
not really define them in terms of the universe of discourse. This is
important if you want to claim that you are presenting a conceptual
model because that should satisfy the so-called Conceptualization
Principle which states that conceptual model deals only and
exclusively with aspects of the Universe of Discourse. This excludes
for example implementation concepts. In other words, you should not
introduced artificial identifiers that do not have some kind of well-
defined meaning in the Universe of Discourse. So it is this definition
that I was asking you about.
Of course, perhaps I misunderstand and you are not trying to present a
conceptual model. But even then adding such artificial implementation
concepts is usually a bad idea unless they cannot be avoided or there
are direct and clear benifits. So I first want to establish whether
your concepts are outside the Universe of Discourse or not. So, are
they?
-- Jan Hidders
vldm10
> On 15 jun, 21:42, vldm10 <vld...@yahoo.com> wrote:
>
>
>
>
>
> > On Jun 14, 10:23 am, Jan Hidders <hidd...@gmail.com> wrote:
>
> > > On 14 jun, 12:55, vldm10 <vld...@yahoo.com> wrote:
>
> > > > Here I believe that you misunderstand what is key. I have
> > > > 1. the identifier of the state of an entity or relationship
> > > > 2. the identifier of the entity or relationship.
> > > > [...big snip...]
>
> > > [....] So how about
> > > you giving some proper definition and/or explanation of what they
> > > mean?
>
> > The names of these identifiers are with exact meaning. This is reason
> > why the names are so long.
> > You can find it on my website, where I presented this model. So
> > theoretical background you can find there (No needs to write it
> > twice). All terms are very precisely defined or I gave their
> > construction.
>
> Well, what you give is a description of how they behave, but you do
> not really define them in terms of the universe of discourse.
Once again, all terms are precisely defined or I gave their
construction. They define my vision of Conceptual Model.
Maybe we don't understand each other, because of that here are some my
notice.
In my opinion sometimes *construction* is more important then a
definition or a theorem.
Maybe you didn't pay attention on all details in my model.
Many things in my model are related to your question.
I will mention for example that I defined "constructor" and I think
that on this way
should be created every information which is at attribute level. The
idea is to create
let me say lawful information, not some random information. Same for
the identifier of an entity.
Of course I don't think that we should solve simple DB cases on
complicated way. This is model mostly for complex DBs. My example1 can
be useful for further discussion.
>This is
> important if you want to claim that you are presenting a conceptual
> model because that should satisfy the so-called Conceptualization
> Principle which states that conceptual model deals only and
> exclusively with aspects of the Universe of Discourse. This excludes
> for example implementation concepts. In other words, you should not
> introduced artificial identifiers that do not have some kind of well-
> defined meaning in the Universe of Discourse. So it is this definition
> that I was asking you about.
>
> Of course, perhaps I misunderstand and you are not trying to present a
> conceptual model. But even then adding such artificial implementation
> concepts is usually a bad idea unless they cannot be avoided or there
> are direct and clear benifits. So I first want to establish whether
> your concepts are outside the Universe of Discourse or not. So, are
> they?
>
> -- Jan Hidders- Hide quoted text -
>
> - Show quoted text -
Example1
Let we have the following Car table
CarKey CarID Maker Type Color Datefrom
Dateto
...
23 vin1 Buick sedan silver
1.1.2000. 12.31.2000.
24 vin1 Buick sedan blue
1.1.2001 8.1.2001
25 vin1 Buick sedan red
8.2.2001 1.1.2005.
26 vin1 Buick sedan silver
1.2.2005 999999
27 vin2 Honda sedan silver
3.15.2006 999999
28 vin3 Ford sedan black
1.1.2005 999999
...
Here the CarKey is the Identifier of the state of the entity Car and
this is the
only column of the table which has unique values. So the attribute
CarKey
is the primary key. Car ID is an Identifier of the entity Car. We use
VIN values
for this attribute. Maker and Color are the attributes of the entity
Car.
"999999" means that corresponding data is current.
Here Datefrom and Dateto are strictly related to attribute Color (not
to entity car).
Datefrom and Dateto are not the attributes. They are a part of our
actual
knowledge about the attribute color.
(so here the "The information principle" is not appropriate, because
the entire
information content is not represented as values in attribute
positions, i.e.
here we also represent knowledge about attribute Color )
Now from table Car I will construct the following four tables:
Table1 Table2 Table3
... ... ...
23 vin1 23 Buick 23 sedan
24 vin1 24 Buick 24 sedan
25 vin1 25 Buick 25 sedan
26 vin1 26 Buick 26 sedan
27 vin2 27 Honda 27 sedan
28 vin3 28 Ford 28 sedan
... ... ...
Table4
...
23 silver 1.1.2000. 12.31.2000.
24 blue 1.1.2001 8.1.2001
25 red 8.2.2001 1.1.2005.
26 silver 1.2.2005 999999
27 silver 3.15.2006 999999
28 black 1.1.2005 999999
...
Basically, here I made four "column-based" or "attribute-based"
relations
from the relation in step one.
(the first three tables in fact have one column and key)
Table4 as addition has knowledge about attribute color which is
represented
with two columns (datefrom and dateto). One can add some other
"knowledge-column" related to Color
Table1 Table2 Table3
23 vin1 23 Buick 23 sedan
24 vin1 27 Honda 27 sedan
25 vin1 28 Ford 28 sedan
26 vin1
27 vin2
28 vin3
Table4
23 silver 1.1.2000. 12.31.2000.
24 blue 1.1.2001 8.1.2001
25 red 8.2.2001 1.1.2005.
26 silver 1.2.2005 999999
27 silver 3.15.2006 999999
28 black 1.1.2005 999999
Vladimir Odrljin
vldm10
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
This is additional explanation for Example1 (by mistake, I didn't copy
my last version of example1)
So there are three steps in the example. First step is the starting
table (with columns CarKey, CarID, Maker, Type, Color, Datefrom,
Dateto)
Second step is decomposition from starting relation to the four binary
relations.
Third step is a try to cat "vertical redundancy" in Table2 and Table3.
This third step is posted more as a problem.
For example - is it possible to move from step3 to step2 using
relational algebra technique?
I can do this using procedural approach and table1
Vladimir Odrljin
Jan Hidders
> On Jun 15, 8:42 pm, Jan Hidders <hidd...@gmail.com> wrote:
>
>
>
> > On 15 jun, 21:42, vldm10 <vld...@yahoo.com> wrote:
>
> > > On Jun 14, 10:23 am, Jan Hidders <hidd...@gmail.com> wrote:
>
> > > > On 14 jun, 12:55, vldm10 <vld...@yahoo.com> wrote:
>
> > > > > Here I believe that you misunderstand what is key. I have
> > > > > 1. the identifier of the state of an entity or relationship
> > > > > 2. the identifier of the entity or relationship.
> > > > > [...big snip...]
>
> > > > [....] So how about
> > > > you giving some proper definition and/or explanation of what they
> > > > mean?
>
> > > The names of these identifiers are with exact meaning. This is reason
> > > why the names are so long.
> > > You can find it on my website, where I presented this model. So
> > > theoretical background you can find there (No needs to write it
> > > twice). All terms are very precisely defined or I gave their
> > > construction.
>
> > Well, what you give is a description of how they behave, but you do
> > not really define them in terms of the universe of discourse.
>
> Once again, all terms are precisely defined or I gave their
> construction.
Well, it that is going to be your position I'm afraid the discussion
has to stop here. I've indicated already which terms I think are not
well-defined and you have refused to give any further clarification.
Yes, I did read your web page, and, no, it is not precise enough. So,
since I don't understand your terminology well enough it is simply not
possible for me to continue this discussion in a meaningful way.
However, in the following part you seem to start from the relational
model, which is terminology I understand. So I will try to continue
from there.
That depends on what you call an attribute. If this is a relation then
all columns are attributes and since you have represented all the
information in them you are, as far as the relational model is
concerned, completely faithful to the information principle.
It seems that you are attempting to normalize the relation, but the
result is not exactly the same as that of the standard normalization
procedure. Can you explain why you depart from the standard procedure
and why your procedure gives a better result? Do you think it is
simpeler than the normal procedure?
vldm10
I didn't have time to respond.
As I told you, your question is right and I believe my answer is
good. Maybe your question can be expressed on another way as well as
my answer, but at this moment, I believe it is OK. On my web site I
define construction of these identifiers as well as some other things
related to the identifiers.
> Yes, I did read your web page, and, no, it is not precise enough.
This confuse me. For example you asked me " How do you define the
state of an entity?".
I have this definition on my web. It is given in 5.7 . Also in 5.8. I
gave definition of change of the state of an entity.
> So,
> since I don't understand your terminology well enough it is simply not
> possible for me to continue this discussion in a meaningful way.
Before we complete the discussion about the conceptual level I have
one question.
Here in example1 I gave the identifier of an entity Car. It is about
VIN = "vehicle identification number".
The identifier is not local for Car entity. It is global. Let me say
we have here global lawful information, which is completely accepted
as successful solution in the reality and in every country. You state
that "...adding such artificial implementation concepts are usually a
bad idea..." Can you explain why VIN is a bad idea? Otherwise I can't
accept your position about the identifiers.
Here are some notices related to the identifiers. These notices are
not related to your question mentioned above, they are additional
aspects about the identifiers.
1)
I use identifier in sense just to identify something. I didn't use
term like name, which can draw much more things. For example a name of
somebody parents, can draw memories, emotions etc.
2)
This example is about Sir Walter Scott who is the author of Waverley.
The example is due to B. Russell.
At diner at which W. Scott was present, a person X asked W. Scott "Are
you the author of Waverley"?
W. .Scott answered: "Sire, I am not the author of Waverley". (W. Scott
didn't want to say the truth, that he is the author of Waverley)
Now we have the two semantics. The person X thinks that this is not
Sir Walter Scott.
However W. Scott didn't want to say that he is not W. Scott. He
didn't want to say "I am not I", despite he didn't say the truth
regarding the author of Waverley.
This well known example says that identifying by role of an object can
be a bad idea. Here the role is "the author of".
With this example I didn't mean to speak about the role of an object
in NIAM/ORM theory. This is about identifying.
3)
In recent time I noticed in this NG the sentences like "numeric id
column". As id is in fact identifier, we can set the question - what
is numeric identifier. Are we use the numbers as the identifiers. And
which numbers? Is it possible to use real numbers for identifiers? For
example "pi". Or we can use integers? Theoretically it can be much
identifiers. So there is question regarding construction of much
numbers.
Maybe here we can't speak about the numbers as the identifiers, but we
can speak about using the names of the numbers as the identifiers. And
again, which numbers - real or integers - and there is question about
the construction, this time it is the construction of the names of the
numbers. If we are speaking about the identifiers related to the
entities or to the events or to knowledge, then in my opinion this
matter can be very complicated.
I am using my definition of the attribute. This definition is on my
web site under 5.2.
> If this is a relation then
> all columns are attributes and since you have represented all the
Can you please, tell me what is the definition of the attribute in RM.
I didn't notice this definition in RM.
> > Vladimir Odrljin- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Vladimir Odrljin
Vadim Tropashko
> On Jun 18, 7:41 pm, Jan Hidders <hidd...@gmail.com> wrote:
> > If this is a relation then
> > all columns are attributes and since you have represented all the
>
> Can you please, tell me what is the definition of the attribute in RM.
> I didn't notice this definition in RM.
Perhaps it is the standard set theory defintion?
Lets move the letter M to one position forward, so that RM becomes RL.
In the RL an [atomic] attribute is defined as an empty relation A such
that there doesn't exist relation X such that
A > X > 00
Jan Hidders
>
> On Jun 18, 7:41 pm, Jan Hidders <hidd...@gmail.com> wrote:
> >
> > So,
> > since I don't understand your terminology well enough it is simply not
> > possible for me to continue this discussion in a meaningful way.
>
> Before we complete the discussion about the conceptual level I have
> one question.
> Here in example1 I gave the identifier of an entity Car. It is about
> VIN = "vehicle identification number".
> The identifier is not local for Car entity. It is global. Let me say
> we have here global lawful information, which is completely accepted
> as successful solution in the reality and in every country. You state
> that "...adding such artificial implementation concepts are usually a
> bad idea..." Can you explain why VIN is a bad idea?
You misunderstand. There is nothing wrong with VIN if it is a natural
concept that was already present in your Universe of Discourse. What I
object to is the artificial addition of such concepts if they are not
already present. The problem with your definitions is that they are
not precise enough to say whether they are or not.
> > If this is a relation then
> > all columns are attributes and since you have represented all the
>
> Can you please, tell me what is the definition of the attribute in RM.
The column of a relation. Conceptually it models a role in the
predicate that is associated with the relation.
-- Jan Hidders
Bob Badour
> On 21 jun, 21:17, vldm10 <vld...@yahoo.com> wrote:
>
>>On Jun 18, 7:41 pm, Jan Hidders <hidd...@gmail.com> wrote:
>>
>>>On 18 jun, 20:59, vldm10 <vld...@yahoo.com> wrote:
[snip]
>>>If this is a relation then
>>>all columns are attributes and since you have represented all the
>>
>>Can you please, tell me what is the definition of the attribute in RM.
>
> It's a column of a relation. Conceptually it represent the role in the
> predicate that is represented by the relation.
Is an attribute a predicate variable of some sort?
David Cressey
> You misunderstand. There is nothing wrong with VIN if it is a natural
> concept that was already present in your Universe of Discourse. What I
> object to is the artificial addition of such concepts if they are not
> already present. The problem with your definitions is that they are
> not precise enough to say whether they are or not.
The problem here is with the word "natural". The VIN is artificial in the
sense that it is man made. It's connection to the vehicle it identifies is
a matter of convention rather than observation. To be sure, little tags
showing the VIN are attached to the vehicle, but this is mere labelling, I
think you'll agree.
Nevertheless, I have to agree that it is present in our Universe of
Discourse, and that functionally, it identifies a vehicle. So we can treat
it as if it were "natural" with no consequence.
The problem arises when the Universe of Discourse contains an entity whose
identity is marked by an informal mechanism that is subject to error. For
example, if we had a small college where students are identified by first
name and last name, "because numbers are too impersonal", we would still
be faced with identifying students in the database. The humans who are
responsible for the Universe of Discourse as presented to us adopt
workarounds for the defects in their identification scheme. They summarize
these workarounds as "applying human intelligence". This does not lend
itself to sane database design.
So, sometimes, we are forced to extend the U of D so as to formalize it,
prior to mapping the U of D onto database design. Whether we call these
extensions to the U of D "surrogate keys" or not is moot.
Jan Hidders
>
> So, sometimes, we are forced to extend the U of D so as to formalize it,
> prior to mapping the U of D onto database design. Whether we call these
> extensions to the U of D "surrogate keys" or not is moot.
Agreed. Avoid them if you can, but use them if you must. But
Vladimir's method seems to dictate (but I could be wrong, he is not
explicit on this) that they are always introduced because his method
rests upon their presence. In fact, he introduces two of them, one for
identifying the entity accross several diferent states, and one for
identifying the entity in a certain state. The latter identifier is
generated new for each update on the state of the entity (so, if the
entity returns to the same state, it still gets a different state
identifier). This is great if that is what the organization wants to
record in the database, but it it isn't then you are over-modelling.
Also note that he adds these two identifiers to the same entity,
whereas it would make more sense to split the entity into two: one for
the abstract entity that describes the time-invariant attributes and
one for the concrete entity that describes the time-variant attributes
that make up its state.
-- Jan Hiders
Jan Hidders
As in "the x in predicate P(x, y) "? Yes, I'd say that is the same
thing.
-- Jan Hidders
Bob Badour
I thought so. When one writes a query and requests certain attributes,
would those requested attributes be free variables in a logic sense?
When one uses an attribute in a restrict condition or join condition,
would that attribute be a bound variable in a logic sense?
Jan Hidders
> Jan Hidders wrote:
> > On 22 jun, 01:36, Bob Badour <bbad...@pei.sympatico.ca> wrote:
>
> >>Jan Hidders wrote:
>
> >>>On 21 jun, 21:17, vldm10 <vld...@yahoo.com> wrote:
>
> >>>>On Jun 18, 7:41 pm, Jan Hidders <hidd...@gmail.com> wrote:
>
> >>>>>On 18 jun, 20:59, vldm10 <vld...@yahoo.com> wrote:
>
> >>[snip]
>
> >>>>>If this is a relation then
> >>>>>all columns are attributes and since you have represented all the
>
> >>>>Can you please, tell me what is the definition of the attribute in RM.
>
> >>>It's a column of a relation. Conceptually it represent the role in the
> >>>predicate that is represented by the relation.
>
> >>Is an attribute a predicate variable of some sort?
>
> > As in "the x in predicate P(x, y) "? Yes, I'd say that is the same
> > thing.
>
> I thought so. When one writes a query and requests certain attributes,
> would those requested attributes be free variables in a logic sense?
That's not how I would formulate it. Strictly speaking the attribute
is a position or role in a predicate. So if the predicate is P(x, y)
then there are two positions / attributes: the first position where x
is bound and the second position where y is bound. It doesn't really
make sense to say that the variable is the attribute because how then
would you describe the situation in the formula "P(x, y) and Q(x, x)"?
Is the variable 'x' an attribute? It makes more sense to say that it
is a variable that is bound to the first position / attribute of P and
the first and second posiiton / attribute of Q.
So, er, I guess my previous answer should actually have been "no, not
exactly". ;-)
-- Jan Hidders
Bob Badour
Thank you, that clarifies things for me.
David Cressey
"Bob Badour" <bba...@pei.sympatico.ca> wrote in message
news:467bbfa9$0$4321$9a56...@news.aliant.net...
I don't know what the term "free variable" means in the world of logic. I
do know what it means in the context of Lisp programming. (Or, at least, I
used to know). So far, the use of "free variable" in these discussions
seems to be consistent with the Lisp usage.
Jan Hidders
David Cressey schreef:
There's different meanings of the term "bound". The notion of free
variable is simply defined as "not bound by a quantifiers". So in the
formula "forall x : p(x,y)" the variable x is bound but y is free. But
at the same time it is sometimes said that "y is bound to the second
position of predicate p". So there is "bound by a quantifier" and
"bound to a position in a predicate" and only the first is the
opposite of the usual notion of "free". Hopefully that clears it up a
bit.
-- Jan Hidders
vldm10
> On 21 jun, 21:17, vldm10 <vld...@yahoo.com> wrote:
>
>
>
>
>
>
>
> > On Jun 18, 7:41 pm, Jan Hidders <hidd...@gmail.com> wrote:
>
> > > So,
> > > since I don't understand your terminology well enough it is simply not
> > > possible for me to continue this discussion in a meaningful way.
>
> > Before we complete the discussion about the conceptual level I have
> > one question.
> > Here in example1 I gave the identifier of an entity Car. It is about
> > VIN = "vehicle identification number".
> > The identifier is not local for Car entity. It is global. Let me say
> > we have here global lawful information, which is completely accepted
> > as successful solution in the reality and in every country. You state
> > that "...adding such artificial implementation concepts are usually a
> > bad idea..." Can you explain why VIN is a bad idea?
>
> You misunderstand.
Here I am speaking about identifying. It is identifying the entities
(relationships) and the state of an entity.
They are basic things for DB, not an artificial addition. The entity
is always present in DB. This is identyfying of the entity represented
by its attributes.
I accept entity as it is defined in ER model i.e. that an entity is
defined with its attributes. But I gave definition of the state of an
entity, which is not defined only by the attributes.
Our mind can identify these abstractions separately and keep it as
totality.
The identifiers can be applied both, one or neither, depending on
purpose, needs, db solution etc.
Of course I am aware that even a variable has its universe and your
notice about Universe of Discourse is OK, but it is not essential for
identifying entities as abstraction as well as identifying the real
objects which we abstract as the entities.
>There is nothing wrong with VIN if it is a natural
> concept that was already present in your Universe of Discourse. What I
> object to is the artificial addition of such concepts if they are not
> already present. The problem with your definitions is that they are
> not precise enough to say whether they are or not.
>
> > > If this is a relation then
> > > all columns are attributes and since you have represented all the
>
> > Can you please, tell me what is the definition of the attribute in RM.
>
> The column of a relation. Conceptually it models a role in the
> predicate that is associated with the relation.
>
> -- Jan Hidders- Hide quoted text -
vldm10
No, I am not doing normalization, rather it is a construction.
Example1 is presented in the three steps because of better
understanding what I am doing. In real situation I have only step2 or
step 3 i.e. I immediately construct table1, table2, table3, table4 and
a data entry screen(s). This is based on the corresponding entity
(conceptual level). This entity should have 1) simple key 2) the
mutually independent attributes 3) the attributes only from one
entity.
> Can you explain why you depart from the standard procedure
> and why your procedure gives a better result? Do you think it is
> simpeler than the normal procedure?
There are some advantages in my approach. I will mention one. It has
representation on attribute level or on information/data level. I
beleive that this aproach is more natural and better for DBs and RM
then for example XML approach.
>
> -- Jan Hidders
>
>
>
>
>
> > Vladimir Odrljin- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Vladimir Odrljin