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.
