Thanks very much for your answer, Jeremy. Very helpful. I had already started down the road of storing object ownership information in the database itself. I've hammered on the simple case by adding a column for owner_id to each table and filtered on that just as you suggest. We may eventually need to support some fairly complex cases for access control. You are helping to convince me that sharding is not the droid I'm looking for. -MF
On Sunday, February 15, 2015 at 12:13:56 PM UTC-5, Jeremy Evans wrote: > > 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.
