Hello,

I'd like to hear what you think about the following -

We have an application (database plus software) that is used as a central
application. Sub-applications can connect to it and perform certain actions
like reading and writing data. The central application has some objects
which the sub-applications can extend. For example the central application
has an object 'person' with elements 'first name' and 'last name' and the
sub-application can extend this object by adding an element 'gender'. This
element is seen only by this sub-application.

This extension is done through object descriptor. So one can say that there
are two object descriptors - central and extended where the extended is per
sub-application. In the above example the central descriptor contains 'first
name' and 'last name' and the descriptor of this one sub-application
contains 'gender'.

In order to avoid changing the database with each change a sub-application
introduces to the extended descriptor of an object, the values of the
extended elements are stored in one table, let's say with four columns -
sub-application ID, object name, element name and element value. This way
adding new extended descriptor does not require change of the database.

Now the question is where to store the values of the central elements -

a) in the same table

b) in another table where each element has a column

In case a) the benefits are less columns and query is made to one table
only. The disadvantages are that this table will grow in rows faster and
that more rows have to be read in order for the object to be assembled.

In case b) the benefits are that the central table will grow in rows slower
and that less rows will have to be read in order for the object to be
assembled as the central elements will be columns of one row. The
disadvantages are that each time query will be made to two tables.

Which method would be considered better if the database is expected to host
up to 100 sub-applications and each sub-application will store on average
approx. to 10 000 records with up to 30 elements each. Or the extended table
is expected to have approx. up to 30 000 000 rows. Almost elements are
'small' - such as 'name', 'gender', 'age'. Only few are longer such as
'note'. No large objects will be stored for now (such as images, etc.) in
the database.

Thank you all,
Iavor



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])