KimSooil’s Weblog

Question: Good and Bad in “too-many-columns-in-a-single-database-table”

I found the answer: (In my case, my DB table has 520 columns in a single table which will capture the answers really many questions from a single person.)

“The pros and cons depend on how the schema is design and if it made sense to denormalize the table into the 170 column entity you posses. If denormalizing the table prove to be a benifitial thing for the majority of the query to this schema then it was a wise choice. If the net benifit to the denormalization is moot then a redesign it needed. if you sub-divide the large table into smaller table and replace the large table with a view does it impact performance? That question needs to be answered before you decide to break the large table up. There is a plus and minus to normalize database schema. When the cost is too great in term of query performance then some denormalization is needed. There is also a point where denormalizing something into infinitum defeats having a database and you are left with a giant spreadsheet. The best solution is a health medium of the 2, where you have both normalization and denormalization in a schema.”

(From: http://stackoverflow.com/questions/892960/too-many-columns-in-a-single-database-table)

Seems… the cost & time to break a single too-many-columns table into smaller VS. the loss of flexibility when adding new columns.

Actually… nobody cares except me… but I will break it into smaller ones anyway since that just looks smarter.

Uncategorized

November 19, 2010

Leave a comment

Grails and DBs (HSQL, MySQL, Oracle, Postgres, H2)

I’ve been using Grails/Groovy for all my projects (mostly bio-medical informatics) since early 2010.

…

For now, I am supposed to implement a web survey application which should be just a bit long web form. I ended up creating a Grails/Groovy Domain Class which has more than 500 variables. (Maybe … close to 1000s. Yeah… it’s stupid to design that many variables in a single domain. Not a good excuse though – Original Questions/Answers sheet is just not good for design. I had to put all unorganized questions one by one very quickly. I can say… it’s very similar to online registration form with so much different kinds of questions I am not familiar with).

The default datasource for Grails, HSQL (in-memory and file-based) works without an issue. I deployed after changing to production setting with MySQL. Soon I got the startup failure. MySQL have an issue with numbers of columns (which is same as number of variables). So, if MySQL is the final, I have to divide a big domain into smaller ones. I am not sure the maximum number of columns in MySQL. Here is the clue: (http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html).

Next, I decided to try Oracle to solve it. but Oracle has different issue with my application – a single column name’s length should not exceed 30 characters.(http://www.dba-oracle.com/sf_ora_00972_identifier_is_too_long.htm)

I turned to Postgres which I have never used in my former projects. It took couple of hours to understand how it works – I just needed to figure out how to configure remote hosts to access the db on Postgres. I works finally.

If I had another database issue, I would try H2. H2 is built by the developer of HSQL too. It seems better – small footprint, fast, pure-java, and feature-rich, etc. Here is the comparisons (copied from http://www.h2database.com).

	H2	Derby	HSQLDB	MySQL	PostgreSQL
Pure Java	Yes	Yes	Yes	No	No
Memory Mode	Yes	Yes	Yes	No	No
Encrypted Database	Yes	Yes	Yes	No	No
ODBC Driver	Yes	No	No	Yes	Yes
Fulltext Search	Yes	No	No	Yes	Yes
Multi Version Concurrency	Yes	No	Yes	Yes	Yes
Footprint (jar/dll size)	~1 MB	~2 MB	~1 MB	~4 MB	~6 MB