Обсуждение: Domains (Was [PERFORM] Views With Unions)

On Fri, 1 Aug 2003, Tom Lane wrote:

> Domains were a work-in-progress in 7.3, and to some extent still are.
> Please try to test out 7.4beta and let us know about deficiencies you
> find.

Are domains user defined types? That they seem to be based on what I see
on the docs.

Any drawbacks to using them?

Right now I have a new database I am making and wanted some consistency
accros some tables. Currently I used inheritance to enforce the consitency
since a good number of fields needed to be common among the tables AND the
inheritted tables are basically a supperset of the data, so some times I
would want to access the inheritted tables and other times the parent/main
table.

On Fri, 2003-08-01 at 12:26, Francisco J Reyes wrote:
> On Fri, 1 Aug 2003, Tom Lane wrote:
[snip]
> accros some tables. Currently I used inheritance to enforce the consitency
> since a good number of fields needed to be common among the tables AND the
> inheritted tables are basically a supperset of the data, so some times I
> would want to access the inheritted tables and other times the parent/main
> table.

Isn't this when you'd really want child tables, instead?

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

Francisco J Reyes wrote:
> Are domains user defined types? That they seem to be based on what I see
> on the docs.

They are similar a bit to user defined types, but there are some
important differences.

First of all, domains are based on other datatypes, so you would
have to have an existing datatype that is a sufficiently close match.
Secondly, domains can be mixed directly with the datatype they
are based on and other domains that are based on that datatype.
User defined types require you to first cast them (which in turn
implies a casting function needs to be defined) to the other/a
common type before you can mix them.

Overall, I tend to see them more as macro's then as real
datatypes. Typically I use them to verify that data is properly
formatted (like the phone number example).

Please note that the SQL:1999 concept of user defined types is
quite different from the one in PostgreSQL (in his book "Advanced
SQL:1999" Jim Melton even warns that domains may be deprecated in
future SQL standards in favour of SQL-style user defined types).

Jochem

On Sat, 2003-08-02 at 13:22, Francisco J Reyes wrote:
> On Fri, 1 Aug 2003, Ron Johnson wrote:
>
> > On Fri, 2003-08-01 at 12:26, Francisco J Reyes wrote:
> > > On Fri, 1 Aug 2003, Tom Lane wrote:
> > [snip]
> > >  Currently I used inheritance to enforce the consitency
> > > since a good number of fields needed to be common among the tables AND the
> > > inheritted tables are basically a supperset of the data, so some times I
> > > would want to access the inheritted tables and other times the parent/main
> > > table.
> >
> > Isn't this when you'd really want child tables, instead?
>
>
> I think both ways can accomplish the same (if not very simmilar
> functionality), however I find using inherittance easier.
> Not really sure about efficiency though.
>
> A simple example of the type of design I am planning to do would be:
>
> Table A
> Userid
> date entered
> last changed
>
>
> Table B inherited from A(additional fields)
> person name
> birthday
>
> Table C inherited from A(additional fields)
> book
> isbn
> comment
>
> I plan to keep track of how many records a user has so with inherittance
> it's easy to do this. I can count for the user in Table A and find out how
> many records he/she has or I can count in each of the inheritted tables
> and see how many there are for that particular table.
>
> Inheritance makes it easier to see everything for a userid or just a
> particular type of records.

But isn't this what LEFT OUTER JOIN is for?

Attached is a zip of the sql and results of what I mean.

Plain inner joins or LOJ with "WHERE {B|C}.whatever IS NOT NULL"
also pare things dawn.

Of course, just yesterday, in a post on -general or -performance,
I read that LEFT OUTER JOIN isn't particularly efficient in PG.

Also, wouldn't it be odd to have a userid without a name?  So,
why isn't table_b combined with table_a?  But all circumstances
are different, I guess...

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

On Sat, 2003-08-02 at 11:29, Jochem van Dieten wrote:
> Francisco J Reyes wrote:
> > Are domains user defined types? That they seem to be based on what I see
> > on the docs.
>
> They are similar a bit to user defined types, but there are some
> important differences.
>
> First of all, domains are based on other datatypes, so you would
> have to have an existing datatype that is a sufficiently close match.
> Secondly, domains can be mixed directly with the datatype they
> are based on and other domains that are based on that datatype.
> User defined types require you to first cast them (which in turn
> implies a casting function needs to be defined) to the other/a
> common type before you can mix them.
>
> Overall, I tend to see them more as macro's then as real
> datatypes. Typically I use them to verify that data is properly
> formatted (like the phone number example).

They are also (primarily?) used on other DBMSs to "prove correctness".
Thus, for example, everywhere you see a field of type TXNID_DOM,
you know that it is a "transaction id", no matter what the field
name is.

It's also theoretically possible to make type modifications simpler.
Say you have domain TXNID_DOM of type INTEGER, and you are running
up against the 2,100,000,000 threshold:
   ALTER DOMAIN TXNID_DOM BIGINT;

Now, you have 10^9 more transaction ids.  Of course, the down side
is that *every* table with a field of type TXNID_DOM is modified,
and that might take a *REALLY*LONG*TIME*...

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

On Fri, 1 Aug 2003, Ron Johnson wrote:

> On Fri, 2003-08-01 at 12:26, Francisco J Reyes wrote:
> > On Fri, 1 Aug 2003, Tom Lane wrote:
> [snip]
> >  Currently I used inheritance to enforce the consitency
> > since a good number of fields needed to be common among the tables AND the
> > inheritted tables are basically a supperset of the data, so some times I
> > would want to access the inheritted tables and other times the parent/main
> > table.
>
> Isn't this when you'd really want child tables, instead?


I think both ways can accomplish the same (if not very simmilar
functionality), however I find using inherittance easier.
Not really sure about efficiency though.

A simple example of the type of design I am planning to do would be:

Table A
Userid
date entered
last changed


Table B inherited from A(additional fields)
person name
birthday

Table C inherited from A(additional fields)
book
isbn
comment

I plan to keep track of how many records a user has so with inherittance
it's easy to do this. I can count for the user in Table A and find out how
many records he/she has or I can count in each of the inheritted tables
and see how many there are for that particular table.

Inheritance makes it easier to see everything for a userid or just a
particular type of records.

On Sat, 2 Aug 2003, Ron Johnson wrote:

> > Inheritance makes it easier to see everything for a userid or just a
> > particular type of records.
>
> But isn't this what LEFT OUTER JOIN is for?


Yes but the more tables you have the more cumbersome it would become to do
with outer joins.
Imagine a parent table and 20 children tables. To get a count of all
records the user has I either have to do a nasty/ugly union or do 20
counts and then add them (ie doing the separate counts and keeping
track of them with a language like PHP)

> Of course, just yesterday, in a post on -general or -performance,
> I read that LEFT OUTER JOIN isn't particularly efficient in PG.

And it's probably worse when many tables are involved.


> Also, wouldn't it be odd to have a userid without a name?  So,
> why isn't table_b combined with table_a?

I have a separate table with user information.
The main reason I thought of inherittance was because I need to do
accounting and keep track of how many records a user has for certain type
of data or in total. Inheritance makes this really easy.

Table A, B and C are not combined because B, C and onward have totally
different type of data and they are not one to one.

There are times when children tables make more sense like:

*person table
-person id
-name
-address

*phones
-person id
-phone type (ie fax, home, work)
-area
-phone

*emails
-person id
-email type (home, work)
-email

In my opinion a case like that is best handled with children tables.
Specially if there are only a couple of childre tables.

On my case I have about 8 inherited tables and what I believe inheritance
does for me is:
* Easy way to count both a grand total or a table per inherited table.
* Easy to work with each inheritted table, which will be very often.
* Much simpler queries/reporting

On Sat, 2 Aug 2003, Jochem van Dieten wrote:

> Francisco J Reyes wrote:
> > Are domains user defined types? That they seem to be based on what I see
> > on the docs.
>
> They are similar a bit to user defined types,
......
>
> Please note that the SQL:1999 concept of user defined types is
> quite different from the one in PostgreSQL (in his book "Advanced
> SQL:1999" Jim Melton even warns that domains may be deprecated in
> future SQL standards in favour of SQL-style user defined types).


Thanks for the explanation and the warning. Based on your feedback, don't
quite see how domains would be usefull to me so I will not use them for
now.