On 30/11/2007, Lincoln Yeoh <lyeoh@xxxxxxxxxxxxx> wrote:
Hi,--- post follows ---
Found this post on Slashdot which I found interesting, any comments?
by Anonymous Coward on Wed Nov 28, '07 03:23 PM (#21509173)
Speak for your database -- postgresql does.
Postgresql's "table inheritance" is a flawed concept and has nothing to do
with the *type system*. Relations contain tuples, and tuples contain
attributes, which are a name plus a VALUE. Those values are chosen from TYPES
(sets of possible values). Those types are the TYPE SYSTEM.
Table inheritence doesn't even make sense. Tables are analogous to relations.
All relations are the same type, the relation type (think "set" or "array" to
make it easier). How can one value of a type (one table) be a subtype of
another value (another table)? That's like saying, "3" is a subtype of "5",
if your types are integers. What if you use the _expression_ "3+2" Is that "5"
still the subtype of 3? likewise, when you make complex queries with a "base"
table, does the result have any connection with the "sub" table? It's like
gobbledygook, just mashing words together without any understanding. That's
why the postgresql table inheritance concept doesn't see more widespread use.
Many people quickly discover the limitations (and incorrectly think it's just
"unfinished", when it actually is flawed).
The correct way to store types and subtypes in the database is to store them
in the columns. In other words, choose attribute VALUES from a TYPE SYSTEM.
Nothing else in the relational model needs to be changed. Something like
this, in hypothetical SQL-like language:
CREATE TABLE People ( INT id, PERSON_CLASS person )
p1 = PERSON_CLASS.new(name: "joe", etc)
p2 = CUSTOMER_CLASS.new(name: "bob", etc) // assume CUSTOMER_CLASS
subclass of PERSON_CLASS
INSERT INTO People VALUES (1, p1), (2, p2)
SELECT person FROM People WHERE person.name = "bob"
SELECT person, order FROM People JOIN Orders // can't do this in the
typical "object database"
This is a "solved problem" (see "The Third Manifesto"). It's just a matter of
getting somebody to implement it. But the vendors are clueless, thinking
object databases are a "different model" and not wanting to confuse
programmers, and programmers are clueless, not even understanding SQL or
types and values half the time, so they don't demand anything new from
vendors... we never move forward.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Seams like two completely different concepts are getting confused. ie that of Table Inheritance and that of Type Inheritance. They are completely different concepts.
Table Inheritance is table structure ie a child table has all the same columns as the old one with some added columns that sore specialist items. This feature is used heavily used in Table Partitioning. Perhaps it should be renamed.
Type Inheritance is adding extra features to types eg
Varchar(5) is a child of text that adds a maximum length limit of 4 and char(5) is a type of text with a fixed length of 5. But they are all text. This is a very silly example.
Just thoughts.
Peter.