# How to design database: has\_and\_belongs\_to\_many

**URL:** <https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777>\
**Category:** Rails\
**Created:** [March 5, 2007, 10:36pm UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777 "2007-03-05T22:36:01Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 5, 2007, 10:36pm UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/1 "2007-03-05T22:36:01Z")

</div>

I’m not sure how to design my database. This is the casus:

There are users who can make declarations. And there are users who are  
coupled to the declaration. They are the ones who have to pay for the  
declaration. So the first users did pay for something in a store.  
Enters it in the system and add other users to the declaration. The  
costs are splitted among the coupled users.

For example: user A bought a crate of Beer for €9. His roommates (B  
and C) are going to drink too and the all decide to share the costs. A  
enters the crate in the system and everyone has to pay €3.

This is an existing application but I’m going to remake it in Ruby. (A  
part of) the current structure is like this:

Table: declarations  
user\_id  
amount  
description  
etc…

Table: declarations\_users  
user\_id  
declaration\_id

Is it possible to have the user\_id in the declarations table and in  
the many\_to\_many table? And does get this? Do I have to make an  
has\_and\_belongs\_to\_many declaration AND an belongs\_to?Or just one?

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 5, 2007, 10:55pm UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/2 "2007-03-05T22:55:03Z")

</div>

You can just… do what it is you’re trying to do.

class User  
has\_and\_belongs\_to\_many :declarations  
has\_many :my\_declarations, :class\_name =\> ‘Declarations’  
end

class Declaration  
has\_and\_belongs\_to\_many :users  
belongs\_to :owning\_user, :class\_name =\> ‘User’  
end

Now you refer to declaration.users to find all the users that belong to  
it  
You refer to declaration.owning\_user to find out the original owner

You refer to user.declarations to find all the declarations that belong  
to a  
user  
You refer to user.my\_declarations to find all the declarations that are  
owned by that user

This should work given the table descriptions in your email.

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 5, 2007, 11:04pm UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/3 "2007-03-05T23:04:05Z")

</div>

Ah thanks! I think this is exactly what I want.

I’m starting with Ruby on Rails, so maybe a dumb question:  
my\_declarations and owning\_user is an alias? So the db table field  
(for example) is still called “user\_id”?

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 5, 2007, 11:25pm UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/4 "2007-03-05T23:25:09Z")

</div>

Right… basically, the first part of declaring an association is  
telling it  
what name to give it in the class…  
If they didn’t set up default values for you, then EVERY declaration  
would  
look like  
has\_many :users, :class\_name =\> ‘User’, foreign\_key =\> ‘user\_id’  
But that’s the whole “convention over configuration” thing they talk  
about  
in the Rails community… since the chances are that 99% of the time,  
those  
are going to be right, it just has them set as defaults.

But, you can ALWAYS specify them, if you feel the need to, and  
especially if  
you’re needing to associations to the same object… you just use  
different  
first arguments, and then specify class\_name and foreign\_key if you have  
to.

You’re welcome… and there are no dumb questions: just relative levels  
of  
knoweldge.

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 6, 2007, 1:52am UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/5 "2007-03-06T01:52:23Z")

</div>

On 3/5/07, LeonB [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Thanks again for the nice reply. Is everyone that nice at the email  
> lists?

The Rails mailing list is surprisingly polite and helpful.

With this line:

> ```
> create_table :deposits do |table|
> drop_table :deposits
> 
> ```
> 
> end  
> end
> 
> with this line the logic would be complete if I understand it all  
> corectly?  
> belongs\_to :payer, :class\_name =\> ‘User’, foreign\_key =\>  
> ‘from\_user\_id’  
> belongs\_to :casher, :class\_name =\> ‘User’, foreign\_key =\>  
> ‘from\_user\_id’

I believe you intended one of those to be ‘to\_user\_id’ as opposed to  
bother  
‘from\_user\_id’, but other than that, yes it’s correct.

Where did you get this knowledge? By reading the API?

I got it just like this: trying to build something, not understanding,  
and  
either asking questions, or looking up the answer to specific questions  
in  
the API… just generally trying to read the API or really any other  
source  
of information without a specific example that you’re working towards  
will  
usually only get you 1/4 of the way to understanding what you’re  
doing…  
actually using the information is what makes you really understand. 🙂

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 5, 2007, 11:37pm UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/6 "2007-03-05T23:37:10Z")

</div>

Thanks again for the nice reply. Is everyone that nice at the email  
lists?

With this line:  
has\_many :users, :class\_name =\> ‘User’, foreign\_key =\> ‘user\_id’  
you can access the users with the instance variable @users in the  
controller?

I have created this table (for deposits from one user to another to  
pay for depts).

class CreateDeposits \< ActiveRecord::Migration  
def self.up  
transaction do  
create\_table :deposits do |table|  
table.column :from\_user\_id, :integer  
table.column :to\_user\_id, :integer  
table.column :amount, :integer  
table.column :confirmed, :boolean  
table.column :created\_at, :datetime  
table.column :updated\_at, :datetime  
end  
end

def self.down  
drop\_table :deposits  
end  
end

with this line the logic would be complete if I understand it all  
corectly?  
belongs\_to :payer, :class\_name =\> ‘User’, foreign\_key =\>  
‘from\_user\_id’  
belongs\_to :casher, :class\_name =\> ‘User’, foreign\_key =\>  
‘from\_user\_id’

Where did you get this knowledge? By reading the API?

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 8, 2007, 12:12am UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/7 "2007-03-08T00:12:24Z")

</div>

It just don’t get it. I thought the extra relationships would auto-  
generate some extra variables.  
So let’s say I do “declarations/show/1” and I would like to see all  
the coupled users (from declarations\_users). How do I access them in  
the view? Do I have to edit some additional controller code?

Thanks!

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 7, 2007, 12:15am UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/8 "2007-03-07T00:15:50Z")

</div>

Ah, thanks.  
I’m gonna try to make this work tomorrow. With a bit of luck I’ve got  
a working application some weeks from now.

---

<div class="post-metadata">

**Author:** ![leonb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/22d042/32.png) [@leonb](https://www.ruby-forum.com/u/leonb)\
**Post date:** [March 8, 2007, 12:14am UTC](https://www.ruby-forum.com/t/how-to-design-database-has-and-belongs-to-many/87777/9 "2007-03-08T00:14:25Z")

</div>

Ahhhhh… never mind. I got it! I read this sourcecode and found out  
that:  
@declaration.owner  
@declaration.users would work. And it dit!