nosql SQL anti patterns and NoSQL alternatives

Table as queue
Problem: A table which holds messages to be completed. Worse, they must be ordered. NoSQL alternative: - RestMQ, Resque - Any other message broker - Redis (LISTS - LPUSH + RPOP) - Use the right tool
Stoned procedures
Problem: Stored procedures hold most of your applications logic. Also, some triggers are used to - well - trigger important data events. SP and triggers has the magic property of vanishing of our memories and being impossible to keep versioned. NoSQL alternative: - Now be careful so you dont use map/reduce as stoned procedures. - Use your preferred language for business stuff, and let event handling to pub/sub or message queues.
NoSQL
SQL anti patterns and NoSQL alternatives
Gleicon Moraes
/gleicon
Doing it wrong, Junior !
SQL Anti patterns
Dynamic table creation
Problem: To avoid huge tables, one must come with a "dynamic schema". For example, lets think about a document management company, which is adding new facilities over the country. For each storage facility, a new table is created: item_id - row - column - stuff 1 - 10 - 20 - cat food 2 - 12 - 32 - trout Now you have to come up with "dynamic queries", which will probably query a "central storage" table and issue a huge join to check if you have enough cat food over the country. NoSQL alternative: - Document storage, modeling a facility as a document - Key/Value, modeling each facility as a SET
Extreme JOINs
Problem: Business stuff modeled as tables. Table inheritance (Product -> SubProduct_A). To find the complete data for a user plan, one must issue gigantic queries with lots of JOINs. NoSQL alternative: - Document storage, as MongoDB - Denormalization - Serialized objects
NoSQL alternative: - Denormalization - Another scheme ? - Document store for flattening model - Key/Value
Your ORM ...
Problem: Your ORM issue full queries for dataset iterations, your ORM maps and creates tables which mimics your classes, even the inheritance, and the performance is bad because the queries are huge, etc, etc NoSQL alternative: Apart from denormalization and good old common sense, ORMs are trying to bridge two things with distinct impedance. There is nothing to relational models which maps cleanly to classes and objects. Not even the basic unit which is the domain(set) of each column. Black Magic ?
Your scheme fits in an A3 sheet
Problem: Huge data schemes are difficult to manage. Extreme specialization creates tables which converges to key/value model. The normal form get priority over common sense. Product_A id - desc Product_B id - desc
Table as log file
Problem: A table in which data gets written as a log file. From time to time it needs to be purged. Truncating this table once a day usually is the first task assigned to new DBAs. NoSQL alternative: - MongoDB capped collection - Redis, and a RRD pattern - RIAK
Row Alignment
Problem: Extra rows are created but not used, just in case. Usually they are named as a1, a2, a3, a4 and called padding. There's good will behind that, specially when version 1 of the software needed an extra column in a 150M lines database and it took 2 days to run an ALTER TABLE. NoSQL alternative: - Document based databases as MongoDB and CouchDB, where new atributes are local to the document. Also, having no schema helps - Column based databases may be not the best choice if column creation need restart/migrations
Table as cache
Problem: Complex queries demand that a result be stored in a separated table, so it can be queried quickly. Worst than views
NoSQL alternative: - Really ? - Memcached - Redis + AOF + EXPIRE - Denormalization
(Now there's a turning point. Data didn't need to change often. Denormalization was a given with cache) 5. The cache needs to be warmed or the app wont work. 6. Key/Value storage was a natural choice. No data on MySQL anymore.
The eternal tree
Problem: Most threaded discussion example uses something like a table which contains all threads and answers, relating to each other by an id. Usually the developer will come up with his own binary-tree version to manage this mess. id - parent_id -author - text 1 - 0 - gleicon - hello world 2 - 1 - elvis - shout ! NoSQL alternative: Document storage: { thread_id:1, title: 'the meeting', author: 'gleicon', replies:[ { 'author': elvis, text:'shout', replies:[{...}] } ] }