Thread: explosion of tiny tables representing multiple fields--Is this necessary?

Dear PostGreSQL experts,


I am working with text objects. A text object will have lots of fields that
are potentially multiple. There may be more than one author, more than one
modern editor, more than one edition number, etc.

These potentially multiple fields are, in my schema, nothing more than
strings.

For my purposes, multiple strings of this kind would consist of
composed/aggregated objects stored as a member in an instance of a
MyTextObject. The member would be some kind of collection. Like this:



public Class MyTExtObject

Vector authors;  //simple String for author name
Vector editionNumbers;  //simple string for editionNumber
...


}//MyTextObject



My question: is there any legitimate way around creating lots of tiny tables,
one for each multiple field, when the multiple fields are nothing more than
strings?

We will be doing lots of involved queries, and although I have a fair amount
of experience with SQL, this profusion of tables would be a pain to implement.
But I don't see any way around creating them when the relation between the
MyTextObject and the various fields is one-to-many.




--
Benjamin Weaver
Faculty Research Associate, Imaging Papyri Projects, Herculaneum Society,
Oxford
email:  benjamin.weaver@classics.ox.ac.uk
phone:  (0)1865 288260

On Thu, Nov 02, 2006 at 04:36:49PM +0000, Benjamin Weaver wrote:
> Dear PostGreSQL experts,
>
>
> I am working with text objects. A text object will have lots of fields that
> are potentially multiple. There may be more than one author, more than one
> modern editor, more than one edition number, etc.

Have you considered using arrays?

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.

Dear Martijn,

Wow, didn't know about arrays.  Did lots of sql, but, as I think about it,
that was 7 years ago, and we didn't know about arrays then

Are their performance problems with arrays?  We will not likely be working
with more than 50,000 - 100,000 records.

Ben


In message <20061103172212.GC551@svana.org> Benjamin Weaver
<benjamin.weaver@classics.oxford.ac.uk>, pgsql-general@postgresql.org writes:
> On Thu, Nov 02, 2006 at 04:36:49PM +0000, Benjamin Weaver wrote:
> > Dear PostGreSQL experts,
> >
> >
> > I am working with text objects. A text object will have lots of fields
that
> > are potentially multiple. There may be more than one author, more than one
> > modern editor, more than one edition number, etc.
>
> Have you considered using arrays?
>
> 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.

--
Benjamin Weaver
Faculty Research Associate, Imaging Papyri Projects, Herculaneum Society,
Oxford
email:  benjamin.weaver@classics.ox.ac.uk
phone:  (0)1865 288260

On Fri, Nov 03, 2006 at 08:25:25PM +0000, Benjamin Weaver wrote:
> Dear Martijn,
>
> Wow, didn't know about arrays.  Did lots of sql, but, as I think about it,
> that was 7 years ago, and we didn't know about arrays then
>
> Are their performance problems with arrays?  We will not likely be working
> with more than 50,000 - 100,000 records.

If by records you mean rows in the database, then 50,000 rows is a baby
database, nothing to worry about there.

Performence of arrays scale about linear with the number of elements in
the array. So if most of your arrays have only 2 or 3 elements, the
performence should be good. If you make a single array with 50,000
element, it's going to suck very badly.

Note, recent versions of postgres have better support for arrays,
including for indexing thereof. Especially the new GIN index type may
be useful for you.

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.