Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We want to store some genomic variant data but there are some problems, more important ones like problem of the data's immense size and variability.

  1. Variant data can be huge. For example, a single individuals variant data could feasibly some day require a million rows of data in a table, or require of a gigabyte of raw storage on disk. Multiply this over several thousand individuals, and you could potentially end up with terabytes worth of information that you need to make sense of.

  2. Each client and/or system we integrate with, will expose or want to see data slightly differently depending on their needs and use cases. This can potentially lead to hundreds of fields that we might need to store, all of which might need to be in different configurations based on the clients needs. So this variant data model will need to keep this in mind in order to remain easy to use, expandable and most importantly, scalable in the long term.

What do you think is better for such a problem? We were thining of having some coulmns in each table that point to an external database or even a file, where we save the huge BLOB data?

share|improve this question
1  
Could you use views or even just queries/procs to resolve your second issue? –  Michael K May 8 '12 at 18:51
3  
You might want to ask to have this question migrated to dba.stackexchange.com/questions –  FrustratedWithFormsDesigner May 8 '12 at 19:09
3  
@maple_shaft I think this is reasonable on either site. I have a feeling the best answer for this is going to be a NoSQL solution, and so for that reason alone I want it on Database Administrators –  jcolebrand May 8 '12 at 20:14
1  
@BDotA use the right tool for the right problem. In this case that's NoSQL for what this sounds like (lots of variant data and lots of random fields, based on the site. That's very easily done with some NoSQL solutions) –  jcolebrand May 8 '12 at 20:15
3  
@BDotA read this dba.stackexchange.com/questions/607/… –  jcolebrand May 8 '12 at 20:36

3 Answers 3

up vote 3 down vote accepted

I don't know enough about your system but you need to look at the following:

1-How you obtain the data and in what format? Answering this will give options of how to store it and load it initially if you will end-up using a database.

2-How do you process this raw data? Answering this, will help you figure the 'active' set size. This will help in deciding how to store and how to load the data also. You may find that you don't need the entire input record and all you need is few fields of it only. If most of the fields are not used, you can keep them in a separate archived storage.

3-How do you inquire this data (online/batch and what criteria is most likely to be used)? Answering this will be the key factor in answering how to store the data what parts to keep on-line and what parts to keep off-line. Oracle for example allows you to run SQL on text files without loading the files in first. This could be a huge time saver, but of course it depends on your scenario.

as per your point:

single individuals variant data could feasibly some day require a million rows of data in a table

I really don't understand how this is possible. If it is accurate, I am not sure how it will be used. Maybe you need to separate the concepts of mere data storage from the concept of which parts of the data will be used. If you understand more about how the data will be used, you may be able to cut down the number of rows by aggregation or a similar technique.

In short much analysis is required for before a solution can be found. The guiding principles are:

1-Know your data well

2-Cut down on row size by keeping the needed columns only and linking to off-line storage when possible

3-Cut down on total row numbers by aggregation when possible

4-Use table partitioning and avoid excessive indexing

5-Know how the users need to use this data

6-Consider loading data as it arrives

7-You are probably going to need a star schema (fact and dimensions) to speed queries, but we can't tell by just the information provided

share|improve this answer

For (1), look for common things, you may be surprised with its extent. Thats the only way to solve size problem. That is how all normalization and even compression works: find patterns (a.k.a. common stuff), store it at one place, replace values with references at every place its used.

Another solution for (1), use file system instead of database wherever you can to save blob data. In oracle you can even index to search them later.

For (2), use views (more maintainable) or stored-procedures (less maintainable).

Another solution for (2), may be its better to build separate small applications for each set of your users. I assume you are not expecting millions of users, just a few hundred or a few thousand. Making 10 separate projects is more maintainable than a large one. Alternatively you can divide on bases of modules, in .net a dll is a module, in database a schema is. Use this solution if you don't find much in common among your users, if you do then use normalization.

Don't forget fundamental technique of indexing your tables!

share|improve this answer

I have made use of external files and link to them. This will greatly reduce the strain on the DB. You will need to find a way to get the data back out of the file, but it can be done pretty simply with JavaScript or PHP.

share|improve this answer

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.