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.

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  
It will depend entirely on how you plan to use the data. Off-row storage would work as long as you will never filter or manipulate that off-row data. As soon as you filter on it, you are doing a table scan which requires every row to be retrieved. –  JNK May 8 '12 at 19:23
1  
    
Presumably Genome data is all sequences of GATTACA, which should be amenable to transformation into a common format, or am I missing something? –  ConcernedOfTunbridgeWells May 8 '12 at 20:47
    
@ConcernedOfTunbridgeWells : nop, basically information like this: dgvbeta.tcag.ca/dgv/app/chromosome?ref=NCBI36/hg18 –  bdoat May 8 '12 at 21:00
    
So it's higher level than gene sequences? –  ConcernedOfTunbridgeWells May 8 '12 at 21:12
show 6 more comments

2 Answers

Structure? !

If the data is just a zillion-character string with 4 possible letters and store each in a file.

If you have meta data associated with the string, how big will that be? How should that be structured in a relational database table?

There are only 4 letters, correct? That means 2 bits suffice for each letter. Design an encoding so you can store 4 letters per byte. Then experiment with zip and other compression tools.

Whether to put the string (of letters or 2-bit codes) into a LONGTEXT/LONGBLOB, or put it into a file -- that question comes up often with respect to "should I store my images in a table or a file". The answer is pretty much the same:

  • table -> referential integrity (less likely to lose string vs meta data)
  • file -> might be easier for other tools to access
share|improve this answer
add comment

I have not experience such scenario but based on my present knowledge I would suggest you have a table which contains union of all the column which any client can need e.g. say client A wants column 1 and 2 and client 2 wants column 2 and 3 and you have only these two clients then create a table with column 1,2 and 3 and in case of client 1 keep column 3 as null and in case of client 2 keep column 2 as null. Once you are done with this create view for each client e.g.create View A, it should have column 1,2 and will be used in case of client A similarly create view for each client.

Also I would suggest to keep data in table only and not separatly on disc as it create sync issue.

share|improve this answer
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.