Dynamic table - Mailing list pgsql-general

Hi.
I have a little problem (and a suggestion for a solution) that I
wondered if anyone would care to comment on.

I have a standard table filled with customers (with a unique customer
id, names etc.) and for each customer I need to store some integer
values. The problem is that the number of integer values that should
be stored WILL change over time (it can both increase and decrease).
It will most likely grow from zero to 10-18 and then perhaps add 1 or
remove one value per year but basically be about 10-18 or so.

I must be able to add a new integer value to all customers, and remove
an integer value from all customers  Altering the table by adding and
deleting columns would theoretically solve it, but since columns are
not  really dropped, just hidden away, and there is a 1600 column
limit on tables as I understand it, this would crash the application
at some time in the future, or at least it will waste a lot of
discspace.

Other things I must be able to do is to alter values for a specific
integer value for one customer or all customers simultaneous. This can
be like "change all value 4 to 8 for the third integer value".
And I must be able to quickly select all the integers for a specific
customer for further processing. There will also be access to single
integer values for a specific customer. It's hard to say how much
compared to "get all integer values" for a specific customer. All
customers will be equally accessed.

As I see it I have these options.

Method A) a "add-only" dynamic table that re-uses columns that are not
used any more.

First create a metatable
CREATE TABLE Columnspec (id integer, columnname varchar(10), name
varchar(30), defaultvalue integer, active boolean default true );
That will keep track of the dynamically added columns.

and a table for all the integer values
CREATE TABLE customerValues (customer_id integer references customer);
Initially it will be empty

and adding a new integer value called "shoe size" with a default value
of 4 would mean to run these two queries.
insert into Columnspec (id,columnname,name,defaultvalue) values (1,
'col1', 'shoe size', 4);
alter table customerValues add column col1 integer default 4;

removing a column would mean an operation like
update Columnspec set active=false where id=5;

then adding a new column would mean to reuse an inactive column, like
the following operations
update Columnspec set active=true, name='hair length', defaultvalue=10
where id=5;
update customerValues set col5=10; -- to give all customers the default value.

One could perhaps alter the default value of the column called 'col5'
in the example above and also alter the names of the columns but that
are just cosmetic.
The only drawback I can see with this rightnow is that if the number
of columns decreases, it will waste space.

Method B)
The EAV way.
With a meta table
CREATE TABLE metadata (id integer primary key, name varchar(30),
defaultvalue integer);

and then the values
CREATE TABLE eav (customer_id references customer, key integer
references metadata on delete cascade , value integer,
unique(customer_id, key)  );

With c customers, k integer fields method A requires  at least c*(k+1)
integers to store,  B requires c*k*3. A factor about 3 sounds not that
much wasted space, but of course it can make a difference.

Method C)
Have a metadata table as usual, and then store the values in an array.
I must admit that I have not looked enough at arrays yet.
The drawback I can see right now will be the access to a specific
value in the array, but I guess PostgreSQL is fast...

Method D)
As method B) but add a view that aggregates the values into a complete
set for each customers if that will speed up anything....

Any comments on the pros/cons with these strategies?