I was playing around with the idea of redoing our customer database at work. Our customer database contains for each customer their email address, name, when their service expires, how many licenses they have, and things like that. Essentially a bunch of named scalar values.
It also contains their transaction history--essentially an event log. Some of the transactions have auxiliary information such as specific line items on an order.
A person subscribing to our service can use it on multiple computers (up to a number determined by exactly what they purchased). So there is a list of the computers they are using it on, with some information about that use.
The state of a customer could be represented nicely by a nested data structure, like a Perl hash or a JSON document, and so some kind of document oriented storage seems like it could work well. Most changes to a customer's data consist of adding new transactions, and occasionally updating the expiration date.
We have a customer service interface that our support people use to view and change a customer's data. Getting all the data by fetching a single JSON document would be much simpler than the multiple queries that are now done to grab the data from the MySQL database it is in, spread across many normalized tables.
In general, with one notable exception, almost everything that deals with a customer is dealing with one customer at a time. For instance, a support person is dealing with the customer they have on the phone. The order processor is dealing the customer's account for the order that it is currently processing.
It's not even clear we need a database at all for this. Why not just a file per account, with the file containing a serialized Perl hash or JSON document or something similar, with a simple classic Unix file locking mechanism used to prevent corruption? This is easy to scale, for both reads and writes.
That brings me to the notable exception to "dealing with one customer at a time". Reports. Reports like to do things involving selecting data from a large number of customers, related by various factors such as having purchased particular SKUs in a particular time frame, or having their credit cards charged through a particular gateway, or things like that.
Reports seems to be something that fits in a lot better with the traditional relational approach.
If the set of reports needed was known up front, I think one could design a system that uses a document approach for storing customer data, but also maintains a relational reports database to hold data specifically for reports. We tend to have a changing set of reports--we do a lot of poking around and playing with data to try to find ways to improve our system and I'm often looking at things I had no idea a week before I'd ever need to look at for a report.
Comments
I was playing around with the idea of redoing our customer database at work. Our customer database contains for each customer their email address, name, when their service expires, how many licenses they have, and things like that. Essentially a bunch of named scalar values.
It also contains their transaction history--essentially an event log. Some of the transactions have auxiliary information such as specific line items on an order.
A person subscribing to our service can use it on multiple computers (up to a number determined by exactly what they purchased). So there is a list of the computers they are using it on, with some information about that use.
The state of a customer could be represented nicely by a nested data structure, like a Perl hash or a JSON document, and so some kind of document oriented storage seems like it could work well. Most changes to a customer's data consist of adding new transactions, and occasionally updating the expiration date.
We have a customer service interface that our support people use to view and change a customer's data. Getting all the data by fetching a single JSON document would be much simpler than the multiple queries that are now done to grab the data from the MySQL database it is in, spread across many normalized tables.
In general, with one notable exception, almost everything that deals with a customer is dealing with one customer at a time. For instance, a support person is dealing with the customer they have on the phone. The order processor is dealing the customer's account for the order that it is currently processing.
It's not even clear we need a database at all for this. Why not just a file per account, with the file containing a serialized Perl hash or JSON document or something similar, with a simple classic Unix file locking mechanism used to prevent corruption? This is easy to scale, for both reads and writes.
That brings me to the notable exception to "dealing with one customer at a time". Reports. Reports like to do things involving selecting data from a large number of customers, related by various factors such as having purchased particular SKUs in a particular time frame, or having their credit cards charged through a particular gateway, or things like that.
Reports seems to be something that fits in a lot better with the traditional relational approach.
If the set of reports needed was known up front, I think one could design a system that uses a document approach for storing customer data, but also maintains a relational reports database to hold data specifically for reports. We tend to have a changing set of reports--we do a lot of poking around and playing with data to try to find ways to improve our system and I'm often looking at things I had no idea a week before I'd ever need to look at for a report.