Re: Best practice on inherited tables - Mailing list pgsql-general
| From | Chris Travers |
|---|---|
| Subject | Re: Best practice on inherited tables |
| Date | |
| Msg-id | CAKt_Zfs1N2xCZwj_ryjYy1yNkvmAc911kP3F_QbTqGg6e=kLdw@mail.gmail.com Whole thread Raw |
| In response to | Re: Best practice on inherited tables (Julian <tempura@internode.on.net>) |
| List | pgsql-general |
On Sun, May 19, 2013 at 4:44 AM, Julian <tempura@internode.on.net> wrote:
On 19/05/13 13:02, Chris Travers wrote:INHERIT is feature that immediately gets interest from an OOP
>
> I actually think that bringing some object-oriented principles into
> database design can result in some very useful things, provided that one
> remembers that applications are modelling behavior while databases are
> modelling information (and so the two behave quite differently). The OO
> principles are helpful particularly when looking at derived data.
>
perspective, however its usefulness, IMO, is naught. I agree that
applications model behavior (do this) but databases don't model
information, their core purpose is to store information in such a away
that the information may be modeled how you see fit, using a query
language (SQL).
I actually find table inheritance quite useful in a fair number of cases. Here are some examples where it is obviously useful:
1. In LedgerSMB we use it to avoid a global notes table by using the notes table as an abstract (no rows) table which exists only to create consistent interfaces for notes that would attach elsewhere. So this enables us to do soemthing like:
CREATE TABLE note (
id serial,
subject text,
note_class_id int references note_class(id),
content text,
ref_key int
);
CREATE TABLE invoice_note (
primary key (id, note_class_id),
unique(id),
ref_key references transactions(id)
) inherits (note);
We currently have I think 5 tables which inherit note in this way, and this avoids the headaches associated with a global notes table.
2. Useful in table composition.
I have an internal application which is designed with some fairly wide tables because of relatively flat functional dependencies. These are transitive dependencies, but there are dependencies across different groups, so I can define a "normalized" schema, and then compose denormalized tables through inheritance. This makes breaks wide tables into digestable chunks, and it provides interfaces specific to those chunks.
3. Useful with "table methods" for consistent interfaces. For example, we could do something like:
CREATE OR REPLACE FUNCTION tsvector(note) RETURNS tsvector AS
$$
SELECT to_tsvector($1.subject || ' ' || $1.content);
$$ language sql;
Then we can:
SELECT ... FROM invoice_note WHERE note.tsvector @@ plainto_tsquery('looking for something');
Storing data to how you wish to receive it, is not realizing the true
power of a RDBMS like postgres.
No, that's not the point.
The point is in the ability to store data so that interesting information can be derived from what you have stored. It is often also decoupled entirely from app design. There are certain kinds of problems where you can use inheritance to safely do things with data modelling in simple ways which would take a great deal of complexity to do without it. The notes example is a good example. Without inheritance you could either have a bunch of mapping tables (in which case you can't verify that each note is attached to only one thing), or you can add a bazillion foreign keys to the table. Now what you lose is a clean way to do foreign keys into the notes table and include subtables, but in this case, that really doesn't matter.
The principles of OOP are to be brought into database design with risk.
Sure. One wants to master relational design first.
INHERITS actual usefulness is merely an alternative to already accepted
relational database principles and actually goes *against* the
relational model. Postgres has alot of features. So even without the
current caveats I warn against using INHERIT when I see it mentioned.
Not always. Let me give you a simple contrived example.
CREATE TABLE foo (bar int not null, baz text not null);
CREATE TABLE foobar (foo foo, comment text, check((foo).bar is not null and (foo).baz is not null);
INSERT INTO foobar (foo, comment) values (row(1, 'testing')::foo, 'this is a test of nested tuple structures);
Note that example (nested tuple structures) doesn't actually violate the relational model, and it does not violate first normal forms atomicity requirement (since foo is a single value of a domain, it no more violates atomicity than datetime does). Now, check constraints are a little more complex in this model but it does work. For example, not null constraints on foo do not cascade to foobar.foo.
With table inheritance however, we can actually handle this better:
CREATE TABLE foobar (comment text) inherits (foo);
Now the not null constraints properly cascade but instead of SELECT (foo).* from foobar; we just do:
SELECT (f::foo).* from foobar f;
Now this doesn't really violate the relational model any more than the first one does. We might use a base table for a group of commonly occuring fields which have a similar meaning but occur in which the functional dependencies from these columns on out are variable.
The key thing though is that the discussion on inheritance needs to stay at the database level. One shouldn't be modelling database data storage structures based on application structures (there we are in total agreement).
Best Wishes,
Chris Travers
pgsql-general by date: