Search code examples
design-patternsrefactoringrelational-databasekey-valuehorizontal-scaling

What pattern should I use for refactoring my relational SQL database to a key-value store?

I'm looking at migrating my web-application from an SQL database to a key-value store, so I can scale my app horizontally.

I'm planning out how to migrate my schema - and I'm sure there must be a pattern for doing this.

I'll give a Mickey Mouse logical example to show my thinking. Here is a simple library system in a relational database with Books, Persons and BorrowEvents. You can join from the Books to the Persons using the BorrowEvent to see who has borrowed which Book. 

Book
 - BookID
 - BookTitle

Person
 - PersonID
 - PersonName

BorrowEvent
 - BorrowEventID
 - BookID
 - PersonID

Now let's try the following refactor:

[Key][Value]
 - ID/BookName
 - ID/PersonName
 - ID/[String]"BorrowEvent-"+[ID of BookName]+"-"+[ID of PersonName]

This works by querying on the BorrowEvent, splitting the String of the value using a regex to get the IDs.

Now this has several limitations. If I want to have add an Author to my Book, I've got to add an additional hop to retrieve my book information. Same goes if I want the Person to have a first and last name.

I imagine there must be a pattern for this transformation from relational to key-value store. (Or multiple patterns). I just don't know what the name is.

My question is: What pattern should I use for refactoring my relational SQL database to a key-value store?

Assumptions:

  • Assume this is a logical refactor, not a physical refactor.
Solution

  • This post lists a number of techniques, including:

    1. Dimensionality Reduction
    2. Index Table
    3. Composite Key Index
    4. Aggregation with Composite Keys
    5. Inverted Search - Direct Aggregation
    6. Tree Aggregation
    7. Adjacency Lists
    8. Materialised Paths
    9. Nested Sets

    10. Nested Documents Flattening

      i. Numbered Field Names

      ii. Proximity Queries

    11. Batch Graph Processing