Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
What is the estimated number of rows for this table? –  Max Vernon Sep 28 '13 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 Sep 28 '13 at 17:37
1  
Do all 170 attributes have data for every data capture? What do you intend to do with this data? –  Max Vernon Sep 28 '13 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 Sep 28 '13 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!) –  Max Vernon Sep 28 '13 at 20:20
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
1  
Also see the related Does NULL occupy space in PostgreSQL on stackoverflow to evaluate storage space in data and index. –  Daniel Vérité Sep 29 '13 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 Sep 29 '13 at 15:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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