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 <[EMAIL PROTECTED]> wrote:
On 7/24/06, Redefined Horizons <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
