Postgresql – Entity with >150 attributes: One table or many tables with relationships

database-designperformancepostgresql

I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal – these are conditions for GROUP BY operations. I could create one table for all entity:

CREATE TABLE rows {
   terminal_id long reference terminals(id),
   time timestamp,
   -- description 170 attributes
   PRIMARY KEY(terminal_id, time)
}

or I could create many tables with relationships:

CREATE TABLE rows {
   row_id long PRIMARY KEY,
   terminal_id long  reference terminals(id),
   time timestamp -- need create index for group by
}

CREATE TABLE gps {
   row_id long references rows(row_id),
   -- description gps attributes
}

CREATE TABLE fuel {
   row_id long references rows(row_id),
   -- description fuel attributes
}
-- etc.

Please advise optimal structure for the database of this type.

Best Answer

First off, apply normalization to the table and make sure that it really is one table with 170 attributes and not several tables jammed together.

Then decide whether your table will be sparsely populated and if so, decide whether storage space is a concern that warrants creating sub-tables to hold subsets of nullable columns.

If you do decide to create sub-tables, remember to factor in the extra index space when calculating whether a sub-table saves storage or not.

Related Question