Re: Beginner's questions about creating a custom data type in PostgreSQL...

Merlin,

Thank you very much for taking the time to write that detailed
response to my question. I appreciate it very much.

You have helped me understand my design issue better, but I have
another question if you have the patience to answer! :]

I am actually working on a spatial extension for PostgreSQL that is
similar to PostGIS, but compatible with a geometry library I am
developing.

I had first thought of using a "table" only desgin, with no custom
data types, to store the geometry information in the database.

For example, the "many_lines" table might store records representing
multi-segmented lines. (One geometry made up of smaller connected line
segments.) A separate table called "single_lines" would contain the
actual lines. The link between the line segment geometries and the
"many lines" geometries would be maintained by a one-to-many
relationship.

However, I began to realize that as my geometries became more complex
I would begin to have more and more interdependent relationships. this
in itself isn't a problem, until you consider that in a typical GIS
system I might be dealing with thousands or tens of thousands of
geometries. In the example above, if I wanted to find all of the line
segment geometries that belonged to a"many line" geometry I would have
to run a query on the "single_lines" table.

I am concerned about the performance of a system that would frequently
rely on a cascade of these types of searches.

If I instead implement a custom "many lines" geometry data type I can
now directly access the line segment geometries.

Do you think this reasoning is sound? Are custom data types the right
solution? Or am I being overly concerned about the performance issues?

If custom data types aren't a good idea in this situation, when are
they? I can think of almost no situation when I can't mirror the
functionality of a custom data type with a series of related database
tables.

Thanks Again,

Scott Huey

On 7/24/06, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On 7/24/06, Redefined Horizons <redefined(dot)horizons(at)gmail(dot)com> wrote:
> > I've got a few questions about creating custom data types for PostgreSQL.
>
> ok, i have a weakness for medieval stuff, so:
>
> > I'm not sure how to phrase the questions without an example, so here goes:
> >
> > I'd like to create a custom data type representing a Medieval Knight.
> > (This isn't what I really want to do, it's just an example.)
>
> > One of the "properties" of this Medieval Knight data type that you can
> > access through custom functions is "Combat Skill", which represents a
> > Knights fighting ability.
> >
> > The Knight's fighting ability depends on two things. His horse, and
> > his sword. The custom functions that work with the Medieval Knight
> > data type know how to manipulate the properties of Horses and Swords
> > as well.
> >
> > My Medieval Knight datatype "contains" a horse and sword. In the C
> > programming language implementation of my datatype I would like to
> > represent Swords and Horses with seperate structs, rather than
> > throwing everything together into a single Medieval Knight struct.
> >
> > Is it possible to define the Sword and Horse structs in the same DLL
> > that I define my Knight struct in, but not have Swords and Horses
> > available as custom data types themselves?
> >
> > I want to have 3 separate structs for programming simplicity, but I
> > want to embody them in a single custom data type and set of custom
> > functions for PostgreSQL.
>
>
> This is not necessarily a custom data type question, this is a data
> modeling question. Your phrasing suggests that you have a programming
> background...in the database world it's a bit different. The closest
> thing to the custom type as you described it is the composite type.
> Composite types are groupings of other types. When you create a
> table, by the way, a composite type is automatically created for you.
> you can also create your own using
> http://www.postgresql.org/docs/8.1/static/sql-createtype.html (look at
> the first form of create type).
>
> so,
> create table knight(combat_skill numeric, weapon text, horse text);
>
> gives you a 'knight' type, now you can:
>
> select (1.0, 'sword', 'trigger')::knight;
>
> which composes the knight type out of the fields using a row
> constructor. This isn't really necessary though, in most cases it's
> safe just to use tables without consideration of its type at all, a
> table is just a set of a type defined in its create table statement.
> Let's say you wanted to develop the horse further...
>
> create table horse(name text primary key, age int, speed numeric);
>
> let's link the knight to his horse:
>
> alter table knight add foreign key(horse) references horse;
>
> this is not the only way to do this, but this is more of a relational
> approach than what you suggested in your email. the key to
> understanding sql and data modeling is reducing data to it's
> functional dependencies so changes/additions to your data are as local
> as possible. This is a little bit diffferent than you might think of
> things from a C perspective.
>
> merlin
>