+1 to all of what Ben says. I've used composite keys with the CPK gem in apps with legacy DBs - based on this experience it's not something I would consciously choose when starting a new rails app. As Ben says, there's a lot of conventions that need to be overridden to make it work consistently.
On 03/04/2013, at 10:51 PM, Ben Hoskings <[email protected]> wrote: > Firstly, I'd keep this display ID (maybe "member ID") separate from users.id. > Any non-standard behaviour there is asking for trouble. There's a lot of > convention built up in activerecord around having a single artificial integer > PK, and veering from this standard isn't worth the trouble it causes. (We > used some composite PKs for a time at TC as part of a migration strategy, and > they were very inconvenient to work with.) > > I'd recommend having a unique users.id column like normal, and then a > separate member_id that you use for routing, which is unique within each > tenant (enforced in the DB). > > I'd add a unique index to the users table on (tenant_id, member_id): > > add_index :users, [:tenant_id, :member_id], unique: true > > Then I'd add a validation in the model, for reasonable error messages (the > real "validation" is done by the DB's unique index): > > validates :member_id, presence: true, uniqueness: {scope: :tenant_id} > > Then I'd auto-populate that field before the model's validation happens. > > before_validation :assign_member_id_if_required > > def assign_member_id_if_required > self.member_id ||= begin > max_member_id = tenant.users.order('member_id > DESC').limit(1).pluck(:member_id).first > (max_member_id || 0) + 1 > end > end > > That will query efficiently: > > SELECT "users"."member_id" FROM "users" WHERE "users"."tenant_id" = 12345 > ORDER BY member_id DESC LIMIT 1 > > - Ben > > > On 3 April 2013 20:29, Rich Buggy <[email protected]> wrote: > Hi everyone, > > I'm building a multi-tenant application and I'd like some of the ID's to be > per tenant. It's using the path based multi-tenant approach. For example - > /:account/invoices/:id > > I've got the routing working correctly but the ID's are being generated > automatically so they are unique across the application, not just the tenant. > This isn't ideal because each tenant should start from 1. > > I already store the next id in the tenant table so they can choose to start > from a number other than 1. This is important as someone with existing data > may decide to not start from 1. I think there are a number of ways I can deal > with this. > > 1. If I was doing this without rails I'd make the tenant_id + id a composite > field and set the id manually when creating the record. Is there are an easy > way to do this with rails? > > 2. I could add number field and accept that number != id. This is easy but > ugly because /demo/invoices/123456 could be invoice 1 > > 3. I could add a number field and try to muck with the routing so it used > number instead of id > > Does anyone know of a good guide or blog post on how to do this? > > Rich > > -- > You received this message because you are subscribed to the Google Groups > "Ruby or Rails Oceania" 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/rails-oceania?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > > -- > Cheers > Ben > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby or Rails Oceania" 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/rails-oceania?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" 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/rails-oceania?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
