Re: Help writing SQL statement in PHP script

On May 15, 9:52 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
Mitch Sherman wrote:
On Thu, 15 May 2008 11:55:29 -0400, Jerry Stuckle
<jstuck...@xxxxxxxxxxxxx> wrote:
I'm not arguing about proper database design. My only comment is it is
IMPOSSIBLE to determine if the database is normalized or not from the
given information.

That doesn't mean that the relation can't be normalized first. That
seems to be the critical point you're missing.

No, the critical point YOU'RE MISSING is that the table may be
normalized - AND STILL HAVE DUPLICATES IN THESE COLUMNS.

That is the critical point!

Totally incorrect. I refer you to the Wikipedia definition:

A table is in first normal form (1NF) if and only if it faithfully
represents a relation.[3] Given that database tables embody a relation-
like form, the defining characteristic of one in first normal form is
that it does not allow duplicate rows or nulls.

It would be nice if you knew something about database normalization
before you started pontificating about it. Duplicates are forbidden.
Your whole premise is based on allowing duplicate rows.


You seem to arguing that it's better to build on a potentially flawed
database design rather than get it right first, which is terrible
advice.

No, I'm not. There is nothing flawed about a design which has three
columns (of which these are only two) determining the primary key (or
other unique value).

There could be one or more additional columns to determine uniqueness, for instance.

That's not the design in the op's problem.


And people wonder why I send folks to comp.databases.mysql for MySQL
questions - that's where the REAL experts hang out.

This is a pointless hypothetical. If you have N columns, you can still
maintain uniqueness across those columns. That doesn't require
duplicate rows any more than the original problem which had only 2
columns.

Mitch

No, it is not pointlessly hypothetical. It is very germane to this
situation. We do not have all of the information - the complete
database design, usage, etc.

If my aunt had balls, she'd be my uncle!

The other column(s) may not be germane to the problem, so the original
op did not list them. That is quite common - and correct - as it does
not confuse the issue at hand with irrelevant data. There may very well
have been 2 columns - or 20 columns or even 200 columns. You don't know
which is correct.

And there would still be no duplicates if you normalized it.

For instance, here's a table which could very well be the case:

userid groupid permission
1 1 read
1 1 write
1 1 delete
1 2 read
1 3 read

This is a commonly used design. The permission column is not pertinent
to the original ops question - so it wouldn't be listed. But Peter's
query will fail if it looks for someone who is a member if groups 1 and
2. The correct query works in this case just fine.

Wrong. Your example has no duplicates at all. Therefore it does not
support your assertion that a table with duplicate is somehow
"normalized."

If you add columns, then obviously you have to add those to the group
by statement in Peter's solution. You would need to do the same with
your query as well. It's pointless to change the table design and then
expect the same queries to work. Try restricting your argument to the
actual problem the op posed, instead of some hypothetical.

My God, I've never seen someone so insistent about making false
assumptions about someone else's code - and so stubborn about sticking
to a bad suggestion.

Thank you for describing your position so accurately. I couldn't have
done it better myself.

I really suggest you learn some more advanced sql - actually, the
correct answer isn't even advanced level. I'm not sure it even makes
intermediate level.

The correct query works 100% of the time - whether there are duplicates
or not.

Yes, it works 100% of the time, except in those cases where it doesn't
work at all.

Ron Doyle [oxision at yahoo.com]
.

Relevant Pages

  • Re: Help writing SQL statement in PHP script
    ... IMPOSSIBLE to determine if the database is normalized or not from the ... normalized - AND STILL HAVE DUPLICATES IN THESE COLUMNS. ... I've been doing database normalization for over 20 years. ... The correct query works in this case just fine. ...
    (comp.lang.php)
  • Re: Database Design & Normalization Question
    ... Products, Other Titles, Title Types. ... However, oftentimes bad design will kill performance, no matter ... Normalization would dictate that these dates would depend on the KEY, ... > didn't have to change the database design to store new ...
    (microsoft.public.sqlserver.server)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... information (my understanding of what a normalized database is). ... Or, is there ever a time that you want redundant keys (that is, the ... The answer is yes, there are times when a design is a good one, ... For each normalization form, ...
    (comp.databases.theory)
  • RE: Northwind Template
    ... hand you'd learn more about database design as you go along if you start from ... I would not create a column for your structured CustomerID. ... entire database, but I will give it a try. ... example of its lack of proper normalization can be found in its Customers ...
    (microsoft.public.access.gettingstarted)
  • Re: Database Design Problem
    ... Design is a hard thing to do if you want a good design that is. ... because of bad database design. ... There are lots of normalization rules ... and why I include the customer PO in the OrderDetails is because ...
    (microsoft.public.sqlserver.programming)