On Sunday, February 15, 2015 at 4:27:42 AM UTC-8, Michael Faughn wrote: > > Hello Everybody, > > First off. This is my first post here. That being the case I'll start > with a thank you to Jeremy Evans. Thanks, Jeremy. I work for a small > outfit that predominantly does gov't contracting (your tax dollars at work) > and we committed ourselves to using Sequel several years ago. We like it. > > We are just now getting around to tackling multi-tenancy. One web > application, many users / groups, data should only be shared within groups. > It looks like sharding is a reasonable approach to dealing with this. > Maybe there is a better solution? We're still looking around. >
I think sharding is only a decent solution for that if you have a small number on tenants. Once the number of tenants becomes large, sharding by tenant becomes unwieldy. But if you are doing gov't contracting with a separate tenant per contract, you may never have a large enough number of tenants to matter. Another way to handle it is to have a tenant_id in each table storing which tenant it belongs to. Then restrict all of your queries inside the app to the tenant that is logged in. If you would like an example of this, this app implements a multi-user accounting system with the same idea for access control: https://github.com/jeremyevans/spam. However, this commingles data from different tenants in the same table, which from a regulatory perspective may be a no-go. Also, this requires you are careful when writing your queries to make sure that a tenant cannot see data added by another tenant. > We have predominantly been using SQLite as our database of choice for most > applications. We are able to swap and use PostgreSQL and we've played > around with this some. Does the sharding functionality of Sequel work with > SQLite? Even if it does, is there case for using PostgreSQL (or something > else) instead? > Yes, you can use sharding with SQLite. I prefer PostgreSQL, but that's more because I dislike SQLite's type system and the inability to natively handle most schema modifications. Basically, if you can't run a server and need to store your data in a single file, SQLite is a good choice. If you can run a server, I'd choose PostgreSQL. However, that doesn't mean SQLite is a bad choice for your application, especially if you have more familiarity with it. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
