I am working on large tables (> 100 fields each) currently organized in flat-files in csv-format. An Oracle DB is to be created to store all the data and allow for several more or less intricate analyses.
Some of the fields are Strings, a large portion of fields are of numeric data types though. The system in the end should allow querying large individual tables (I do not expect frequent joins) and carrying out basic arithmetic operations on respective columns.
However, in terms of missing values, I have to be able to distinguish between multiple different types of NULL (denoting different reasons for why data is missing). How would I best encode this with the least possible effect on overall DB performance?
Options I thought of:
- Every field as String and carry out all computation in the application layer (testing for NA1, NA2,... + casting to type).
- Extending Types by e.g. NA1, NA2, NA3,...
- For each field create an additional field with a default value of 0 (no missing data), otherwise describing why data is missing while using NULL in the original field.
Personally, I feel like Option 3 should be the way to go. However, this would increase the table size even more (several hundred fields) - which, then again, could be solved by splitting the table. How would you go at it?