How to store tabular data in an RDBMS

database-agnosticdatabase-design

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.

Best Answer

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

Related Solutions

How to manage a list for particular object. for eg. user comments on specific object(user post etc.)

As the comments indicate, you need to understand normalization. Without too much theory, when you want a list for a particular object in a database here are some possibilities:

Create a new column for each additional list item. If your list has a small concrete length this may be a viable option, however for most lists n items today becomes n+1 tomorrow and your table begins to have an unmanageable number of columns that require extra coding for joining and filtering.
Create a new table for each additional list. If the lists are independent then this is the way to go, however, if the lists are the same, then creating and maintaining a new list table for each and every object becomes unmanageable. Additionally, queries crossing objects require UNIONs on multiple tables. These queries could become lengthy and have to be modified with each additional object.
Create a normalized table for this type of list. If the list structure is the same for each object then a single table can be used to store any number of list items for any number of objects. The tables definition should be defined and scoped.

For your particular question you have defined the list item as a comment, so you will simply need additional columns to indicate what object (post?) it goes with and what comment number it is. You may also want to have columns indicating who made the comment, when it was made, and from where it was made.

Here is how this could look:

Post    CommentNumber  Comment                                     
------  -------------  --------------------------
098763  1              Comment 1 for post 098763.
...  
142525  1              Comment 1 for post 142525.
142525  2              Comment 2 for post 142525.
...
200526  1              Comment 1 for post 200526.

How many fields is too much for a primary key

First off, don't worry about performance until you're sure you have a performance problem, especially for a tiny number of rows.

Second, whether you use a unique index that is primary key or a unique index that isn't the primary key to enforce the uniqueness of the combination of your four columns the performance will be the same with respect to that index.

Whether to make that unique index the primary key should depend on what else is going on in your schema. If you have another table (or tables) that reference this table, then your compound primary key is going to get propagated to other tables as a foreign key. If that is going to happen, they you have more to think about.

If you don't need to worry about propagating a compound foreign key, then you should probably just make your compound unique index the primary key of your table. That would be the least effort which achieves your goal of enforcing your business rule.