# Linking one model to another **URL:** \ **Category:** Rails\ **Created:** [April 18, 2006, 4:53pm UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107 "2006-04-18T16:53:26Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 18, 2006, 4:53pm UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/1 "2006-04-18T16:53:26Z")
I have a User model and a Thing model. I want to link users with things, so I have a migration that creates a permissions table with user\_id and thing\_id. The migration works fine and gives me three columns, I assume I need the permission’s ID so that I can delete it in future. Three questions come to mind though, which I have given thought but got nowhere. 1. Does the permissions table need the ID column? 2. Is there any way in the backend/SQL (or otherwise) to ensure that a given pair of user\_id and thing\_id are unique? Obviously the columns cannot themselves be unique, because a user may have more than one permission. 3. What is the easiest way to make a UI to link users with things? Currently I just have two controllers that are slightly modified from the scaffold. Is there a neat way that’s friendly and quick for the end-user to assign users to things that is also fairly easy to code? I’ve looked for some examples in existing projects but haven’t found anything yet. Cheers! ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 18, 2006, 5:06pm UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/2 "2006-04-18T17:06:18Z")
David wrote: > I have a User model and a Thing model. I want to link users with > things, so I have a migration that creates a permissions table with > user\_id and thing\_id. The migration works fine and gives me three > columns, I assume I need the permission’s ID so that I can delete it in > future. > > Three questions come to mind though, which I have given thought but got > nowhere. > > 1. Does the permissions table need the ID column? No. In fact it shouldn’t. you can switch it off in the migration. > 1. Is there any way in the backend/SQL (or otherwise) to ensure that a > given pair of user\_id and thing\_id are unique? Obviously the columns > cannot themselves be unique, because a user may have more than one > permission. You can set the combination as a composite primary key constraint. ALTER TABLE things\_users ADD CONSTRAINT PRIMARY KEY (‘user\_id’, ‘thing\_id’) in MySQL In think. > 1. What is the easiest way to make a UI to link users with things? > Currently I just have two controllers that are slightly modified from > the scaffold. Is there a neat way that’s friendly and quick for the > end-user to assign users to things that is also fairly easy to code? > I’ve looked for some examples in existing projects but haven’t found > anything yet. There’s nothing prebuilt. I’d go with a combo with all users and combo with all things, in controller do User.find(params[:user\_id]) \<\< Thing.find(params[:thing\_id]) As long as User has\_and\_belongs\_to\_many :things and vice versa, that shoudl work 🙂 A. ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 18, 2006, 5:07pm UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/3 "2006-04-18T17:07:19Z")
On 4/18/06, David [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > I have a User model and a Thing model. I want to link users with > things, so I have a migration that creates a permissions table with > user\_id and thing\_id. The migration works fine and gives me three > columns, I assume I need the permission’s ID so that I can delete it in > future. > > Three questions come to mind though, which I have given thought but got > nowhere. > > 1. Does the permissions table need the ID column? Technically, no. But it’s not going to hurt either. Personally, I’d use one. > 1. Is there any way in the backend/SQL (or otherwise) to ensure that a > given pair of user\_id and thing\_id are unique? Obviously the columns > cannot themselves be unique, because a user may have more than one > permission. Yes. Check your database documentation. This is a common thing. > 1. What is the easiest way to make a UI to link users with things? > Currently I just have two controllers that are slightly modified from > the scaffold. Is there a neat way that’s friendly and quick for the > end-user to assign users to things that is also fairly easy to code? > I’ve looked for some examples in existing projects but haven’t found > anything yet. Assuming you’re on Rails 1.1+ take a look at the has\_many :through relationships. It’s exactly what you’re describing here. [http://wiki.rubyonrails.com/rails/pages/ThroughAssociations](http://wiki.rubyonrails.com/rails/pages/ThroughAssociations) – James ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 19, 2006, 9:54am UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/4 "2006-04-19T09:54:55Z")
Alan F. wrote: > David wrote: > > > 1. Does the permissions table need the ID column? > > No. In fact it shouldn’t. you can switch it off in the migration. Thanks, I’ll look. I guess I’ll also have to look at routing too, so that the delete action in the controller can take more than just an ID 🙂 > > 1. Is there any way in the backend/SQL (or otherwise) to ensure that a > > given pair of user\_id and thing\_id are unique? Obviously the columns > > cannot themselves be unique, because a user may have more than one > > permission. > > You can set the combination as a composite primary key constraint. > > ALTER TABLE things\_users ADD CONSTRAINT PRIMARY KEY (‘user\_id’, > ‘thing\_id’) in MySQL In think. Hm, it seems that the following works: ALTER TABLE permissions ADD UNIQUE (‘user\_id’, ‘tool\_id’) It seems I would have to use ‘execute’ in the migration to get this to work, but wouldn’t this break compatibility with other DB backends? Or can I make it optional somehow? Thanks for your help, I’ve learnt some new SQL 🙂 ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 19, 2006, 5:58pm UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/5 "2006-04-19T17:58:45Z")
On the forms you can do something like collection\_select(‘user’, ‘thing\_id’, @things, ‘id’, ‘name’) to do this you have to make a @things in the controller but collection\_select rocks for linking tables together like this. -Chuck ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 19, 2006, 10:16am UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/6 "2006-04-19T10:16:52Z")
David wrote: > ALTER TABLE permissions ADD UNIQUE (‘user\_id’, ‘tool\_id’) > > It seems I would have to use ‘execute’ in the migration to get this to > work, but wouldn’t this break compatibility with other DB backends? Or > can I make it optional somehow? I lie, the wiki page shows how this is possible: add\_index :permissions, [:thing\_id, :user\_id], :unique David ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [April 19, 2006, 9:56am UTC](https://www.ruby-forum.com/t/linking-one-model-to-another/58107/7 "2006-04-19T09:56:24Z")
James L. wrote: > On 4/18/06, David [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > 1. What is the easiest way to make a UI to link users with things? > > Currently I just have two controllers that are slightly modified from > > the scaffold. Is there a neat way that’s friendly and quick for the > > end-user to assign users to things that is also fairly easy to code? > > I’ve looked for some examples in existing projects but haven’t found > > anything yet. > > Assuming you’re on Rails 1.1+ take a look at the has\_many :through > relationships. It’s exactly what you’re describing here. Perfect, now I need to work out how to do it in code and arrange the forms so they are easy to use. Thanks for your help. David