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 Michaux wrote the following on 30.06.2007 20:12 :

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.
  
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,

Peter Michaux wrote the following on 30.06.2007 20:12 :

> 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.

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)