Actually, for oce Norm is far more right than wrong; you...

...OTOH, are vicey-versy.

bob the poet writes:

[Quoting Norm:]
I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed.
??? First of all, I think you mean "design of a table (or perhaps 'these two tables')",

Well, no -- it is true of ALL databases that ANY two tables in them that you intend to use in a "master-detail" fashion should be linked by a field that is unique in the "master" table. (And since a primary key, by its very nature, won't allow any duplicates, it is the obvious candidate for this.)

If, as a not-even-very-charitable reading suggests, Norm is talking about *only* the case we have here -- how to properly connect two tables _in a "master-detail" relationship_ -- then he is perfectly correct not only in this instance, but for this kind of relationship in all (SQL-based) databases in general.

and second, that's not true for all tables.

No, but then we weren't talking about "all" tables in general, but more specifically those that are involved _in a "master-detail" relationship_.

If the "proper" design were "no duplicates allowed", then why have the option at all?

For indexes on fields that are *not* used to link a "detail" table to a "master" one, of course.

And for indexing the link field in the *"detail"* table, because it is the nature of a "master-detail" relationship that there *can* be many "detail" records for each "master" record... The uniqueness requirement is only on the "master" table, because there can be only one "master" record for each "detail" record.

Because some sets are not atomistically unique.

Huh?!? Idunno, suddenly you're using words like "set" (and, Bog help me, "atomistically unique"! WTF???)... Are you talking of "sets" of values of fields *other* than the one used to link a "detail" to a "master" table?

'Coz if you are, you aren't talking about the same thing as anyone else in this thread. (And besides, if you're talking about "sets" of *complete* rows from a table, you're factually wrong here too.)

Bobby B writes:

It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that.

Well, we actually know (now) that they *aren't* in a "master-detail" relationship, if by that you mean they'd have to have referential integrity constraints defined on them...

But on the other hand, they *are* in a "master-detail" relationship in the sense that that's how they're being used in BOx's query (if I understood it correctly); they are *being* "related" by the JOIN in the query.

At least the latter of my two paragraphs/sentences above is no assumption, on Norm's or anyone else's part.

And that the latter *should* (as in, "ought to") imply the former is also more of a reasoned opinion than an assumption. :-)

Post #107,682 by CRConrad 6/28/03 9:30:20 AM Reply	Actually, for oce Norm is far more right than wrong; you... ...OTOH, are vicey-versy. bob the poet writes: [Quoting Norm:] I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", Well, no -- it is true of ALL databases that ANY two tables in them that you intend to use in a "master-detail" fashion should be linked by a field that is unique in the "master" table. (And since a primary key, by its very nature, won't allow any duplicates, it is the obvious candidate for this.) If, as a not-even-very-charitable reading suggests, Norm is talking about only the case we have here -- how to properly connect two tables _in a "master-detail" relationship_ -- then he is perfectly correct not only in this instance, but for this kind of relationship in all (SQL-based) databases in general. and second, that's not true for all tables. No, but then we weren't talking about "all" tables in general, but more specifically those that are involved _in a "master-detail" relationship_. If the "proper" design were "no duplicates allowed", then why have the option at all? For indexes on fields that are not used to link a "detail" table to a "master" one, of course. And for indexing the link field in the "detail" table, because it is the nature of a "master-detail" relationship that there can be many "detail" records for each "master" record... The uniqueness requirement is only on the "master" table, because there can be only one "master" record for each "detail" record. Because some sets are not atomistically unique. Huh?!? Idunno, suddenly you're using words like "set" (and, Bog help me, "atomistically unique"! WTF???)... Are you talking of "sets" of values of fields other than the one used to link a "detail" to a "master" table? 'Coz if you are, you aren't talking about the same thing as anyone else in this thread. (And besides, if you're talking about "sets" of complete rows from a table, you're factually wrong here too.) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #107,696 by tseliot 6/28/03 1:13:30 PM Reply	We're not disagreeing here. You're just using more words than I did. It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,706 by CRConrad 6/28/03 2:58:36 PM Reply	Ah... that "assumption"! :-) Bobby B writes: It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that. Well, we actually know (now) that they aren't in a "master-detail" relationship, if by that you mean they'd have to have referential integrity constraints defined on them... But on the other hand, they are in a "master-detail" relationship in the sense that that's how they're being used in BOx's query (if I understood it correctly); they are being "related" by the JOIN in the query. At least the latter of my two paragraphs/sentences above is no assumption, on Norm's or anyone else's part. And that the latter should (as in, "ought to") imply the former is also more of a reasoned opinion than an assumption. :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]

Welcome to IWETHEY!