Thread: Re: Inheritance
> > It's nonlocal constraints that are the problem, and here foreign keys > > and UNIQUE constraints are certainly the canonical examples. Both of > > these would be largely solved with table-spanning indexes I think. > > Note that the other obvious way to solve this would be to store all of > the information inherited from the parent in the parent table, so that > you don't have to do anything special to make all of the constraints and > whatnot apply. Seems with above you are not able to constrain what qualifies for a supertable row, you would only be able to specify constraints that apply to all it's subtables. To me, the current implementation looks superior and more efficient. The SQL inheritance is a class/subclass thing. All tables have instances (==rows) that are not (by itself) related. (Even if they happen to share all attribute values with another row of a supertable.) If you want that, then you need to resort to 3NF (or ROWREF's which iirc is another SQL99 feature). Andreas
On Fri, 16 Aug 2002, Zeugswetter Andreas SB SD wrote: > > Note that the other obvious way to solve this would be to store all of > > the information inherited from the parent in the parent table, so that > > you don't have to do anything special to make all of the constraints and > > whatnot apply. > > Seems with above you are not able to constrain what qualifies for a > supertable row, you would only be able to specify constraints that > apply to all it's subtables. Yes, that's the whole point. If I have a constraint on a table, I think it should *never* be possible for that constraint to be violated. If a subtable should not have constraint the supertable has, it shouldn't inherit from the supertable. To do otherwise breaks the relational model. > The SQL inheritance is a class/subclass thing. All tables have > instances (==rows) that are not (by itself) related. (Even if > they happen to share all attribute values with another row of a > supertable.) If you want that, then you need to resort to 3NF (or > ROWREF's which iirc is another SQL99 feature). As I understand it, SQL99 has the restriction that a row with the same primary key appearing in a supertable and/or any of its subtables must be the result of a single INSERT statement. Thus, SQL99 doesn't allow what you're saying, if I understand what you're saying. (I'm not sure that I do.) Am I to take it that you think the inheritance should be inheritance of type information only? That is, if I have supertable A and subtable A', inserting a row into A' does not make a row appear in A? If so, I've got not real problem with that at present, but it's not what postgres currently does, nor would it conform to SQL99. What do others think of this idea? cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
> > Seems with above you are not able to constrain what qualifies for a > > supertable row, you would only be able to specify constraints that > > apply to all it's subtables. > > Yes, that's the whole point. If I have a constraint on a table, I think > it should *never* be possible for that constraint to be violated. If a > subtable should not have constraint the supertable has, it shouldn't > inherit from the supertable. If you want that, you simply need to only create constraints that apply to all tables in the hierarchy. Note that you *can* do this. It should imho be the default behavior. > > To do otherwise breaks the relational model. > That is probably a point of argument. Imho the inheritance feature is something orthogonal to the relational model. It is something else, and thus cannot break the relational model. > > > The SQL inheritance is a class/subclass thing. All tables have > > instances (==rows) that are not (by itself) related. (Even if > > they happen to share all attribute values with another row of a > > supertable.) If you want that, then you need to resort to 3NF (or > > ROWREF's which iirc is another SQL99 feature). > > As I understand it, SQL99 has the restriction that a row with the same > primary key appearing in a supertable and/or any of its subtables must > be the result of a single INSERT statement. Thus, SQL99 doesn't allow > what you're saying, if I understand what you're saying. (I'm not sure > that I do.) I was not talking about primary key, not all tables have a primary key. If the supertable has a PK, then of course an exact match of columns is not possible in supertable/subtable rows. > Am I to take it that you think the inheritance should be inheritance > of type information only? That is, if I have supertable A and > subtable A', inserting a row into A' does not make a row appear in > A? If so, I've got not real problem with that at present, but it's > not what postgres currently does, nor would it conform to SQL99. No, not at all. All I am saying is that I want to be able to create a constraint that only applies to the supertable rows, and not the subtable rows. I would *not* want this as default behavior when creating a constraint though. Andreas
On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote: > > Yes, that's the whole point. If I have a constraint on a table, I think > > it should *never* be possible for that constraint to be violated. If a > > subtable should not have constraint the supertable has, it shouldn't > > inherit from the supertable. > > If you want that, you simply need to only create constraints that apply to > all tables in the hierarchy. Note that you *can* do this. It should imho be > the default behavior. So what you're saying is that constraints shouldn't be inherited? > > To do otherwise breaks the relational model. > > That is probably a point of argument. Imho the inheritance feature > is something orthogonal to the relational model. It is something else, and > thus cannot break the relational model. So then constraints must be inherited. The relational model, if I am not incorrect here, says that, given a table definition such as this: CREATE TABLE my_table (my_key int PRIMARY KEY,my_value text UNIQUE,my_other_value int CHECK (my_other_value > 0) ) You will never, ever, when selecting from this table, have returned to you 1. two rows with the same value of my_key but different values for the other columns, 2. two rows with the same value of my_value but different values for the other columns, or 3. a row in which the value of my_other_value is not greater than zero. Breaking these sorts of guarantees under any circumstances really doesn't do it for me; what's the point of having guarantees if they aren't guarantees? > > As I understand it, SQL99 has the restriction that a row with the same > > primary key appearing in a supertable and/or any of its subtables must > > be the result of a single INSERT statement. Thus, SQL99 doesn't allow > > what you're saying, if I understand what you're saying. (I'm not sure > > that I do.) > > I was not talking about primary key, not all tables have a primary key. Well, for those that do.... Also, I should amend that; I suspect (though I could well be wrong, knowing how screwed up SQL is at times) that this really applies to all candidate keys in the table. (And this is one of my complaints about SQL; it's possible for a table to exist without candidate keys. So much for set theory!) > No, not at all. All I am saying is that I want to be able to create a > constraint that only applies to the supertable rows, and not the > subtable rows. I would strongly object to that. It should not be possible to SELECT data from a table that violates the constraints that that table is guaranteeing on the data. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
> > > Yes, that's the whole point. If I have a constraint on a table, I think > > > it should *never* be possible for that constraint to be violated. If a > > > subtable should not have constraint the supertable has, it shouldn't > > > inherit from the supertable. > > > > If you want that, you simply need to only create constraints that apply to > > all tables in the hierarchy. Note that you *can* do this. It should imho be > > the default behavior. > > So what you're saying is that constraints shouldn't be inherited? No. I even said that inheriting should be the default. > > > To do otherwise breaks the relational model. > > > > That is probably a point of argument. Imho the inheritance feature > > is something orthogonal to the relational model. It is something else, and > > thus cannot break the relational model. > > So then constraints must be inherited. The relational model, if I > am not incorrect here, says that, given a table definition such as > this: > > CREATE TABLE my_table ( > my_key int PRIMARY KEY, > my_value text UNIQUE, > my_other_value int CHECK (my_other_value > 0) > ) A local constraint should be made obvious from looking at the schema, a possible syntax (probably both ugly :-): CHECK my_table ONLY (my_other_value > 0) or CHECK LOCAL (my_other_value > 0) > > You will never, ever, when selecting from this table, have > returned to you > > 1. two rows with the same value of my_key but different values > for the other columns, > > 2. two rows with the same value of my_value but different values > for the other columns, or > > 3. a row in which the value of my_other_value is not > greater than zero. > Well, that is where I do not think this is flexible enough, and keep in mind that all triggers and rules would then also need such restrictions. > I would strongly object to that. Regardless whether your objection is *strong* or not :-) If you don't like the feature (to add a local constraint), don't use it. (Remember you are talking about removing an implemented feature) Andreas
On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote: > > So what you're saying is that constraints shouldn't be inherited? > > No. I even said that inheriting should be the default. Ah. So you think it should be possible not to inherit constraints. > A local constraint should be made obvious from looking at the schema, Ok, this now I could live with. Though I'm not sure that its theoretically very defensible, or worth the effort. Other languages that offer constraints, such as Eiffel (and soon Java), do not allow constraints that are not inherited, as far as I know. Do you have some counterexamples. > Well, that is where I do not think this is flexible enough, and keep in mind > that all triggers and rules would then also need such restrictions. Yes, all triggers, rules, and everything else would have to be inherited. > Regardless whether your objection is *strong* or not :-) > If you don't like the feature (to add a local constraint), don't use it. > (Remember you are talking about removing an implemented feature) 1. It's not exactly an implemented feature, it's an accident of an incomplete implementation of inheritance done in a certain way. 2. Should we change the way we decide to implement inheritance, perhaps to make fixing the current problems much easier, it might be a lot of work to add this. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Mon, 2002-08-19 at 15:42, Curt Sampson wrote: > > A local constraint should be made obvious from looking at the schema, > > Ok, this now I could live with. Though I'm not sure that its > theoretically very defensible, or worth the effort. Other languages > that offer constraints, such as Eiffel (and soon Java), do not allow > constraints that are not inherited, as far as I know. Do you have some > counterexamples. In Eiffel, at least, I can say "invariant feature_x" and redefine feature_x in a descendant class, thus effectively redefining the constraint. If we decide to inherit constraints unconditionally, the application writer can achieve similar flexibility by moving the logic of the constraint into a function whose behaviour depends on which table it is used on. This would put the burden on the application rather than requiring additional syntax in PostgreSQL. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For every one that asketh receiveth; and he that seeketh findeth; and tohim that knocketh it shall be opened." Luke 11:10
On Mon, 2002-08-19 at 09:42, Curt Sampson wrote: > On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote: > > > > So what you're saying is that constraints shouldn't be inherited? > > > > No. I even said that inheriting should be the default. > > Ah. So you think it should be possible not to inherit constraints. I've been silent for a bit because I wanted to kick the concept around in my head. After some thought, I say that I support children inheriting constraints. In a more abstract sense, we are really setting conditions for all entities of a given type (class) which must be met to classify as a defined type. Again, in an abstract sense, if I say all "candies" (type/class, candy) must have sugar (constraint), and I go on to create a subclass of candy which I desire not to have sugar, then there is a fundamental problem. Either I incorrectly identified my problem domain and didn't properly create my entities which address my domain needs or what I'm trying to express really isn't a candy at all. In other words, it sounds like candy should of been a subclass of a more abstract base entity. Likewise, the newly desired class which doesn't have sugar should also inherit from the newly created base class and not be derived from candy at all. > > > A local constraint should be made obvious from looking at the schema, > > Ok, this now I could live with. Though I'm not sure that its > theoretically very defensible, or worth the effort. Other languages > that offer constraints, such as Eiffel (and soon Java), do not allow > constraints that are not inherited, as far as I know. Do you have some > counterexamples. I tend to agree. Constraints should be inherited. See above. > > > Well, that is where I do not think this is flexible enough, and keep in mind > > that all triggers and rules would then also need such restrictions. > > Yes, all triggers, rules, and everything else would have to be inherited. Agreed. > > > Regardless whether your objection is *strong* or not :-) > > If you don't like the feature (to add a local constraint), don't use it. > > (Remember you are talking about removing an implemented feature) > > 1. It's not exactly an implemented feature, it's an accident of an > incomplete implementation of inheritance done in a certain way. > > 2. Should we change the way we decide to implement inheritance, > perhaps to make fixing the current problems much easier, it might > be a lot of work to add this. > I'm still trying to figure out if subclasses should be allowed to have localized constraints. I tend to think yes even though it's certainly possible to create seemingly illogical/incompatible/conflicting constraints with parent classes. Then again, my gut feeling is, that's more and an architectural/design issue rather than a fundamental issue with the concept. --Greg Copeland
The August draft of the SQL:200n standard (9075-2 Foundation) says in Section 4.17.2: "Every table constraint specified for base table T is implicitly a constraint on every subtable of T, by virtue of the fact that every row in a subtable is considered to have a corresponding superrow in every one of its supertables." Peter Gulutzan Co-Author, SQL-99 Complete, Really Co-Author, SQL Performance Tuning
Peter Gulutzan wrote: > The August draft of the SQL:200n standard (9075-2 Foundation) says in > Section 4.17.2: "Every table constraint specified for base table T is > implicitly a constraint on every subtable of T, by virtue of the fact > that every row in a subtable is considered to have a corresponding > superrow in every one of its supertables." Yep, this is where we are stuck; having an index span multiple tables in some way. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 3 Sep 2002, Bruce Momjian wrote: > Yep, this is where we are stuck; having an index span multiple tables > in some way. Or implementing it by keeping all data in the table in which it was declared. (I.e., supertable holds all rows; subtable holds only the primary key and those columns of the row that are not in the supertable.) From looking at the various discussions of this in books, and what it appears to me that the SQL standard says, it seems that their overall vision of table inheritance is to be consistent with the implementation that I described above. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Thu, 2002-09-05 at 03:57, Curt Sampson wrote: > On Tue, 3 Sep 2002, Bruce Momjian wrote: > > > Yep, this is where we are stuck; having an index span multiple tables > > in some way. > > Or implementing it by keeping all data in the table in which it > was declared. (I.e., supertable holds all rows; subtable holds > only the primary key and those columns of the row that are not > in the supertable.) How would you do it for _multiple_ inheritance ? When implementing it on top of standard relational model you have more or less two ways to slice the problem 1) the way you describe (parent holding common columns + child tables for added child columns), which makes it easy to define constraints but hard to do inserts/updates/deletes on inherited tables 2) the postgresql way (a new table for each child), which makes it hard to define constraints but easy to do inserts/updates/deletes. > From looking at the various discussions of this in books, and what > it appears to me that the SQL standard says, it seems that their > overall vision of table inheritance is to be consistent with the > implementation that I described above. Yes. The SQL99 standard specifies only _single_ inheritance for tables + LIKE in column definition part, making the model somewhat similar to Java's (single inheritance + interfaces). This way it could probably be done even more effectively than you describe by: 1) keeping _all_ (not only the inherited columns) the data for inheritance hierarchy in the same physical file. 2) having partial indexes (involving tableoid=thiskindoftable) for possible speeding up of SELECT .. ONLY queries. 3) no changes to (unique) indexes - they still reference simple TID's without additional table part. 4) update/delete of all child tables are trivial as they are actually done in the same table and not using joins It seems that single inheritance avoids other conceptual problems, like what to do with primary keys when inheriting from two tables that have them. -------------------- Hannu
On 5 Sep 2002, Hannu Krosing wrote: > On Thu, 2002-09-05 at 03:57, Curt Sampson wrote: > > > Or implementing it by keeping all data in the table in which it > > was declared. (I.e., supertable holds all rows; subtable holds > > only the primary key and those columns of the row that are not > > in the supertable.) > > How would you do it for _multiple_ inheritance ? Exactly the same way. Each column resides in only one physical table, so you need only find the table it resides in, and do the insert there. I'll be happy to provide an example if this is not clear. > 1) the way you describe (parent holding common columns + child tables > for added child columns), which makes it easy to define constraints but > hard to do inserts/updates/deletes on inherited tables I wouldn't say it makes it "hard" to do inserts, updates and deletes. Postgres already has pretty near all of the code it needs to support these updates, because these are the semantic equivalant of the separate actions applied to the separate tables within one transaction. > 2) the postgresql way (a new table for each child), which makes it hard > to define constraints but easy to do inserts/updates/deletes. I agree that making constraints work in this model is very difficult and a lot of work. > This way it could probably be done even more effectively than you > describe by: > > 1) keeping _all_ (not only the inherited columns) the data for > inheritance hierarchy in the same physical file. You appear to have delved into a different database layer than one I'm looking at, here. I was examining storage on the table level, which is unrelated to files. (E.g., postgres sometimes stores a table in one file, sometimes in more than one. MS SQL Server stores many tables in one file. It doesn't matter which approach is used when discussing the two inheritance implementation options above.) > 4) update/delete of all child tables are trivial as they are actually > done in the same table and not using joins Or are you talking about storing all of the columns in a single table? That's a possibility, but wouldn't it be costly to update the entire table every time you add a new child table? And table scans on child tables would certainly be more costly if you had many of them, becuase the effective row width would be much wider. But it might be worth thinking about. > It seems that single inheritance avoids other conceptual problems, like > what to do with primary keys when inheriting from two tables that have > them. I don't see where there's a conceptual problem here, either. With multiple inheritance you can simply demote both keys to candidate keys, and continue on as normal. (The only difference between a primary key and a candidate key is that you can leave out the column names when declaring foreign keys in another table.) cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Thu, 2002-09-05 at 09:28, Curt Sampson wrote: > On 5 Sep 2002, Hannu Krosing wrote: > > > On Thu, 2002-09-05 at 03:57, Curt Sampson wrote: > > > > > Or implementing it by keeping all data in the table in which it > > > was declared. (I.e., supertable holds all rows; subtable holds > > > only the primary key and those columns of the row that are not > > > in the supertable.) > > > > How would you do it for _multiple_ inheritance ? > > Exactly the same way. Each column resides in only one physical table, > so you need only find the table it resides in, and do the insert there. > I'll be happy to provide an example if this is not clear. > > > 1) the way you describe (parent holding common columns + child tables > > for added child columns), which makes it easy to define constraints but > > hard to do inserts/updates/deletes on inherited tables > > I wouldn't say it makes it "hard" to do inserts, updates and deletes. > Postgres already has pretty near all of the code it needs to support > these updates, because these are the semantic equivalant of the separate > actions applied to the separate tables within one transaction. What I meant was that it is relatively more costly to update several "physical" tables than updating one . > > 2) the postgresql way (a new table for each child), which makes it hard > > to define constraints but easy to do inserts/updates/deletes. > > I agree that making constraints work in this model is very difficult and > a lot of work. But again this is not _conceptually_ hard, just hard to implement efficiently. > > This way it could probably be done even more effectively than you > > describe by: > > > > 1) keeping _all_ (not only the inherited columns) the data for > > inheritance hierarchy in the same physical file. > > You appear to have delved into a different database layer than one > I'm looking at, here. probably. I was describing to a way to efficiently implement single inheritance. The layer was somewhere between physical files and logical tables, i.e. above splitting stuff into main/toast and also above splitting big files to 1Gb chunks, but below logical tables, which are (or are not when omitting ONLY ;) still separate logically. Perhaps it could be named "logical file". > I was examining storage on the table level, which is unrelated to files. > (E.g., postgres sometimes stores a table in one file, sometimes in more > than one. MS SQL Server stores many tables in one file. > It doesn't matter which approach is used when > discussing the two inheritance implementation options above.) It does not matter in case you are assuming that the storage model can't be changed. The trick with inherited tables is that in some sense they are the same table and in another sense they are separate tables. > > 4) update/delete of all child tables are trivial as they are actually > > done in the same table and not using joins > > Or are you talking about storing all of the columns in a single > table? That's a possibility, but wouldn't it be costly to update > the entire table every time you add a new child table? You should not need it, as the storage for existing tuples does not change - even now you can do ADD COLUMN without touching existing tuples. > And table > scans on child tables would certainly be more costly if you had > many of them, becuase the effective row width would be much wider. It would not be noticably wider (only 1 bit/column) even if I did propose storing all columns. What I was actually trying to describe was that the tuple format would be what it is currently, just stored in the same table with parent. > But it might be worth thinking about. > > > It seems that single inheritance avoids other conceptual problems, like > > what to do with primary keys when inheriting from two tables that have > > them. > > I don't see where there's a conceptual problem here, either. With > multiple inheritance you can simply demote both keys to candidate > keys, and continue on as normal. (The only difference between a > primary key and a candidate key is that you can leave out the column > names when declaring foreign keys in another table.) That's one possibility. The other would be to keep the one from the first table as primary and demote onlly the other primary keys. With single inheritance you don't even have to think about it. ----------------- Hannu
On 5 Sep 2002, Hannu Krosing wrote: > What I meant was that it is relatively more costly to update several > "physical" tables than updating one . Oh, I see. Not that this is that big a deal, I think. Given that it doesn't work correctly at the moment, making it work fast is a definite second priority, I would think. Once it's working right, one can always replace the internals with something else that does the same job but is more efficient. > > I agree that making constraints work in this model is very difficult and > > a lot of work. > > But again this is not _conceptually_ hard, just hard to implement > efficiently. No, it's conceptually hard. Not all constraints are implemented with just a unique index you know. And changing a constraint means you have to check all the child tables, etc. etc. It's difficult just to track down down all the things you have to try to preserve. Not to mention, there's always the question of what happens to triggers and suchlike when handed a tuple with extra columns from what it expects, and having it modify the insert into a different table. The beauty of storing all supertable columns in the supertable itself is that the behaviour is automatically correct. > What I was actually trying to describe was that the tuple format would > be what it is currently, just stored in the same table with parent. So what you're saying is that each tuple in the table would have a format appropriate for its "subtype," and the table would be full of tuples of varying types? At first blush, that seems like a reasonable approach, if it can be done. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
I have a question about inheritance: You have 2 tables: Programmer and employee. Programmer inherits employee. You put in a generic employee record for someone, but then she becomes a programmer. What do you do? (I borrowed this example from a book by C.J. Date, who posed this question). Do you DELETE then INSERT? Something seems wrong with that somehow. Are the postgres developers agreed upon how that situation should be handled? What about the database users, and their expectations of the behavior? I am not advocating that we remove inheritence (I say this because this topic has generated some significant discussion about that). However, I will stick to the well-defined relational model until I see something useful from the inheritance system that is as well-defined. I agree it saves a few keystrokes (and can help organize things for you, as do objects in a programming language), but mind is more at peace when I am actually sure of what's happening. I can always throw more rules/views/triggers at the situation until I have a nice set of things to work with in the application. Or, I suppose, if someone shows me something that I can't do in the relational model, but can with inheritance, I might be convinced otherwise. Regards,Jeff Davis On Thursday 05 September 2002 01:05 am, Hannu Krosing wrote: > On Thu, 2002-09-05 at 03:57, Curt Sampson wrote: > > On Tue, 3 Sep 2002, Bruce Momjian wrote: > > > Yep, this is where we are stuck; having an index span multiple tables > > > in some way. > > > > Or implementing it by keeping all data in the table in which it > > was declared. (I.e., supertable holds all rows; subtable holds > > only the primary key and those columns of the row that are not > > in the supertable.) > > How would you do it for _multiple_ inheritance ? > > When implementing it on top of standard relational model you have more > or less two ways to slice the problem > > 1) the way you describe (parent holding common columns + child tables > for added child columns), which makes it easy to define constraints but > hard to do inserts/updates/deletes on inherited tables > > 2) the postgresql way (a new table for each child), which makes it hard > to define constraints but easy to do inserts/updates/deletes. > > > From looking at the various discussions of this in books, and what > > it appears to me that the SQL standard says, it seems that their > > overall vision of table inheritance is to be consistent with the > > implementation that I described above. > > Yes. The SQL99 standard specifies only _single_ inheritance for tables + > LIKE in column definition part, making the model somewhat similar to > Java's (single inheritance + interfaces). > > This way it could probably be done even more effectively than you > describe by: > > 1) keeping _all_ (not only the inherited columns) the data for > inheritance hierarchy in the same physical file. > > 2) having partial indexes (involving tableoid=thiskindoftable) for > possible speeding up of SELECT .. ONLY queries. > > 3) no changes to (unique) indexes - they still reference simple TID's > without additional table part. > > 4) update/delete of all child tables are trivial as they are actually > done in the same table and not using joins > > > It seems that single inheritance avoids other conceptual problems, like > what to do with primary keys when inheriting from two tables that have > them. > > -------------------- > Hannu > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Thu, 5 Sep 2002, Jeff Davis wrote: > You have 2 tables: Programmer and employee. Programmer inherits employee. You > put in a generic employee record for someone, but then she becomes a > programmer. What do you do? (I borrowed this example from a book by C.J. > Date, who posed this question). Do you DELETE then INSERT? Something seems > wrong with that somehow. This is not so wrong. If you think about it, you have the same problem in most object-oriented programming languages: a person object can't generally easily become a subclass of itself after being created. This is a case, I would say, where you simply don't want to use inheritance. A person has-a job, not is-a job. > What about the database users, and their expectations of the behavior? Nobody really knows; table inheritance in databases is not well-defined. (Though perhaps the latest SQL spec. changes that.) > However, I will stick to the well-defined relational model until I see > something useful from the inheritance system that is as well-defined. Amen! :-) > Or, I suppose, if someone shows me something that I can't do in the > relational model, but can with inheritance, I might be convinced > otherwise. I think that most people are at this point agreed that table inheritance, at least as currently implemented in any known system, doesn't offer anything that can't easily be done relationally. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Thu, 2002-09-05 at 10:52, Curt Sampson wrote: > On 5 Sep 2002, Hannu Krosing wrote: > > > What I meant was that it is relatively more costly to update several > > "physical" tables than updating one . > > Oh, I see. Not that this is that big a deal, I think. Given that > it doesn't work correctly at the moment, making it work fast is a > definite second priority, I would think. But choosing an implementation that _can_be_ made to work fast is not. > Once it's working right, one can always replace the internals with > something else that does the same job but is more efficient. I still think that choosing the right implementation can also help in making it work right. > > > I agree that making constraints work in this model is very difficult and > > > a lot of work. > > > > But again this is not _conceptually_ hard, just hard to implement > > efficiently. > > No, it's conceptually hard. Not all constraints are implemented with > just a unique index you know. And changing a constraint means you have > to check all the child tables, etc. etc. It's difficult just to track > down down all the things you have to try to preserve. It may be a lot of work, but not _conceptually_ hard. Conceptually you have to do the same thing as for a single table, but just for all inherited tables. > Not to mention, > there's always the question of what happens to triggers and suchlike > when handed a tuple with extra columns from what it expects, and having > it modify the insert into a different table. IMHO that the trigger should not be aware of underlying implementation - so it needs not worry about modifying the insert into a different table. > The beauty of storing all supertable columns in the supertable itself is > that the behaviour is automatically correct. But "automatically correct" may not be what you want ;) What about trigger that generates a cached printname using function printname(row) that is different for each table - here you definitely do not want to run the function defined for base table for anything inherited. > > What I was actually trying to describe was that the tuple format would > > be what it is currently, just stored in the same table with parent. > > So what you're saying is that each tuple in the table would have a > format appropriate for its "subtype," and the table would be full of > tuples of varying types? At first blush, that seems like a reasonable > approach, if it can be done. At least it makes some parts easier ;) ---------------- Hannu
On 5 Sep 2002, Hannu Krosing wrote: > > Oh, I see. Not that this is that big a deal, I think. Given that > > it doesn't work correctly at the moment, making it work fast is a > > definite second priority, I would think. > > But choosing an implementation that _can_be_ made to work fast is not. I would say it definitely is. A correctly working implementation can be replaced. An incorrectly working implementation destroys data integrety. Which is more important for PostgreSQL? Speed or maintaining data integrity? > > Not to mention, > > there's always the question of what happens to triggers and suchlike > > when handed a tuple with extra columns from what it expects, and having > > it modify the insert into a different table. > > IMHO that the trigger should not be aware of underlying implementation - > so it needs not worry about modifying the insert into a different table. I agree. > > The beauty of storing all supertable columns in the supertable itself is > > that the behaviour is automatically correct. > > But "automatically correct" may not be what you want ;) > > What about trigger that generates a cached printname using function > printname(row) that is different for each table - here you definitely do > not want to run the function defined for base table for anything > inherited. Right. But that will be "automatically correct" when you store all base data in the base table. It's when you start storing those data in other tables that the trigger can get confused. Or are you saying that when I insert a row into "just" a child table, the trigger shouldn't be invoked on the "parent table" portion of that insert? If so, I'd strongly disagree. If that trigger is acting as an integrety constraint on the base table, you might destroy the table's integrity. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
> > This is not so wrong. If you think about it, you have the same > problem in most object-oriented programming languages: a person > object can't generally easily become a subclass of itself after > being created. > > This is a case, I would say, where you simply don't want to use > inheritance. A person has-a job, not is-a job. > But a person is-a employee (allow me to momentarily step aside from the rules of english grammer, if you would), and a person is-a programmer. That's why I didn't call my table "job" :) [1] I don't like the way some OO programming languages handle objects, if they mean to say you can't change an object's type without performing a logical data copy to a new object. If you don't use some kind of extra layer of abstraction in C, you will end up with that problem: you'd need to copy all that RAM over to change from one struct to another. Most people would rather take that RAM copying hit than all the hits for allowing "room to expand" (at least in some applications). However, postgres needs to provide that "room to expand" for each tuple anyway, so to go through the same copying seems bad (especially since we're no longer just talking RAM). Take as an example python... it's easy to emulate other objects: just assign to the attribute, even if it's not there yet, it'll add the attribute. Same with python, it's providing room to expand for it's objects already, so why do all the copying? Now compare with Java, and see why you'd be annoyed. It has the facilities to change the objects all around, but you can't do it. Even if you disregard all implementation details, and assume that the database is intelligent enough to not redundantly write data (and if you could name one such database, I would like to know), you're still doing something that doesn't logically make sense: you're deleting and inserting atomically, when the more obvious logical path is to expand on the data you already carry about an entity. I like entities to be mutable, at least as far as makes sense to an application. Try telling an employee that as part of a promotion, they're going to be fired, lose their workstation, then be re-hired, and get a new workstation; I bet he'd have an interesting expression on his face (hey, at least postgres guarantees the "A" in ACID, or else bad things could happen to that poor guy :) Thanks for responding, and I agreed with everything else you said. As you might have guessed, I don't much like "most object-oriented languages" if that's what they're going to try to tell me I have to do. Python works nicely, however :) Regards,Jeff Davis [1] Come to think of it, the JOIN operator seems to, at least on a first thought, represent the "has-a" relationship you describe. You could have the tuples "manager" and "programmer" in the table "job" and join with a "people" table. Don't ask about inheritance yet for this model, I'm still thinking about that one (does "has-a" even have an analogue to inheriteance?). Send me your thoughts about this, if you should have any.
On Thu, 2002-09-05 at 11:34, Curt Sampson wrote: > On 5 Sep 2002, Hannu Krosing wrote: > > > > Oh, I see. Not that this is that big a deal, I think. Given that > > > it doesn't work correctly at the moment, making it work fast is a > > > definite second priority, I would think. > > > > But choosing an implementation that _can_be_ made to work fast is not. > > I would say it definitely is. A correctly working implementation > can be replaced. An incorrectly working implementation destroys > data integrety. > > Which is more important for PostgreSQL? Speed or maintaining data > integrity? Both of course. The lack of one often makes the other unusable. But as MySQL experience suggest, often people select speed over data integrity. OTOH sometimes you happily accept a 10sec delay in updates to have data integrity (like when doing a money transfer over internet;) > > > Not to mention, > > > there's always the question of what happens to triggers and suchlike > > > when handed a tuple with extra columns from what it expects, and having > > > it modify the insert into a different table. > > > > IMHO that the trigger should not be aware of underlying implementation - > > so it needs not worry about modifying the insert into a different table. > > I agree. > > > > The beauty of storing all supertable columns in the supertable itself is > > > that the behaviour is automatically correct. > > > > But "automatically correct" may not be what you want ;) > > > > What about trigger that generates a cached printname using function > > printname(row) that is different for each table - here you definitely do > > not want to run the function defined for base table for anything > > inherited. > > Right. But that will be "automatically correct" when you store all > base data in the base table. It's when you start storing those data > in other tables that the trigger can get confused. > > Or are you saying that when I insert a row into "just" a child > table, the trigger shouldn't be invoked on the "parent table" > portion of that insert? If so, I'd strongly disagree. Conceptually there are no "portions" of table - the trigger is invoked on one _tuple_ exactly (pg has only row-level triggers), and each tuple belongs to only one table regardless how it is implemented internally. > If that > trigger is acting as an integrety constraint on the base table, > you might destroy the table's integrity. What I try to say is that you should have the same freedom with triggers that you have with select/insert/update/delete - you must be able to choose if the trigger is on the parent table ONLY or on parent and all children. And you should be able to override a trigger for child table even if it is defined on parent as applying to all children - I guess that overriding by trigger _name_ would be what most people expect. Suppose you have a table CITIZEN with table-level constraint IS_GOOD which is defined as kills_not_others(CITIZEN). and there is table CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD so you must either be able to override the trigger for that table (and its children) or make sure that the functions used are dynamically mached to the actual tuple type (header in Relational Model parlance) so that kills_not_others(MILITARY) will be used, which presents the system MILITARYs view of the being good ;) What I'm after here is dynamic (and automatic) row level dispach of the right function based on row type - so that for rows in CITIZEN or CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but for rows in MILITAY the kills_not_others(MILITARY) is used. --------- Hannu
On Thu, 2002-09-05 at 12:29, Jeff Davis wrote: > > > > This is not so wrong. If you think about it, you have the same > > problem in most object-oriented programming languages: a person > > object can't generally easily become a subclass of itself after > > being created. > > > > This is a case, I would say, where you simply don't want to use > > inheritance. A person has-a job, not is-a job. > > > > But a person is-a employee (allow me to momentarily step aside from the rules > of english grammer, if you would), and a person is-a programmer. That's why I > didn't call my table "job" :) [1] > > I don't like the way some OO programming languages handle objects, if they > mean to say you can't change an object's type without performing a logical > data copy to a new object. If you don't use some kind of extra layer of > abstraction in C, you will end up with that problem: you'd need to copy all > that RAM over to change from one struct to another. Most people would rather > take that RAM copying hit than all the hits for allowing "room to expand" (at > least in some applications). However, postgres needs to provide that "room to > expand" for each tuple anyway, so to go through the same copying seems bad > (especially since we're no longer just talking RAM). I would like to have UPDATEs both up and down the inheritance hierarchy, so that when I have hierarchy OBJECT(id serial primary key)+ HUMAN(name text,age int) + EMPLOYEE(salary numeric) + ENGINEER(workstation computer) + PHB(laptop computer) and ENGINEER named Bob I could do UPDATE ENGINEER TO PHB SET salary = salary * 2 + age * 1000, laptop.disksize = max(laptop.disksize , workstation.disksize + 1000000)WHERE name='Bob' ; to promote Bob from an engineer to phb, give him a salary rise and a laptop with default configuration ensuring big enough disk to keep all his old files, but still keep all FK related records. > Take as an example python... it's easy to emulate other objects: just assign > to the attribute, even if it's not there yet, it'll add the attribute. Same > with python, it's providing room to expand for it's objects already, so why > do all the copying? that's unless you use the new-style objects and __slots__ >>> class myobj(object): ... __slots__ = ['a','b'] ... >>> M = myobj() >>> M.a =1 >>> M.c =1 Traceback (most recent call last): File "<stdin>", line 1, in ? AttributeError: 'myobj' object has no attribute 'c' >>> > Same with python, it's providing room to expand for it's objects already, > so why do all the copying? > [1] Come to think of it, the JOIN operator seems to, at least on a first > thought, represent the "has-a" relationship you describe. You could have the > tuples "manager" and "programmer" in the table "job" and join with a "people" > table. Don't ask about inheritance yet for this model, I'm still thinking > about that one (does "has-a" even have an analogue to inheriteance?). Not in inheritance, but in OO world attributes are used to express has-a relations. So bob = people(name='Bob')bob.job = job('Manager') makes an has-a relation between Bob and his job in python BTW, good programming guidelines in python tell you not to test if bob is-a something but rather test if the interface for something exists - to see if you can iterate over bob you do not test if bob is a sequence but just try it: try: for idea in bob: examine(idea) except TypeError: print 'Failed to iterate over %s %s !' % (bob,job.name, bob.name) --------------- Hannu
I really like Hannu's idea of storing an entire (single-inheritance) hierarchy in a single file. I guess the question we need to ask ourselves is if we're prepared to abandon support of multiple inheritance. Personally I am, but... regards, tom lane
On Thu, Sep 05, 2002 at 10:23:02AM -0400, Tom Lane wrote: > I really like Hannu's idea of storing an entire (single-inheritance) > hierarchy in a single file. Wouldn't this require solving the ALTER TABLE ADD COLUMN (to parent) column ordering problem? > I guess the question we need to ask ourselves is if we're prepared to > abandon support of multiple inheritance. Personally I am, but... No opinion - I've not used the inheritance much, since I'm not willing to give up referential integrity. Ross
On Thu, 2002-09-05 at 19:23, Tom Lane wrote: > I really like Hannu's idea of storing an entire (single-inheritance) > hierarchy in a single file. > > I guess the question we need to ask ourselves is if we're prepared to > abandon support of multiple inheritance. Personally I am, but... So am I, but I think we should move in stages - 1) first implement the SQL99 standard CREATE TABLE mytable() UNDER parenttable ; using the above idea and make it workright vs constraints, triggers, functions, etc. This should include the ability to include other table structures using LIKE : CREATE TABLE engine(...); CREATE TABLE vehicule(...); CREATE TABLE car ( model text, wheels wheel[], LIKEengine, ) UNDER vehicule; which could then hopefully be used for migrating most code of form CREATE TABLE car ( model text primary key, wheels wheel[] ) INHERITS (vehicule, engine); it would be nice (maybe even neccessary) to keep the current functionality that columns introduced by LIKE are automatically added/renamed/deleted when LIKE's base table changes. 2) when it is working announce non-SQL99-standard-and-broken INHERITS to be deprecated and removed in future. 3) give people time for some releases to move over to UNDER + LIKE . Or if someone comes up with bright ideas/impementationsfor fixing multiple inheritance, then un-deprecate and keep it. 4) else try to remove INHERITS. 5) if too many people object, goto 3) ;) ------------------- Hannu
On Thu, 2002-09-05 at 08:15, Hannu Krosing wrote: > On Thu, 2002-09-05 at 11:34, Curt Sampson wrote: > > On 5 Sep 2002, Hannu Krosing wrote: > > If that > > trigger is acting as an integrety constraint on the base table, > > you might destroy the table's integrity. > > What I try to say is that you should have the same freedom with triggers > that you have with select/insert/update/delete - you must be able to > choose if the trigger is on the parent table ONLY or on parent and all > children. Sounds like a mechanism to make the distinction between virtual (child can override parent) and non-virtual (child is constrained by the parent) constraints are needed. After all, there are two basic needs for constraints. One is for relational integrity and the other is business rule integrity. That is, one seeks to ensure that the database makes sense in respect to the data model (a shoe is a product) while the other is to enforce business rules (products are never free). Seems like the DBA should be able to dictate which domain his constraint falls into in some manner. > > And you should be able to override a trigger for child table even if it > is defined on parent as applying to all children - I guess that > overriding by trigger _name_ would be what most people expect. > That's the reason I used virtual and non-virtual above. If we think using C++ idioms, the child is stuck with it if it's deemed non-virtual. Generally speaking, if someone designed something with that expectation in mind, there's probably a good reason for it. In this case, we could assume that such non-virtual constraints would be to help ensure proper RI. Something that otherwise, IMO, would be tossed out with the bath water. > What I'm after here is dynamic (and automatic) row level dispach of the > right function based on row type - so that for rows in CITIZEN or > CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but > for rows in MILITAY the kills_not_others(MILITARY) is used. I think we're touching on some form of RTTI information here. That is, triggers and even functions may need to be able to dynamically determine the row type that is actively being worked on. If we're on the same page, I think that seemingly makes a lot of sense. What about the concept of columns being public or private? That is, certain columns may not be inherited by a child? Any thought to such a concept? Perhaps different types of table inheritance can be considered in our model...has-a, is-a, etc... Regards, Greg Copeland
On Fri, 2002-09-06 at 03:19, Greg Copeland wrote: > On Thu, 2002-09-05 at 08:15, Hannu Krosing wrote: > > On Thu, 2002-09-05 at 11:34, Curt Sampson wrote: > > > On 5 Sep 2002, Hannu Krosing wrote: > > > > If that > > > trigger is acting as an integrety constraint on the base table, > > > you might destroy the table's integrity. > > > > What I try to say is that you should have the same freedom with triggers > > that you have with select/insert/update/delete - you must be able to > > choose if the trigger is on the parent table ONLY or on parent and all > > children. > > Sounds like a mechanism to make the distinction between virtual (child > can override parent) and non-virtual (child is constrained by the > parent) constraints are needed. > > After all, there are two basic needs for constraints. One is for > relational integrity and the other is business rule integrity. That is, > one seeks to ensure that the database makes sense in respect to the data > model (a shoe is a product) while the other is to enforce business rules > (products are never free). Seems like the DBA should be able to dictate > which domain his constraint falls into in some manner. > > > And you should be able to override a trigger for child table even if it > > is defined on parent as applying to all children - I guess that > > overriding by trigger _name_ would be what most people expect. > > > > That's the reason I used virtual and non-virtual above. If we think > using C++ idioms, the child is stuck with it if it's deemed > non-virtual. Generally speaking, if someone designed something with > that expectation in mind, there's probably a good reason for it. In > this case, we could assume that such non-virtual constraints would be to > help ensure proper RI. Something that otherwise, IMO, would be tossed > out with the bath water. I agree to this. What I described (making overriding decision solely in child) is probably a bad idea. > > What I'm after here is dynamic (and automatic) row level dispach of the > > right function based on row type - so that for rows in CITIZEN or > > CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but > > for rows in MILITAY the kills_not_others(MILITARY) is used. > > I think we're touching on some form of RTTI information here. That is, > triggers and even functions may need to be able to dynamically determine > the row type that is actively being worked on. Should be easy if the row comes directly from a table : just use tableoid column. > If we're on the same page, I think that seemingly makes a lot of sense. > > What about the concept of columns being public or private? That is, > certain columns may not be inherited by a child? Any thought to such a > concept? Perhaps different types of table inheritance can be considered > in our model...has-a, is-a, etc... I can't fit this in my mental model of table inheritance for two reasons 1) all parent table columns must be present in child 2) granting some right to parent should automatically allow selecting from children both are required for select/insert/update/delete to work on table and its children (i.e. without ONLY) But maybe i just need to think more about it ;) ------------------ Hannu
On 5 Sep 2002, Hannu Krosing wrote: > Suppose you have a table CITIZEN with table-level constraint IS_GOOD > which is defined as kills_not_others(CITIZEN). and there is table > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD.... This I very much disagree with. In most object-oriented languages (Eiffel being a notable exception, IIRC), you can't specify constraints on objects. But in a relational database, you can specify constraints on tables, and it should *never* *ever* be possible to violate those constraints, or the constraints are pointless. So if I have a constraint that says, "no rows appearing in this table will ever violate constraint X," and then you go and create a way of inserting rows into that table that violate that constraint, I think you've just made the database into a non-relational database. I really don't want to break postgres' relational side for some inheritance features of dubious utility. Constraints should be explicitly removed from tables if they are no longer needed, not implicitly removed through the creation of another table. I think we should settle this point before going any further. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On 5 Sep 2002, Greg Copeland wrote: > Sounds like a mechanism to make the distinction between virtual (child > can override parent) and non-virtual (child is constrained by the > parent) constraints are needed. Oh, I should mention that I have no problem with being able to declare a constraint "overridable" by subtables, so long as it's not the default, and it's clear from the table definition that it might be overridden. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Thu, 5 Sep 2002, Jeff Davis wrote: > But a person is-a employee (allow me to momentarily step aside from > the rules of english grammer, if you would), and a person is-a > programmer. That's why I didn't call my table "job" :) [1] Certainly it's not the case that a person is-a job, by virtue of the fact that a person can have no job. Nor is it the case that a person is-a programmer; not all people are programmers. Perhaps you're reversing the sense of "is-a"? One says "subtype is-a supertype," not "supertype is-a subtype." But even reversing these, it's not the case that job is-a person, by virtue of the fact that you cannot use a job anywhere you can use a person. (A person can file his tax return, a job can't.) That might be a matter of bad mappings of object names to real-world concepts, though. As for "programmer is-a person," yes, you could model things that way if you really wanted to. But it's a bad way to do it because, as you point out, a person can change his job, or not have a job. Now what do you do with that programmer-subtype-of-person object you created? I think in this case English misled you: we do say that "he is a programmer," but what we really mean is that "one of the characteristics of that person is that he programs." So create a separate characteristic type and have the person object "have-a" as many or as few of those characteristics as you need. > I don't like the way some OO programming languages handle objects, if they > mean to say you can't change an object's type without performing a logical > data copy to a new object. That's not a problem with the programming language; that's you modelling things badly. > Take as an example python... it's easy to emulate other objects: just assign > to the attribute, even if it's not there yet, it'll add the attribute. Same > with python, it's providing room to expand for it's objects already, so why > do all the copying? Now compare with Java, and see why you'd be annoyed. It > has the facilities to change the objects all around, but you can't do it. Yes, you can't do it in Java because you Can't Do It in a language where you can specify static typing. If I have field that holds a String, I'm given a guarantee that, if I can put a reference in that field, it is and always will be a String. In non-statically-typed languages that give you the option of changing types, you might give a referenc to a string, change the objects type on me, and then I might blow up when I try to use it later. These bugs tend to be quite difficult to track down because the source and manifestation of the problem can be widely separated in code and in time. That's why most languages don't allow this. > ...when the more obvious logical path is to expand on the data you > already carry about an entity. Yes, that's the perfectly obvious path. And that's just what the relational model lets us do, and do very well. Why do you want to use an ill-fitting, error-prone model when you've already got something that works better? > [1] Come to think of it, the JOIN operator seems to, at least on a first > thought, represent the "has-a" relationship you describe. You bet! Hey, this relational stuff doesn't suck so badly after all, does it? Especially for a 30-year old theory. :-) cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: > On 5 Sep 2002, Hannu Krosing wrote: > > > Suppose you have a table CITIZEN with table-level constraint IS_GOOD > > which is defined as kills_not_others(CITIZEN). and there is table > > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY > > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD.... > > This I very much disagree with. > > In most object-oriented languages (Eiffel being a notable exception, IIRC), > you can't specify constraints on objects. But in a relational database, > you can specify constraints on tables, and it should *never* *ever* be > possible to violate those constraints, or the constraints are pointless. That's not how real world (which data is supposed to model) operates ;) As Greg already pointed out, there are two kinds of constraints - database integrity constraints (foreign key, unique, not null, check), which should never be overridden and business-rule constraints which should be overridable in child tables. one can argue that the latter are not constraints at all, but they sure look like constraints to me ;) To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. > So if I have a constraint that says, "no rows appearing in this > table will ever violate constraint X," and then you go and create > a way of inserting rows into that table that violate that constraint, > I think you've just made the database into a non-relational database. SQL standard constraints should be non-overridable. I still think that Constraint triggers should be overridable/dynamic. Or maybe it is better to just make the check function should be dynamically dispatched, so the constraint will always hold, it just can mean different things for different types. > I really don't want to break postgres' relational side for some > inheritance features of dubious utility. Constraints should be explicitly > removed from tables if they are no longer needed, not implicitly removed > through the creation of another table. > > I think we should settle this point before going any further. It seems that the dynamic dispatch of trigger function should be enough for business-rule constraints. And it is also simpler and cleaner (both conceptually and to implement) if constraints themselves are not overridable. So in my CAMPAIGN_GOODS example you just have different valid_prices(GOODS) and valid_prices(CAMPAIGN_GOODS), but one constraint on GOODS which states that price must be valid . Doing it this way ensures that you are not able to have a record in GOODS for which valid_price(ROW) does not hold. If you don't want inherited tables to be able to override valid_price() use it in CHECK constraint in GOODS, which should use the valid_prices(cast(ROW as GOODS)) for any inherited type. ----------------- Hannu
On 6 Sep 2002, Hannu Krosing wrote: > > In most object-oriented languages (Eiffel being a notable exception, IIRC), > > you can't specify constraints on objects. But in a relational database, > > you can specify constraints on tables, and it should *never* *ever* be > > possible to violate those constraints, or the constraints are pointless. > > That's not how real world (which data is supposed to model) operates ;) Sure it is. Please don't blame the language for being wrong when you incorrectly model things for your purposes. To chose a much simpler and more obvious example: if you stored birthdate as a date only, and someone complained that you're not born all day, but at a particular time on that day, you don't blame the language for having the date type not store the time of day. You fix your problem to use both a date and a time to store that value. If the language specifies that contstraints on tables are not to be violated, then don't use those constraints when you don't want them. > To elaborate on Gregs example if you have table GOODS and under it a > table CAMPAIGN_GOODS then you may place a general overridable constraint > valid_prices on GOODS which checks that you dont sell cheaper than you > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so > you override the constraint for CAMPAIGN_GOODS. This looks like a classic case of incorrect modelling to me. Does the good itself change when it becomes a campaign_good? No. The price changes, but that's obviously not an integral part of the good itself. So separate your price information from your good information, and then you can do things like have campaign prices, multiple prices per good (since you probably want to keep the original price information as well), and so on. I'm really getting the feeling a lot of these applications that want table inheritance want it just to be different, not because it provides anything useful. I am completely committed to object-oriented programming, and use inheritance heavily, so it's not that I don't understand or like the concepts. But just because a concept works well in one type of use does not mean it will do any good, or even not do harm, when brought into a completely different world. > SQL standard constraints should be non-overridable. I still think that > Constraint triggers should be overridable/dynamic. I still don't like it. Eiffel had good reasons for making the constraints non-overridable. Other OO languages don't have constraints, or they would probably do the same. That said, I could live with dynamic dispatch, if the default were to make it non-dynamic, and you had to add a special flag to make it dynamic. That way it would be obvious to the casual user or a DBA familiar with other databases but not postgres that something unusual is going on. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Fri, 2002-09-06 at 09:53, Curt Sampson wrote: > > If the language specifies that contstraints on tables are not to be > violated, then don't use those constraints when you don't want them. But what _should_ i use then if i want the same business rule on most top-level types, but a changed one on some down the hierarchy ? > > To elaborate on Gregs example if you have table GOODS and under it a > > table CAMPAIGN_GOODS then you may place a general overridable constraint > > valid_prices on GOODS which checks that you dont sell cheaper than you > > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so > > you override the constraint for CAMPAIGN_GOODS. > > This looks like a classic case of incorrect modelling to me. Does the > good itself change when it becomes a campaign_good? No. The price > changes, but that's obviously not an integral part of the good itself. Perhaps we mean different things by good. I meant a GOOD to be a THING bought with the purpose of reselling. Price (actually prices: selling_price and buying_price) is what makes it a GOOD and thus it is an integral part of it. > So separate your price information from your good information, and then > you can do things like have campaign prices, multiple prices per good > (since you probably want to keep the original price information as > well), and so on. It does not solve the problem described above - the price at which the good is soled is still constrained differently for orninary and campaign goods. in standard relational model you would make the distinction inside the constraint (CHECK (selling_price > buying_price) OR is_campaign_good) but this localises the check in wrong place - in OO model I'd expect it to be possible to define the constraint near the child type, not change the parent constraint each time I derive new child types. > I'm really getting the feeling a lot of these applications that > want table inheritance want it just to be different, not because > it provides anything useful. As with any other inheritance, it is just a way to organize stuff. In case of being able to override constraints for child tables it can also be a significant performance boost - if you have 10 000 000 goods in a table you don't want to change a constraint on GOODS to allow campaign goods to be sold cheaper than bought as it would have to check all goods for validity according to new constraint - putting the constraint on just CAMPAIGN_GOODS will enable the DB engine to check just tuples in CAMPAIGN_GOODS. > I am completely committed to object-oriented programming, and use > inheritance heavily, so it's not that I don't understand or like the > concepts. But just because a concept works well in one type of use does > not mean it will do any good, or even not do harm, when brought into a > completely different world. Surely great caution is needed when defining the desired behaviour. > > SQL standard constraints should be non-overridable. I still think that > > Constraint triggers should be overridable/dynamic. > > I still don't like it. Eiffel had good reasons for making the > constraints non-overridable. Other OO languages don't have constraints, > or they would probably do the same. > > That said, I could live with dynamic dispatch, if the default were > to make it non-dynamic, and you had to add a special flag to make it > dynamic. That way it would be obvious to the casual user or a DBA > familiar with other databases but not postgres that something unusual is > going on. That seems about the right compromise between constraining and developer freedom. ------------- Hannu
On Thu, 2002-09-05 at 15:51, Hannu Krosing wrote: > On Fri, 2002-09-06 at 03:19, Greg Copeland wrote: > > > > What about the concept of columns being public or private? That is, > > certain columns may not be inherited by a child? Any thought to such a > > concept? Perhaps different types of table inheritance can be considered > > in our model...has-a, is-a, etc... > > I can't fit this in my mental model of table inheritance for two reasons > > 1) all parent table columns must be present in child Okay, I must admit, I'm not really sure why. If we look at it in a physical versus logical manner, even if it's physically there, why must it be logically exposed? Can you help me understand why it would even need to physically be there. After all, if a child can't update it, they don't need to see it. > > 2) granting some right to parent should automatically allow selecting > from children Unless the parent deemed it inappropriate access (private)? If a column were deemed private, that would have a couple of stipulations on it. That is, it would have to ensure that "NOT NULL" where not one of the constraints, or, if it did, ensure that a default value were also provided. > > both are required for select/insert/update/delete to work on table and > its children (i.e. without ONLY) > > > But maybe i just need to think more about it ;) > Well, I guess I'm lagging behind you on this manner. Perhaps "holding my hand" and explaining it a bit will allow you to work through it some more and help bring me in line with what you're thinking. Greg
On Fri, 2002-09-06 at 07:53, Hannu Krosing wrote: > On Fri, 2002-09-06 at 09:53, Curt Sampson wrote: > > This looks like a classic case of incorrect modelling to me. Does the > > good itself change when it becomes a campaign_good? No. The price > > changes, but that's obviously not an integral part of the good itself. > > Perhaps we mean different things by good. I meant a GOOD to be a THING > bought with the purpose of reselling. Price (actually prices: > selling_price and buying_price) is what makes it a GOOD and thus it is > an integral part of it. No matter now you look at the example, invalidating it does not address the issue raised as it still exists. Either way, Hannu and I seem to agree that some class of constraints need to be able to be overridden. > In case of being able to override constraints for child tables it can > also be a significant performance boost - if you have 10 000 000 goods > in a table you don't want to change a constraint on GOODS to allow > campaign goods to be sold cheaper than bought as it would have to check > all goods for validity according to new constraint - putting the > constraint on just CAMPAIGN_GOODS will enable the DB engine to check > just tuples in CAMPAIGN_GOODS. I had not considered this before. Does that still hold true if we go with a parent contains all columns implementation? Of are you simply saying that it doesn't matter as when the constraint were applied it would only scan the rows the below to the child? Perhaps this doesn't matter for this portion of the conversation. But hey, I was curious. :) > > > > SQL standard constraints should be non-overridable. I still think that > > > Constraint triggers should be overridable/dynamic. > > > > I still don't like it. Eiffel had good reasons for making the > > constraints non-overridable. Other OO languages don't have constraints, > > or they would probably do the same. Well Curt, as you outlined above (clipped out) about it being a different world...I think also applies here. IMO, we are treading lightly on new and perhaps thin ground so we need to be careful that we apply common parallels and idioms only we are certain that they need apply. What I'm trying to say is, just because it's not allowed in Eiffel does have to mean the same applies here. > > > > That said, I could live with dynamic dispatch, if the default were > > to make it non-dynamic, and you had to add a special flag to make it > > dynamic. That way it would be obvious to the casual user or a DBA > > familiar with other databases but not postgres that something unusual is > > going on. > > That seems about the right compromise between constraining and developer > freedom. > I agree. That does appear to be pointing us in a conservatively sane and safe direction. Greg
> On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: > > On 5 Sep 2002, Hannu Krosing wrote: > > > > > Suppose you have a table CITIZEN with table-level constraint IS_GOOD > > > which is defined as kills_not_others(CITIZEN). and there is table > > > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY > > > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD.... > > > > This I very much disagree with. > > > > In most object-oriented languages (Eiffel being a notable exception, IIRC), > > you can't specify constraints on objects. But in a relational database, > > you can specify constraints on tables, and it should *never* *ever* be > > possible to violate those constraints, or the constraints are pointless. > > That's not how real world (which data is supposed to model) operates ;) > > As Greg already pointed out, there are two kinds of constraints - > database integrity constraints (foreign key, unique, not null, check), > which should never be overridden and business-rule constraints which > should be overridable in child tables. > > one can argue that the latter are not constraints at all, but they sure > look like constraints to me ;) > > To elaborate on Gregs example if you have table GOODS and under it a > table CAMPAIGN_GOODS then you may place a general overridable constraint > valid_prices on GOODS which checks that you dont sell cheaper than you > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so > you override the constraint for CAMPAIGN_GOODS. What that tells me is that the constraint, valid_prices, shouldn't have been on GOODS in the first place. If it is not a legitimate constraint for the children, then it is not a legitimate constraint for the parent. In human inheritance, if you marry someone with "funny coloured skin," you don't get to choose that your children won't have "funny coloured skin." That's a pretty forcible "constraint." :-). For the GOODS situation, the constraint ought not to be on GOODS in the first place. There ought to be a table ORDINARY_GOODS, or some such thing, to which the constraint applies, and from which CAMPAIGN_GOODS will _not_ be inheriting. > > So if I have a constraint that says, "no rows appearing in this > > table will ever violate constraint X," and then you go and create > > a way of inserting rows into that table that violate that constraint, > > I think you've just made the database into a non-relational database. > > SQL standard constraints should be non-overridable. I still think that > Constraint triggers should be overridable/dynamic. > > Or maybe it is better to just make the check function should be > dynamically dispatched, so the constraint will always hold, it just can > mean different things for different types. Or maybe if someone is doing an Object Oriented design, and making extensive use of inheritance, they'll need to apply constraints in a manner that allow them to be properly inherited. -- (concatenate 'string "aa454" "@freenet.carleton.ca") http://cbbrowne.com/info/ If a cow laughed, would milk come out its nose?
On Fri, 2002-09-06 at 08:57, cbbrowne@cbbrowne.com wrote: > > On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: > > > On 5 Sep 2002, Hannu Krosing wrote: > > > > To elaborate on Gregs example if you have table GOODS and under it a > > table CAMPAIGN_GOODS then you may place a general overridable constraint > > valid_prices on GOODS which checks that you dont sell cheaper than you > > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so > > you override the constraint for CAMPAIGN_GOODS. > > What that tells me is that the constraint, valid_prices, shouldn't have been > on GOODS in the first place. If it is not a legitimate constraint for the > children, then it is not a legitimate constraint for the parent. > I don't agree with you on that point. This concept is common to many OO-implementations. Unless you can come up with a powerful argument as to why our "to-be" picture should never do this, I'm less than convinced. > In human inheritance, if you marry someone with "funny coloured skin," you > don't get to choose that your children won't have "funny coloured skin." > That's a pretty forcible "constraint." :-). > Fine, but that only works for YOUR specific example. In that example, the color constraint should be non-virtual, meaning, the child should not be able to change it. On the other hand, if I replace "human" with "metal product", hopefully I won't be stuck with gun metal gray for every derived product. Hopefully, somewhere along the lines, I'll be able to override the parent's color constraint. > > Or maybe it is better to just make the check function should be > > dynamically dispatched, so the constraint will always hold, it just can > > mean different things for different types. > > Or maybe if someone is doing an Object Oriented design, and making extensive > use of inheritance, they'll need to apply constraints in a manner that allow > them to be properly inherited. The problem with that assumption is that there is normally nothing wrong with having seemingly mutually exclusive sets of *business rules* for a parent and child. Greg
Oops! greg@CopelandConsulting.Net (Greg Copeland) was seen spray-painting on a wall: > --=-eu74lKXry3SVx8eZ/qBD > Content-Type: text/plain > Content-Transfer-Encoding: quoted-printable > On Fri, 2002-09-06 at 08:57, cbbrowne@cbbrowne.com wrote: >> > On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: >> > > On 5 Sep 2002, Hannu Krosing wrote: >> > To elaborate on Gregs example if you have table GOODS and under it a >> > table CAMPAIGN_GOODS then you may place a general overridable constraint >> > valid_prices on GOODS which checks that you dont sell cheaper than you >> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so >> > you override the constraint for CAMPAIGN_GOODS. >> What that tells me is that the constraint, valid_prices, shouldn't >> have been on GOODS in the first place. If it is not a legitimate >> constraint for the children, then it is not a legitimate constraint >> for the parent. > I don't agree with you on that point. This concept is common to > many OO-implementations. Unless you can come up with a powerful > argument as to why our "to-be" picture should never do this, I'm > less than convinced. If the plan is for table CAMPAIGN_GOODS to virtually be a view on GOODS, then I'd say it _is_ necessary. >> In human inheritance, if you marry someone with "funny coloured skin," yo= > u=20 >> don't get to choose that your children won't have "funny coloured skin."= > =20=20 >> That's a pretty forcible "constraint." :-). >>=20 Is there something broken with your mailer? It's reformatting quotes rather horribly... > Fine, but that only works for YOUR specific example. In that > example, the color constraint should be non-virtual, meaning, the > child should not be able to change it. On the other hand, if I > replace "human" with "metal product", hopefully I won't be stuck > with gun metal gray for every derived product. Hopefully, somewhere > along the lines, I'll be able to override the parent's color > constraint. That happens by _adding_ an additional characteristic, presumably that of "what kind of paint the metal is covered with." That doesn't override the fundamental constraint that if it's a metal product, there _will_ be metallic properties. If you decide to add in some "non-metallic" products, then it would be _silly_ to have them inherit all their characteristics from "METAL_PRODUCTS;" they should head back up the class hierarchy and inherit their basic characteristics from the _appropriate_ parent. Reality, with the "GOODS/CAMPAIGN_GOODS" example, is that GOODS isn't the appropriate parent class for CAMPAIGN_GOODS. Both should be inheriting the common characteristics from some common ancestor. If that is done, then there's nothing to "override." >> > Or maybe it is better to just make the check function should be >> > dynamically dispatched, so the constraint will always hold, it just can >> > mean different things for different types. >>=20 >> Or maybe if someone is doing an Object Oriented design, and making extens= > ive=20 >> use of inheritance, they'll need to apply constraints in a manner that al= > low=20 >> them to be properly inherited. > The problem with that assumption is that there is normally nothing > wrong with having seemingly mutually exclusive sets of *business > rules* for a parent and child. If the rules are totally different, it begs the question of why they _should_ be considered to be related in a "parent/child" relationship. It may well be that they _aren't_ related as "parent/child." They may merely be "cousins," sharing some common ancestors. -- (concatenate 'string "chris" "@cbbrowne.com") http://cbbrowne.com/info/spreadsheets.html "Note that if I can get you to `su and say' something just by asking, you have a very serious security problem on your system and you should look into it." -- Paul Vixie, vixie-cron 3.0.1 installation notes
There was a comment earlier that was not really addressed. What can you do with table inheritance that you can not do with a relational implementation? Or what would work *better* as inheritance? (you define better) This is a genuine question, not a snarky comment. I really want to know. This is the reason I can think of to use inheritance: Several tables have a common set of attributes and there is some reason for these tables to be separate AND there is some reason for the common columns to be queried en masse. What kinds of "some reasons" are there, though? And if my condition for using table inheritance is lacking or misguided, what should be the criteria for using table inheritance? Creating indexes across tables is a project. Is it the most important project? Will it benefit the most users? Will it benefit any users? Theory is great and important, but if no one uses the functionality, who cares? If these changes will enable people to use the functionality that until now had been too much of a PITA then it might be worth it. However, I suspect the majority of people who would use these changes are participating in these discussions. These features were never widely used in Illustra nor Informix although their implementations were a little smoother imho. To weigh in on the constraints issues, it seems problematic that currently some constraints (check) are inherited and others are not (foreign keys). The chcheers,oice of which ones are or aren't is clear to people familiar with the implementation but what about the rest of the world who just want some consistent rule. I also agree with the people who say, if we inherit constrainsts, then we must be able to override them in the subtables. I like the suggested "LOCAL" keyword, myself. cheers, elein :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: elein@norcov.com (510)543-6079 "Taking a Trip. Not taking a Trip."--anonymous :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
On Fri, 2002-09-06 at 11:05, cbbrowne@cbbrowne.com wrote: > Oops! greg@CopelandConsulting.Net (Greg Copeland) was seen spray-painting on a wall: > >> That's a pretty forcible "constraint." :-). > >>=20 > > Is there something broken with your mailer? It's reformatting quotes > rather horribly... Hmm...not that I know off. Never had complaints before anyways. Looks like an issue with MIME contents...perhaps your mailer doesn't properly parse some MIME and/or mine is hosing it some how. Not really sure. > Reality, with the "GOODS/CAMPAIGN_GOODS" example, is that GOODS isn't > the appropriate parent class for CAMPAIGN_GOODS. Both should be > inheriting the common characteristics from some common ancestor. If > that is done, then there's nothing to "override." > You can complain about and redefine the model to suit your needs all day long and get no where. It doesn't change the need for it. Fact is, it would be nice to allow. Fact is, OO-implementations tend to allow this. I'm quite happy to let you go to every OO computer language camp and inform them that they've done it all wrong. ;) Citing that a specific example is all wrong hardly invalidates the concept. Since we are pretty much at the conceptual stage, I welcome a conceptual argument on why this is bad and should never be done. Please, be high level and generic. After all, I too can give you a hundred specific reasons why a cat is not dog (i.e. bad model)...but it does nothing to facilitate the topic at hand. > > The problem with that assumption is that there is normally nothing > > wrong with having seemingly mutually exclusive sets of *business > > rules* for a parent and child. > > If the rules are totally different, it begs the question of why they > _should_ be considered to be related in a "parent/child" relationship. Because this is how the real world works. Often there are exceptions to the rules. When these rules differ, I've not seen a valid high level conceptual reason that should prevent it. Example: animalquadruped (has 4-trunk limbs) dog injuredDog (has 0 or more trunk limbs) Hopefully we can agree that a dog is still a dog even if it only has three legs? Hopefully you'll realize this was given to illustrate an example and to prove a point. Sometimes a model needs to allow for exceptions to the rule. You can argue that a three-legged dog is no longer a quadruped but I prefer to believe that it is a quadruped which just happens to be an exception to the rule. > > It may well be that they _aren't_ related as "parent/child." They may > merely be "cousins," sharing some common ancestors. Yes, it's true. Sometimes the wrong model is applied but that hardly invalidates the concept or alleviates the need. Regards, Greg Copeland
On Fri, 2002-09-06 at 19:00, elein wrote: > > > There was a comment earlier that was not really addressed. > What can you do with table inheritance that you can not do > with a relational implementation? Or what would work *better* > as inheritance? (you define better) There is nothing that you cannot do in some way; that way may not be very convenient compared to the use of inheritance. I consider simplicity to be preferable to conceptual purity. > This is a genuine question, not a snarky comment. I really > want to know. This is the reason I can think of to use > inheritance: Several tables have a common set of attributes and > there is some reason for these tables to be separate AND there > is some reason for the common columns to be queried en masse. > What kinds of "some reasons" are there, though? And if my > condition for using table inheritance is lacking or misguided, what should > be the criteria for using table inheritance? I use it when a group of tables are closely related; they are all members of some higher class. For example: person <.......................> address | +--------------+--------------+ | | organisation individual <......> pay_tax | | +--------+--------+ +---------+---------+ | | | | | | customer supplier ...etc... staff homeworker ...etc... | +----+-------------+ | | home_customer export_customer It is convenient to use a higher class when you are interested in all its members and only in the attributes of the higher class. So I can say SELECT * FROM person,address WHERE address.person = person.id AND address.town = 'London'; to get all rows for people in London. I will only get those attributes that are in person itself; if I want to know about credit limits, that is only relevant in the customer hierarchy and I have to SELECT from customer instead.. Similarly, I can use the whole customer hierarchy when changing or reporting on outstanding customer balances. If foreign key relations were valid against an inheritance tree, I could implement it for a table of addresses referencing the highest level (every person has an address) and of pay and tax records at the individual level. These don't change as you go down the hierarchy, but a purely relational implementation has to be redone at each level. A reciprocal relation requires an extra table to hold all the hierarchy's keys and that in turn needs triggers to keep that table maintained. (I.e., person should have a FK reference to address and address to person; instead, address needs a reference to person_keys, which I have to create because FK against a hierarchy isn't valid.) The lack of inherited RI makes the design more complex and more difficult to understand. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For whosoever shall call upon the name of the Lord shall be saved." Romans 10:13
At 08:33 AM 9/7/02, Oliver Elphick wrote: >On Fri, 2002-09-06 at 19:00, elein wrote: > > > > > > There was a comment earlier that was not really addressed. > > What can you do with table inheritance that you can not do > > with a relational implementation? Or what would work *better* > > as inheritance? (you define better) > >There is nothing that you cannot do in some way; that way may not be >very convenient compared to the use of inheritance. I consider >simplicity to be preferable to conceptual purity. yes, simplicity is a very reasonable criteria for better. > > This is a genuine question, not a snarky comment. I really > > want to know. This is the reason I can think of to use > > inheritance: Several tables have a common set of attributes and > > there is some reason for these tables to be separate AND there > > is some reason for the common columns to be queried en masse. > > What kinds of "some reasons" are there, though? And if my > > condition for using table inheritance is lacking or misguided, what should > > be the criteria for using table inheritance? In non-OO terms, you have both reasons for tables to be separate and reasons to query an entire hierarchy. Your exact reasons are clear and reasonable. This is helpful. >I use it when a group of tables are closely related; they are all >members of some higher class. For example: > > person <.......................> address > | > +--------------+--------------+ > | | > organisation individual <......> pay_tax > | | > +--------+--------+ +---------+---------+ > | | | | | | > customer supplier ...etc... staff homeworker ...etc... > | > +----+-------------+ > | | >home_customer export_customer > >It is convenient to use a higher class when you are interested in all >its members and only in the attributes of the higher class. So I can >say > > SELECT * FROM person,address > WHERE address.person = person.id AND > address.town = 'London'; > >to get all rows for people in London. I will only get those attributes >that are in person itself; if I want to know about credit limits, that >is only relevant in the customer hierarchy and I have to SELECT from >customer instead.. > >Similarly, I can use the whole customer hierarchy when changing or >reporting on outstanding customer balances. I don't think table inheritance will "go away" and so being consistent about the indexes and constraints is necessary in order to keep its usage simpler. This might lessen the PITA factor for a few more people, but we should prioritize the project. I think few people have put the thought into it that you have. >If foreign key relations were valid against an inheritance tree, I could >implement it for a table of addresses referencing the highest level >(every person has an address) and of pay and tax records at the >individual level. These don't change as you go down the hierarchy, but >a purely relational implementation has to be redone at each level. A >reciprocal relation requires an extra table to hold all the hierarchy's >keys and that in turn needs triggers to keep that table maintained. >(I.e., person should have a FK reference to address and address to >person; instead, address needs a reference to person_keys, which I have >to create because FK against a hierarchy isn't valid.) The lack of >inherited RI makes the design more complex and more difficult to >understand. > >-- >Oliver Elphick Oliver.Elphick@lfix.co.uk >Isle of Wight, UK >http://www.lfix.co.uk/oliver >GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "For whosoever shall call upon the name of the Lord > shall be saved." Romans 10:13 > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: elein@norcov.com (510)543-6079 "Taking a Trip. Not taking a Trip."--anonymous :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: