Circular foreign keys and order of creation

Hi,

I have two database tables

accounts
id
owner_id

users
id
account_id

An account must have an owner_id which is a user’s id. So the owner_id
field must not be null. A user must have an account_id and so
account_id must not be null also. I create the account first but then
the owner_id has to be null at least until the user is created and I
know the user’s id. This is a problem like the chicken and the egg.

I searched google but wasn’t able to find how to handle this problem
in a Rails app.

One approach is I can allow owner_id to be null and when creating the
account I can create the account, create the user with the account_id,
and then set the owner_id on the account. Then check that all went
well. This whole dance happens in the Ruby of my Account model. This
doesn’t seem like fully safe approach. Is there something better?

I’m using PostgreSQL but am hoping there is a database agnostic
solution.

Any suggestions?

Thanks,
Peter

Peter M. wrote the following on 30.06.2007 20:12 :

account_id
One approach is I can allow owner_id to be null and when creating the
account I can create the account, create the user with the account_id,
and then set the owner_id on the account. Then check that all went
well. This whole dance happens in the Ruby of my Account model. This
doesn’t seem like fully safe approach. Is there something better?

I’m using PostgreSQL but am hoping there is a database agnostic solution.

This has nothing to do with RoR…
Just drop one of the key. Only one is needed to link your objects,
trying to have both refer to the other is duplicating information.

For RoR specifics on one-to-one relationships, look at the documentation
of has_one and belongs_to.

Lionel.

Hi Lionel,

On Jun 30, 11:33 am, Lionel B. [email protected]
wrote:

account_id
One approach is I can allow owner_id to be null and when creating the
account I can create the account, create the user with the account_id,
and then set the owner_id on the account. Then check that all went
well. This whole dance happens in the Ruby of my Account model. This
doesn’t seem like fully safe approach. Is there something better?

I’m using PostgreSQL but am hoping there is a database agnostic solution.

This has nothing to do with RoR…
Just drop one of the key. Only one is needed to link your objects,
trying to have both refer to the other is duplicating information.

I don’t think this is duplicate information because an account can
have more than one user but only one user is the owner user.

For RoR specifics on one-to-one relationships, look at the documentation
of has_one and belongs_to.

I think this is a one to many and a one to one.

Any other suggestions?

Thanks
Peter

I don’t think this is duplicate information because an account can
have more than one user but only one user is the owner user.

Sorry, didn’t get it.

You have a choice : either the account can exist without an owner or a
user can exist without an account (or both are independent). If you must
ensure that two of them are linked you’ll have to use transactions (in
fact I had this very same problem in another context and had to use
transactions to make sure that both objects where created linked with
one another).

Lionel

Hi,
From a relational point of view, is an account has a mandatory user, and
a
user has a mandatory account, a “user” IS an “account”

I would maybe model an User table, with a self-relationship for sharing
account between users, an a flag telling if it is an account or not.
But it depends what others attributes you got in your different table.
If you want to retain 2 tables, you should drop one of the “not null”
constraint (or use deferrable constraints if postgres support that)

2007/6/30, Peter M. [email protected]:

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs