Mitch Sherman wrote:
> On Thu, 15 May 2008 12:01:02 -0400, Jerry Stuckle
>
attglobal.net> wrote:>>> On May 14, 7:21 pm, Mike Lahey
yahoo.com> wrote:>>>> Jerry Stuckle wrote:
>>>>
>>>>> No argument.
>>>>> But that was an additional condition the poster required - not the
>>>>> original op. And that's what makes it incorrect.
>>>> Uniqueness is a consequence of the relationship the OP wanted to model.
>>>> Best practice is to create an index, which is the correct solution, as
>>>> has been pointed out several times.
>>>>
>>>> You should properly normalize your DB instead of working around a broken
>>>> design as you're arguing for.
>>> Amen. Any proposed solution that skips this step is incomplete. One
>>> shouldn't rely on a broken data model and expect to get good results.
>>>
>> No arguments. But based on the information given, we cannot say the
>> database was not normalized.
>
> Normalizing it first can do no harm and is certainly an improvement. A
> relational table doesn't need redundant rows.
>
And there is nothing to say the table has redundant rows. Again - this
may be the table structure - we don't know:
userid groupid permission
1 1 read
1 1 write
1 1 delete
1 2 read
1 3 read
There is no redundant information. However, there are duplicates for
(userid, groupid). The permissions column was not pertinent to the
original ops query, and therefore may not have been listed. In fact,
there could have been 20 additional columns. But since they were
irrelevant to the query, the op would not have listed them (which would
be the correct thing to do).
>>>> The OP wanted to indicate membership in a group. A membership relation
>>>> does not contain duplicates.
>>> Yes, by definition, a membership set has no dups. To take another
>>> example, it wouldn't be proper for a student to belong to the same
>>> class twice. (He could repeat the course, but that wouldn't be the
>>> same class would it.)
>>>
>> It depends. For instance, you could have an additional column -
>> privileges. Things like "read", "post", "upload" to determine the
>> rights the user has.
>
> Doesn't matter. Each row is still unique. Why would you specify the
> same "rights" twice when once is enough? There is no relational design
> that you can postulate that requires redundancy. This can always be
> eliminated.
>
Yes, each row is unique. But (userid, groupid) is NOT unique. See above.
>>> Using a flawed db design creates all sorts of inconsistencies which
>>> are better to avoid when developing robust systems.
>>>
>>> Jerry's suggested query blows up when faced with duplicates, so you
>>> can see how easy it is to fall into this trap.
>>>
>> My query does not blow up with there are duplicates. It works perfectly
>> well. But Peter's fails in that case.
>>
>> And people wonder why I refer MySQL questions to comp.databases.mysql -
>> where the real experts hang out.
>
> There have been several posts pointing out your error, but you seem
> desperate to cling to the idea that this is a "bug in MySQL" rather
> than a flawed design. You made the same mistake Peter warned you
> against. His approach is superior to yours because it both a)
> normalizes (by removing dups) and b) optimizes by creating an index.
> Your solution does neither and does not even properly handle duplicate
> rows.
>
> Mitch
>
And I have posted the code proving it works. Additionally, there are a
number of SQL experts over in comp.databases.mysql who also agree this
is the correct thing to do.
Now you know why I send people with SQL questions to
comp.databases.mysql. They get good answers there - not the crap given
out by rank amateurs who think they know everything that you see here.
I suggest you take this up over in comp.databases.mysql. Tell the
developers of MySQL why this won't work. They will disagree with you, also.
Shit - I have never seen so many idiots argue something is wrong without
even trying it - just because they have no f'ing idea what they're
talking about.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@
attglobal.net
==================
