thoughts: 1. why even use a relational database for logging ? especially with 5M rec/month/device/customer, how often is there a genuine SELECT with special criteria for those records ?
2. im not a huge fan of table-per-record. relational schemas aren't designed to work this way, the schema should reflect the geometry of the data, not the data itself. new table means things like tablespaces, disk partitions, etc., that is, they're not "soft" constructs that an app should be just creating and dropping at will, any more than an application would be automatically mounting new filesystems on the fly. 3. deletes shouldn't lock the whole table? if you're on a klunky DB like SQL Server, there are even hints to optimize this. 4. database per customer is more traditional here, but isn't terribly easy to manage if you have thousands of customers On Jun 29, 2012, at 7:11 PM, espresso maker wrote: > Hi there, > > I'd like to get some input on the following design approach. > > I am creating a service where I have to save the customer's device logs. > Every customer can have more than one device and every device can have as > many as 5M records over a period of month or so. > > Having a single table to store all the customer's logs didn't seem to work > because when the device is removed and the logs have to be deleted the table > gets locked for a while. > > So I came up with an alternative solution (haven't implemented yet) and that > is to have a table per customer device. In this case, I just drop the table > to remove the device log records. > > But then I thought why not create a database per customer, and a table per > customer device? This isolate things even more between customers. > > My question is, is this a good idea? Am I missing something? > > If this approach is fine. How would I go on creating the dynamic table names > and bind the declarative class to the table name? > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/0mQ_gVePnSMJ. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
