Re: Stupid Database Tricks

On Jun 5, 6:03 pm, Marshall <marshall.spi..._at_gmail.com> wrote:
> On Jun 5, 12:47 pm, vldm10 <vld..._at_yahoo.com> wrote:
>
>
>
>
>
> > On May 22, 11:59 pm, Marshall <marshall.spi..._at_gmail.com> wrote:
>
> > > On May 22, 1:48 pm, Bob Badour <bbad..._at_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.

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......"

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 -
Received on Sat Jun 09 2007 - 20:17:11 CEST