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:

   terminal_id long reference terminals(id),
   time timestamp,
   -- description 170 attributes
   PRIMARY KEY(terminal_id, time)

or I could create many tables with relationships:

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

   row_id long references rows(row_id),
   -- description gps attributes

   row_id long references rows(row_id),
   -- description fuel attributes
-- etc.

Please advise optimal structure for the database of this type.

  • 1
    What is the estimated number of rows for this table?
    – Hannah Vernon
    Commented Sep 28, 2013 at 15:30
  • about 100 terminals x (30 rows/sec x 60sec x 60min x 8h) = 86 400 000 per day = 2 592 000 000 per month will be added. Probably, once a month will be archived.
    – karavanjo
    Commented Sep 28, 2013 at 17:37
  • 1
    Do all 170 attributes have data for every data capture? What do you intend to do with this data?
    – Hannah Vernon
    Commented Sep 28, 2013 at 18:18
  • Set of attributes change dynamically. May be 100, may be 80 - it's dependency from concrete terminal. In addition for concrete terminal may change set of attributes. Operations for these data - calculation basic statistic parameters - average etc. and displaying change of parameters on charts.
    – karavanjo
    Commented Sep 28, 2013 at 20:13
  • Since you have so much data and a large portion of each row would be NULL values I'd lean towards having a table for each group of attributes. If possible, you'd want attributes that will be reported together stored together in one or two tables. If you will be reporting on ALL the attributes in a single report, you will need some really beefy hardware to get quick reporting on a months worth of data (2+ billion records is a lot!)
    – Hannah Vernon
    Commented Sep 28, 2013 at 20:20

1 Answer 1


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.

  • 1
    Also see the related Does NULL occupy space in PostgreSQL on stackoverflow to evaluate storage space in data and index. Commented Sep 29, 2013 at 15:14
  • Quite so - even NULL columns occupy space in any RDBMS that I've worked with - if even only 1 bit.
    – Joel Brown
    Commented Sep 29, 2013 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.