Re: Use arrays or not? - Mailing list pgsql-sql

Hi,

Thanks for your fast response. But I'm afraid I stated some things unclear.

> >>The data consists of XML-records, with a lot of XML-fields. I want to 
> >>store
> >>the XML as it is, so taking the information from the XML-records and then
> >>storing it in a different-from-XML-format is not an option.
> 
> Actually, your data consists of data. The XML is just scaffolding to 
> enable you to locate and understand your data. Since you are storing it 
> in a relational database, that will use relational scaffolding for its 
> organisation instead. At least partially, you will *have* to parse the 
> values out and organise them differently.
I do, and I know. But I want to store the XML-records as they are. So given an
author, title and an XML-record that are related to that author and title, how
to store that. That's the question. I have good reasons to store the
XML-records as they are, without further parsing them.

> >>Each XML-record describes data about one book. If an update of bookdata
> >>comes, the XML itself is not changed, but a new XML-record is stored with
> >>the updated data. Via a complex scheme of combining a base record and its
> >>updates, the final dataset is produced that is used in the application.
> >>
> >>Searching is done via a simple table lookup on three different fields:
> >>title, author and subject. The data for these fields is extracted from the
> >>database. Each book has a unique identifier (EAN13, derivative of ISBN).
> >>
> >>Here is one way to organize the database:
> >>table title:
> >>TITLE | EAN13, indexing on TITLE
> >>
> >>table author:
> >>AUTHOR | EAN13, indexing on AUTHOR
> >>
> >>table subject:
> >>SUBJECT | EAN13, indexing on SUBJECT.
> >
> >
> >This is a *very* strange way of setting up your database.  Are you new to 
> >Relational Databases and SQL?   If so, I'd recommend starting with a book 
> >on relational database design.
> I agree with Josh - think about a book.

Thank your for the recommendations. But the above thing is just background
information, it will not be stored as such. The important question for me is
the question whether to use arrays or not. With index in the above examples I
do not mean the actual postgres-index, I mean that those are the fields that
are used in searching. One never searches on an EAN13-number, only on author,
title and subject. And one never, by the way, searches for a specific
XML-record, only the total of the stored XML-records per book should be retrieved.

> 
> >If only one author, title and subject are allowed per book, you should 
> >have:
> >
> >table books
> >    EAN13 | TITLE  | AUTHOR | SUBJECT
> 
> If, on the other hand you can have multiple authors (likely) you'll want 
>  something like:
> 
> CREATE TABLE author (
>   ean13        varchar(13), -- Guessing ean13 format
>   author_num   int4,
>   author_name  text,
>   PRIMARY KEY (ean13, author_num)
> );
> 
> Then you can have rows like:
> 
> ('my-ean-number-here', 1, 'Aaron Aardvark')
> ('my-ean-number-here', 2, 'Betty Bee')
> etc.

Yes, I have such a thing. There can be multiple titles, multiple authors and
multiple keywords per book. 

> 
> 
> >>Finally:
> >>table record:
> >>EAN13 | ARRAY OF XML-records.
> >>
> >>It's the last table that I am most curious (and worried) about, the
> >>question being mainly what the optimal way of structuring that table is.
> >>Option 1 is the given option: adding/deleting an XML-record for the same
> >>book requires adding/deleting it to/from the array of XML-records.
> >>
> >>Option 2 would be something like this:
> >>EAN13 | XML-record
> >>where, if a book has several records describing it, there are multiple
> >>entries of the EAN13|XML-record - pair. Adding an XML-record for the same
> >>book, requires adding a new entry to the table as a whole.
> >
> >
> >In my mind, there is no question that this is the best way to do things.  
> >It is a normalized data structure, as opposed to the arrays, which are now.
> 
> Although your option 2 doesn't go quite far enough. You'll also want to 
> know what order these come in. So, assuming you can't have two updates 
> at the same time:
> 
> CREATE TABLE book_history (
>   ean13  varchar(13), -- Guessing ean13 format
>   ts     timestamp with time zone,
>   xml    text,
>   PRIMARY KEY (ean13, ts)
> );

The order is not important; the interpretation of the XML-records is done by
an external module. The order is determined upon the content of the
XML-records, because they can come from different sources and can be combined
in different ways, depending on the application processing the
XML-records. Order is not determined at the moment that the records are
stored, but at the moment the records are interpreted.

> As for your other concerns:
> >>Indexing:
> >>Database growth:
> >>Integrity:
> Just worry about the integrity - if you keep the design simple, 
> PostgreSQL will manage quite large growth on quite small hardware.

What would be a situation in which one should use arrays then?


> Now... I don't think you want to do what you're trying to do. Don't take 
> this personally, but unless you're extremely pushed for time and 
> resources this is almost certainly a bad design choice.

> 1. Wrong tool for the job
> Basically you're taking a relational database and treating it like a 
> filesystem. All you need for what you're doing is a directory-tree to 
> represent the ean13 structure and one file per xml-record. Index the 
> author/title fields with dbm/SQLite. You could write the whole thing in 
> a day - simple, efficient, leverages existing unix tools.

That is correct. I think nothing would beat a dbm-style solution qua
performance, and I'm still considering using that. The added value of a system
like postgresql is the client/server-interface and the omnipresent support of
programming languages, and not in the least the familiarity of most people
with mysql/postgresql in comparison to dbm.

> 2. Wrong job for the tool
> How do I find out which publisher produced the most books in 2003 (I'm 
> assuming this is in your XML somewhere)? Which book is available in the 
> most languages?
> How many updates were applied last month? How many different books did 
> they affect? Why do the numbers not match - which books had multiple 
> changes?
> The first set of questions need you to write code, the second set don't. 
> Why? Because the second set rely on information stored simply and 
> explicitly in the database (book_history as it happens).

We know what set of questions will be asked: we only need to access through
author, title and subject keywords. Another reason maybe to choose a
non-relational model.

> 3. The medium isn't the message
> You don't want to open up your XML records to store them in the 
> database, but I assume you have to in your PHP code, or you can't 
> process individual values. As it stands you're having to extract certain 
> information when an XML update arrives anyway. If the title of a book is 
> amended, then you'll need to remember to update the book_title table. If 
> it's simple to extract more, why not do so? If some of it is fiddly to 
> represent in an SQL database then at least extract everything that is 
> convenient.

There are good reasons for that. The XMLs not necessarily contain the input to
author/title/subject-tables (or columns). I just want to store the XML,
interpret it later. (really, it makes sense!)

> Oh - and I would probably store a "current snapshot" of the book's 
> record separately too. Saves your application having to recalculate it 
> every time it's needed.

I *want* to recalculate it every time it's needed. Because the different
XML-records can be combined in several ways, depending on the application that
is accessing the database-client.

So, let me rephrase my questions:
1. When and why would anyone use arrays?
2. When designing the database, is it really true that there is no performance
difference between a table of which the number of tuples grow by a factor of,
say 10, and a table of which the size of the tuples grow by a factor of, say
10?

Thanks, 

Roelant.