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