Обсуждение: Database Design Theory - PostgreSQL Custom Types

Merlin has helped me understand custom data types in PostgreSQL a lot
better, but I have some more questions I was hoping the list might be
able to help with.

I'm trying to come up with some good rules of thumb that can help me
determine when it is practical to implement a custom type on
PostgreSQL.

After talking to Merlin, I've come up with my first rule:

If the object or entity you are modeling could be represented by more
than one table in the database, don't use a custom data type.
Represent the object with multiple tables and link the tables together
with relationships via foreign keys.

Here is another situation that has me puzzled. I need to store
information about angles in my database. I want to store the angles in
Surveyors units, namely degrees, minutes, and seconds. So none of the
built in numeric types will work, unless I want to do a conversion,
which I don't.

I know I could represent angles in a table with 4 columns. A bigserial
or serial column for the primary key, a degrees column, a minutes
column, and a seconds column.

However, the rule of thumb I mentioned above doesn't apply, because I
can store all the information about an angle in one table. I know I
could make a custom data type for angles. Then I can just store the
angles in a column of any other table, instead of using a foreign key
ties to an angles table.

Both methods will work, but which one is the best? Why is that method the best?

Thanks for any discussion or information.

Scott Huey

On Wed, Jul 26, 2006 at 11:09:22AM -0700, Redefined Horizons wrote:
> I'm trying to come up with some good rules of thumb that can help me
> determine when it is practical to implement a custom type on
> PostgreSQL.

To me a custom datatype is useful when there is a set of values where
you have operations that map to other values in the set.

So numbers and strings are good because you have operations on them
that produce more numbers and strings. Your example with angles is good
also, since you can add and subtract them. Timestamps, intervals and
geometric types are also good.

Most things in the world don't work that way: you can't take two
customers and make a third. I'm sure there's a mathematical way to
express this better but I hope this helps,

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

> Most things in the world don't work that way: you can't take two
> customers and make a third. I'm sure there's a mathematical way to
> express this better...

I'm pretty sure there are other newsgroups where one can go to find examples
of putting two people together to try and make a third one ;-)


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice