Hi,

we have a system, where manage different kind of objects. Instead of one
table per object type, I created a generic table with some generic
columns like this:

create table DATA (
DATAID INTEGER not null,
DATATYPE SMALLINT,
STARTX DOUBLE PRECISION,
STARTX DOUBLE PRECISION,
LAENGE DOUBLE PRECISION,
VALUE1 DOUBLE PRECISION,
VALUE2 DOUBLE PRECISION,
...
VALUE10 DOUBLE PRECISION,
TEXT1 VARCHAR(50),
TEXT2 VARCHAR(50),
...
TEXT5 VARCHAR(50)
DATE1 TIMESTAMP,
DATE2 TIMESTAMP
)

I can easily add a new type of object in my application and just deliver
a new exe file and have no need to make changes in the database.

The disadvantage I have is, that if I add an object type, that needs 6
instead of 5 string fields, I have to change the database structure again.
So I'm looking for a more flexible solution in our next major release. I
see 2 possible solutions:
1.) Normalize the values to detail tables, one detail table per type
(string, float, date).
2.) Use a table with just one column VALUES of type BLOB and store all
the values in this field in a text or binary format.

Is there another solution, to solve this problem?
Do you see problems with one of my solutions?
Which solution will probably be better from the perspective of speed /
performance? The table won't be that big, about 50'000 - 200'000
entries, depending on the customers need.

cu Christian