Re: How do I order by some of the rows in a column. Not all.

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0110130721.4e66aad5_at_posting.google.com>...
> 1) Please post DDL and sample data, so people don't hav to guess aobut
> constriatns, datatypes, keys, etc.

That's what Jacob did.

> 2) Please read a book on relational design or take a course. This
> thing is not even in First Normal Form. You are trying to meta-data
> into an SQL database for an OO design. IT DOES NOT WORK! I know; I
> get paid to fix these schemas.

Metadata can be stored in a SQL database like any other data. Let's not be too harsh and let's consider Jacob did have a reason to have this approach. There's nothing wrong with storing metadata in a SQL database, it is the recommended way and the practical way to store metadata.

Metadata is data after all , and we don't know if it's for an OO or whatever design. I would assume that Jacob's schema is easily even in the Fifth Normal Form. The quality of a schema can be judged only when you know fully the business rules and the domain that schema is trying
to support. Identical schemas can be in the 2nd 3rd 4th or 5th normal form.
But any schema that can be supported by a SQL database is at least in the 1NF.

Whether it works or not is totally a different question, and we can't judge
that yet.

> 3) Stop using meanignless names like "id" - identifier of what??
> Next, you will be using system generated id numbers and huge
> VARCHAR(n) columns, which will destroy your schema. "Property" is not
> an attribute, so it cannot be a column in a table. "value" is both
> vague (value of what?) and meaningless -- a value is what all the
> columns hold.
>
> Here is a relational design:
>
> CREATE TABLE ProductGroups
> (group_id INTEGER NOT NULL PRIMARY KEY,
> group_name VARCHAR (30) NOT NULL);
>
> CREATE TABLE Products
> (upc DECIMAL(10,0) NOT NULL PRIMARY KEY, -- or use EAN
> group_id INTEGER NOT NULL
> REFERENCES ProductGroups(group_id),
> title VARCHAR (30) NOT NULL DEFAULT '{{no title}})'
> ... );

So if Jacob's application is selling products such as books and needles
do you think that needles with the title ''NO TITLE'' is something relational and/or rational ?
What shall we do about attributes such as Author and ISBN and Size and NumberOfPages and so on ?

The right thing was to ask Jakob for more information as to what lead him to his design - which is something to be avoided in general - before condemning him apriori.

And responding to Jacob's question ( he didn't ask how bad his schema was)
I would guess that an ORDER BY PP.VALUE should do the trick since the Product_Property tuples are already restricted to property_id=1 (probably that corresponds to
property_name='title');

> >> If I want to find books with titles like "SQL" I use the following
> query:
>
> SELECT DISTINCT P.ID AS ID
> FROM PRODUCT P, PRODUCT_PROPERTY PP
> WHERE P.ID = PP.PRODUCTID
> AND P.PRODUCTGROUPID = 1
> AND PP.PROPERTYID = 1 AND UPPER(PP.VALUE) LIKE '%SQL%'; <<
>
> Compare this to:
>
> SELECT upc
> FROM Products AS P1
> WHERE title LIKE '%SQL%'
> ORDER BY title;
>
> Every attribute will require 2 or 3 tables, every query will grow and
> grow, almost exponentially and be impossible to maintain. The
> response time will slow down. But the schema has no constraints or
> checking, so a single data error destroys the entire schema. What if
> you have two rows with 'title' wth different id numbers?

That probably means every new attribute to be included as a search criterion
would add another instance of the PRODUCT_PROPERTY table to the join. If the business requirements are that the end user cannot specify more than 2 or 3 search criteria, his approach is perfectly reasonable. The slowness of the response depends on the smartness of the query optimizer.

Given the fact that normally all the tuples in Product_Property table having the same product_id can be made to fit closely in 1 or 2 disk pages,
performance should not be a problem. Received on Sun Oct 14 2001 - 08:32:34 CEST