Re: Stupid Database Tricks

On Jun 9, 11:17 am, vldm10 <vld..._at_yahoo.com> wrote:
> 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.

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 Received on Sat Jun 09 2007 - 22:21:56 CEST