On Tue, 2002-08-13 at 10:16, Curt Sampson wrote: > On 12 Aug 2002, Greg Copeland wrote: ...
> > Are we then assuming that tuples in the child tables do not appear > > > in the base table? That's more or less what I'd assumed when I > > > originally heard about table inheritance (after all, instantiating > > > a child object does not automatically instantiate a separate copy > > > of the parent object), Tuples in the child table "appear" in parent table when you do a plain SELECT, as thei IMHO should, because you _do_ want to get all kinds of animals when doing select from animals. They do not appear in parent table when you do SELECT .. FROM ONLY parent_table It used to be the opposite (one needed to do "SELECT .. FROM parent_table* " to get tuples from inherited tables as well ) but it was changed because SQL99 mandated that inherited tables should be included by default. That's for SQL99's "CREATE TABLE (...) UNDER another_table" kind of single inheritance. > > > but the SQL standard, postgres, and I believe other > > > systems make the exact opposite assumption. > > > > That's actually my exact assumption...that is, that tuples in the parent > > did not exist in the child. > > Sorry, by "opposite assumption," I meant these two opposites: There are two main orthogonal ways of mapping inheritance to relational model. > 1. Tuples in child tables appear in the parent table. That's the way you implemented the samples in the beginning of this thread, i.e. keep the common part in one table and extend by stitching columns fron child tables to the "side" using foreign keys. This makes it easy to enforce primary keys and uniqueness, but grows ugly quite fast if you have deep inhweritance hierarchies - if you have inheritance 5 levels deep, you need 4 joins to get a tuple from the last-descendant table. It also makes automatic updating ov views a pain to do. > 2. Tuples in child tables do not appear in the parent table. This is how postgres implements it - make a new table for each inherited table and do UNION join when doing a SELECT . This makes it hard to implement uniqueness and primary keys, but easy to do updates and inserts. > Take your pick, keeping in mind that the sources I know of (Appendix E of _The > Third Manifesto_, _Database Systems Concepts_ (ISTR), the SQL standard and > postgres currently all assume #1. I would like yet another implementation, more in line with SQL99's single inheritance, where all inherited tables would be stored in the same pagefile (so that you can put a unique index on them and it would "just work" because TIDs all point into the same file). Fast access to some single table ONLY could be done using partial indexes on tableoid. This can't be mapped directly on SQL92 kind of relational model, but can more or less be mimicked by setting the new fields to NULL for tuples belonging to parent relation. > If we find the one we pick is unworkable, we can always go back > and try the other. > > > > If the child table tuples do appear in the parent, you've now got > > > a situation analogous to the current postgres situation where a > > > constraint on the parent table is an outright lie. (I'm thinking > > > of the UNIQUE constraint which guarantees that all values in a > > [snip] > > > > I knew that there are *implementation* issues with postgres that causes > > problems with constraints, etc...I didn't realize that was the reason. > > Well, assuming we are mapping inheritance back into relational stuff > behind the scenes (which it appears to me we are doing now), we can just > map back to the relation method I demonstrated earlier of doing what > someone wanted to do with table inheritance (child tables contain only > foreign key and child-specific data; parent table contains primary key > and all parent data) and that will fix the implementation problem. The main problems I pointed out above: 1. hard-to-implement UPDATE rules, theoretically possible is not good enough for real use ;) 2. too much joining for deep inheritance hierarchies . > Or people have proposed other things, such as cross-table constraints, > to try to do this. > > > Well, you seem to be making references to "...SQL standard, postgres, > > and I believe other systems...". I was counting on you or someone else > > to point us to existing references. > > Well, counting on me is not good, since the whole reason I started this > was because I found the issue confusing in part due to the lack of any > obvious standards here that I could find. :-) But here's what I do have: > > Date, Darwen, _Foundation for Future Database Systems, The > Third Manefesto (Second Edition)_. Appendex E. > > Silberschatz, Korth, Sudarshan, _Database Systems Concepts > (Fourth Edition)_. I think it's around chapter 9. (My copy is > at home right now.) > > SQL Standard. I don't have it handy. Anyone? Anyone? Bueller? I got mine from http://www.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/fcd2found.pdf Quite hard to read, as standard in general tend to be ;) I also have PDF's with a large [FINAL] stamp on them, which I cant remember where I got (but I posted the link here a few months ago) > Postgres. Known broken implementation, but we can at least poke > stuff into it and see what it does. > > In addition, OO programming gets mentioned ocassionally. I don't > think that table inheritance is anything related IMHO table inheritance is a natural relational extension to type inheritance - if you create a subtype that is-a parent type (bird is an animal), you also want to be able to treat it as such in queries - i.e. be able select all animals, and not have to manually make the connection between OO (type inheritance) and Relational (INSERT/UPDATE/SELECT/DELETE) worlds. > (and I've spent > a lot of time in the last couple of years developing methods to > make my OO programs and relational databases play nice with each > other), So have the database guys, adding OO stuff to databases and all ;) > but it might help to have some idea of what people to do > connect the two, in case some people think that they are or should > be connected. You can start by checking out this page for a few > ways of creating objects from database information: > > http://www.martinfowler.com/isa/inheritanceMappers.html I'll try to check it out . ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
