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.

First: I really haven't got any idea how to google for this. If you got one, leave a comment.

If I want to store arbitrarily large tables in a database, how should I set up my database tables?

Requirements:

  • A table has an arbitrary number of columns and rows (columns about 1-10k, rows about 0-10M)
  • There are thousands of tables
  • A table has a name
  • The columns of a table have a name
  • Column-Cell values can be either numbers or strings
  • A column always has the same datatype

Data I want to store:

"Bob the Table"    

| "Name"   | "Size" | "Comment" |
---------------------------------
|  "Homer" |  170   | "A guy"   |
|  "Lisa"  |  120   | "A girl"  |
|  "Bart"  |  130   | "A boy"   |

You get the idea. Table could be much larger.

Data I want to query:

Most of the time I want to query a single table, that is, given a table's name, just display "the table" as depicted above.

How would you implement the RDBMS tables and how would you write queries to select the data for a single table?

Disclaimer: This isn't homework. This here is purely out of curiosity while working on another problem.

share|improve this question
3  
You may want to post your business requirements and not what you think the database design requirements are. I'm aware of almost no business use cases that would require 1-10k columns. If this is based on the linked question, I'm not sure why you want 10,000 columns in a table. –  JNK May 28 '13 at 12:21
    
@JNK - The linked question asks about measurement values. We have real world use cases where one measured sample contains above 2k or 3k channels I think, that's why I chose 10k here, I could have said 4k, but would it really make any difference? –  Martin May 28 '13 at 12:40
1  
You really should normalize this in either case. –  JNK May 28 '13 at 12:44
    
@JNK: Not sayin' anything. A generalized answer probably would include a fully normalized model. That still leaves the query part of the question open. I though it might be an interesting problem to work on. Maybe I'll come up with an answer of my own and probably I'll have to ask for query advice along the way, but that's for then. –  Martin May 28 '13 at 12:48

2 Answers 2

Arbitrary data can be put into a RDBMS even if, as the word might suggest, some relation should be applied.

Some points are obvious: every table must have a unique name, any column must have a unique name. For datatype you can use everywhere a VARCHAR. The best way is to analyse the table requirements and use the appropriate datatype for each column, this is useful for space management and query.

For query: in order to speed up the query on your data you have to apply a partitioning (and, if the database technology can support it, subpartitioning). The efficient use of partition and subpartitioning can speed up all DML operations if you can apply to every SELECT statement a WHERE clause based on the partition key. If you want also to use parallel access try to split different partitions across different disks accessed by different disk controllers.

Moreover many RDBMS allow a limited number of columns per table.

Finally, this is a very theoretical answer, too many details are missing. Although you can consider to leave the RDBMS techonology and adopt a NoSQL database. Probably, with the info supplied, a columnar nosql database is the best choice for you: HyperTable or HBase for example. This document is a must read for NoSQL Databases

share|improve this answer

If you just want the data in the database so you can display it in entirety much like you would a picture stored in the database, then a clob for each set of tabular data would work fine.

If parts of the data will change, if you want to be able to write queries against parts of the data, if you want to join data from different tabular sets or with other data already in the database, and if you want these things to be fast and scalable, then you will need to do some design. Figure out what data needs to be in what tables and how it will relate. Normalize the data for smaller more manageable chunks. This means there could be multiple tables for each tabular set you have.

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.