Hello,

could you please take a look at this table?

CREATE TABLE DETAILS_DATA
(
  ID_MASTER INTEGER NOT NULL,
  ID_PARAM INTEGER NOT NULL,
  PARAM_VALUE VARCHAR(64) NOT NULL
);

ALTER TABLE DETAILS_DATA ADD CONSTRAINT FK_DETAILS_DATA_ID_MASTER
  FOREIGN KEY (ID_MASTER) REFERENCES MASTER_DATA (ID);
CREATE UNIQUE INDEX UNQ_DETAILS_DATA ON DETAILS_DATA (ID_MASTER,ID_PARAM);

In this table I want to keep key-value data associated with some object (ID_MASTER) in master table. Currently I have 59 such parameters. Most of the time I am using only 2-5, so I thought that this structure will be ideal to save some space on the disk.

However, after running some tests I saw that database grows quickly. So for another test I create another table, this time I've included all keys in one row. Earlier I wrote that I have 59 parameters, so I needed to create 59 columns. Here is the table:

CREATE TABLE FLAT_DATA
(
  ID_MASTER INTEGER NOT NULL,
  P1 VARCHAR(64),
  P2 VARCHAR(64),
  P3 VARCHAR(64),
  -- P4..P57
  P58 VARCHAR(64),
  P59 VARCHAR(64)
);

ALTER TABLE FLAT_DATA ADD CONSTRAINT FK_FLAT_ID_MASTER
  FOREIGN KEY (ID_MASTER) REFERENCES MASTER_DATA (ID);

I've put to this table exactly the same data that was in DETAILS_DATA table. Obviously, most of the P1-P59 columns were NULL.

Here is the comparision of space taken by both tables:

DETAILS_DATA:                                                    FLAT_DATA:
Size of the table: 9592 MB                                     Size of the table: 2084 MB
FK_DETAILS_DATA_ID_MASTER: 953 MB              FK_FLAT_ID_MASTER: 52 MB
UNQ_DETAILS_DATA: 1161 MB
TOTAL SPACE: 11706  MB                                     TOTAL SPACE: 2136 MB

As you can see DETAILS_DATA takes 5 times more of space. I was completely surprised by this result, after all I am not wasting space for 50+ columns. Could you explain me this phenomenon?

 Regards.