3 ms·
Ask HN: Best practices for rapidly changing database schema?
Recent discussion has focused on using JSON blobs as the rows of "schema-less" databases (http://news.ycombinator.com/item?id=496946). Commenters note that such approaches are appropriate when the database is really just a glorified hash table, and advanced database operations like joins are not necessary.
I am developing such an application. In it, each record is a JSON object. The problem is is that as I prototype, my schema is changing several times on a daily basis. To compound the issue, the structure of the JSON object does not always correspond with the semantics I need expressed. I am considering using the adaptor pattern to abstract the JSON object using a class. I am also considering some sort of versioning system, to keep track of schema changes, but I don't know what.
Can people propose (or link to) best practices for agile development and prototyping when there is a rapidly changing database schema?
- lrajlich 18y agoI've heard of a number of ways of solving this. I don't like json in blobs since you can't query or index the blob data effectively. I propose the attribute table pattern, which I've used to decent amount of success. It's not as sexy as ff's, but it is simple, easily cached, and queried. Here's an example schema of attributes you could set against a userId. CREATE TABLE `Attributes` ( `userId` int(11) NOT NULL, `attributeId` int(11) NOT NULL, `charValue` varchar(512) default NULL, PRIMARY KEY (`userId`,`attributeId`), ) ENGINE=InnoDB; CREATE TABLE `AttributeMap` ( `attributeId` int(11) NOT NULL auto_increment, `attributeName` varchar(64) NOT NULL, PRIMARY KEY (`attributeId`), KEY `attributeName` (`attributeName`) ) ENGINE=InnoDB;