Re: Question on Structuring Product Attributes

>> Response please. <<

>> traced back to Britton-Lee. And yes, they always had a few Waterloo grads on staff. brings back memories. <<

I never worked with the Britton-Lee machines , but wish I had. I have a rule of thumb to look for software from University of Waterloo. Remember the C compilers? It produced executable 1/3 the size of Microsoft, conformed to standards and had a lot of nice stuff. But the “Watrerloons” (is that still the slang?) could not write a human interface. It was as if they only took hardcore comp sci, math and optimizer courses.

>> [ by building pointer chains under the covers, so each compound key actually appears once in the schema.] Excuse me, but:

1. You posted SQL. I answered SQL. In order to maintain focus, avoid tangents, and close this subject like gentlemen, let's talk SQL only.
2. SQL is a standard, anything that does not comply with the Standard cannot make the claim to be an SQL. The SQL standard requires that all references in the database are by *KEY*, and specifically *NOT* by pointers. <<

Not so. The standards use (over-use?) the term “effectively” to describe the results and not the method. You can use pointers, indexing in all its flavors, hashing (lot of academic stuff on Minimal Perfect Hashes for DB right now), or brute force table scans (looking good for SSD and massive parallelism). Pointers and indexing are simply the most popular because SQL products grew out of existign storage engines and file systems.

Around the time of the SQL-89 standard one of vendors on the old ANSIX 3H2 committee waned to propose a CREATE INDEX statement. The X\Open consortium has such syntax in their portability guide and the X3H2 committee overlapped with them quie a bit. The proposal was voted down on the grounds that a CREATE INDEX / CREATE HASH / CREATE SURROGATE_KEY / CREATE MAGIC_GENIE were too close to a physical implementation. The goal was to make the Standards as abstract as possible so that ANY implementation would be conformant.

What Standards have and implementation do not is a generalize CHECK() constraint and the CREATE ASSERTION statement that can reference any table in the schema with aggregates. The CREATE ASSERTION is why the <name> in “CONSTRAINT <name> CHECK(..)” has to be globally unique. Originally, the CREATE ASSERTION was invented because all constraints on a empty table are TRUE and therefore you cannot force a table to contain data. Nice from a pure logical viewpoint (Existential Import and all that jazz), but not so good for piratical programming with mandatory relationships.

>> Teradata is not SQL. <<

It is as much ANSI/ISO SQL as any other commercial product, but they tend to have Data Warehouse extensions because of their market and optimize for huge amounts of data. The bad part of taching at a Teradata class is that I am the only guy in the with only a Masters in Math and everyone else is PhD in Stats.

>> Sybase, MS, DB2, all create Standard SQL visible indices from data columns, from the SQL command that you supplied. <<

Nope, index as NOT standard. DB2 is my favorite; lots of platforms, good conformance, easy to use. I remember Sybase as very good but I have not done any financial work in years where they shine. MS is always a decade behind, but fine for its niche (the programmers are the problem here; no really).

>> Oracle is non-compliant, in that it twists the SQL definitions in order to appear compliant. It famously uses ROWIDs, above and below the covers, but the indices and data columns are visible, above the covers. <<

I can write Oracle when I have to, and I can USUALLY read it in spite of the dialect. But in addition to your remarks, let me add that to keep it tuned is a full-time job with 300+ parameters to watch. If you can figure it out, it runs like a charm; one parameter wrong and all bets are off. My big gripe with Oracle programmers is the over-use of cursors. They might as well be ina file system in COBOL. Rant, Rant,Rant!

>> [What do you have against the Nested Sets?] There is a recent thread on that subject. I will take it up there. <<

I will cruise over to it when I get my head above water. I need to spend Sunday and Monday catching up with an on-line free course and cleaning the garage. Oh, you are being too polite; I like an adversal dialogue:) My wife is an ordained Soto Zen Priest and she teaches by beating students with a stick. Received on Sun Oct 16 2011 - 01:58:40 CEST