0

Please excuse my question if it is a bad one - I am not a DBA...

I would like to model the following data:

Let's say that I have one column that is the row id as generated by a sequence. Let's also say that I have 3 columns: A, B & C that have low data variability. Let's also say that I have 4 more columns I, II, III, IV with high data variability.

As a concrete example, lets say that the values of A, B & C can be any number between 1 - 50, and the values of I, II, III & IV can take any textual data, as another example, any number between 1 - 1B.

The question:
Should I model a single table with columns: row_id, A, B, C, I, II, III, IV. Or should I model two tables the first to contain the low variability data columns: row_id, A, B, C and the second to contain the rest of the columns while referencing the row_id of the first table: row_id, first_table_row_id, I, II, III, IV.

I am using postgresql. What answer best fits that database? What answer is the generic best?

Also, there might not be a clear cut answer, if so, what are the pros and cons to each modeling scheme?

|improve this question
1

I would suggest creating a separate dimension for your high variability columns. This is a Type 4 mini dimension according to Kimball methodology.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-4-mini-dimension/

Hope this helps.

|improve this answer
  • 1
    Please add here the main points of the document you linked. This could make your answer a good/excellent one. – dezso Nov 5 '14 at 9:36
  • @BenOastler, I read your link (several times). To a layman, well, I'm sorry, its unintelligable, I just can't understand it without having a non-layman understanding of databases. Thanks for the effort though. Could you please elaborate as dezso points out? – Yaneeve Nov 5 '14 at 10:25
  • Sure thing! I'll try not to get too technical. Using your example above, you would have a table with (row_id, dim_numbers_id, dim_text_id). And then 2 tables - one would be (dim_numbers_id, A, B, C) which holds the low variability data. The other table being (dim_text_id, I, II, III, IV) which would hold the high variability data. This splits both the low variable and the high variable fields into separate tables. There are lots of advantages to this design, but that may be too technical at this point. Let me know if you have more questions. – Ben Oastler Nov 5 '14 at 11:34
  • Thanks Ben, so if I got it straight, I would split the data into two parts (according to variability) I would then add a third table that in essence maps a single 'holistic' (in our case 7 dimensional) entry to two entries one per each table. I suppose that for the low variability table at least I would add a unique constraint? Also, how does this affect lookup times if I were to query high variable columns? – Yaneeve Nov 5 '14 at 11:56
  • 1
    Yes that's right. And you've just described the Type 4 design in the link I sent you :) The unique constraint in the low variability table is the dim_numbers_id field (note that you would want to give it a better name than this.). This will give you fast lookup times because you have separated out the high variability data. And querying the high variability table should be faster because your database doesn't need to handle the low variable data.It will also same on disk space. – Ben Oastler Nov 5 '14 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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