It is very easy to misunderstand the CAP properties, hence I’m providing some illustrations to make it easier. Consistency: A query Q will produce the same answer A regardless the node that handles the request. In order to guarantee full consistency we need to ensure that all nodes agree on the same value at all … Read more
Solution: make index a string (because strings, in essence, have infinite “arbitrary precision”). Or if you use an int, increment index by 100 instead of 1. The performance problem is this: there is no “in between” values between two sorted items. item index —————– gizmo 1 <<—— Oh no! no room between 1 and 2. … Read more
I will summarize my thoughts from my blog post – A lesson in address storage (on archive.org). On my current project [I work for a logistics company] we’re storing international addresses. I’ve done research on addresses all over the world in the design of this portion of the database. There’s a lot of different formats. … Read more
While it is true that there is no DROP ALL TABLES command you can use the following set of commands. Note: These commands have the potential to corrupt your database, so make sure you have a backup PRAGMA writable_schema = 1; delete from sqlite_master where type in (‘table’, ‘index’, ‘trigger’); PRAGMA writable_schema = 0; you … Read more
There’s a book in Martin Fowler’s Signature Series called Refactoring Databases. That provides a list of techniques for refactoring databases. I can’t say I’ve heard a list of database patterns so much. I would also highly recommend David C. Hay’s Data Model Patterns and the follow up A Metadata Map which builds on the first … Read more
From this blog post MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger? On EfNet’s #mysql someone asked: How do I make a trigger abort the operation if my business rule fails? In MySQL 5.0 and 5.1 you need to resort to some trickery to make a trigger fail and … Read more
For more international use, one schema to consider is the one used by Drupal Address Field. It’s based on the xNAL standard, and seems to cover most international cases. A bit of digging into that module will reveal some nice pearls for interpreting and validating addresses internationally. It also has a nice set of administrative … Read more
Summary of what I’ve seen so far: Some people don’t like cascading at all. Cascade Delete Cascade Delete may make sense when the semantics of the relationship can involve an exclusive “is part of” description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. … Read more
Not all data is relational. For those situations, NoSQL can be helpful. With that said, NoSQL stands for “Not Only SQL”. It’s not intended to knock SQL or supplant it. SQL has several very big advantages: Strong mathematical basis. Declarative syntax. A well-known language in Structured Query Language (SQL). Those haven’t gone away. It’s a … Read more
A relation has an associated set of columns of any type and an associated set of rows. There is one value per column per row. Sometimes “normalize” is used to mean (1) “decompose each relation to smaller relations that are its projections and that join back to it”. This is normalization to higher NFs (normal … Read more