Rails - Feb 2006 - why there is no automatic relationship discovery

When I explain RoR to someone I don''t have a convincing argument that  
explains why everything is so automatic and transparent, but  
relationships have to be encoded by hand in models. That shows I  
don''t understand well that part of AR.

There''s the argument that says that not all databases offer metadata  
about foreign keys, but the natural question then is that, if the  
reason is that one, why it does not automate this when your database  
do offer that metadata. I am sure the real reason is more subtle than  
that.

Can anyone elaborate a once and for all explanation for this?

-- fxn

On 09 Feb 2006, at 10:35, Xavier Noria wrote:
> When I explain RoR to someone I don''t have a convincing argument  
> that explains why everything is so automatic and transparent, but  
> relationships have to be encoded by hand in models. That shows I  
> don''t understand well that part of AR.
>
> There''s the argument that says that not all databases offer  
> metadata about foreign keys, but the natural question then is that,  
> if the reason is that one, why it does not automate this when your  
> database do offer that metadata. I am sure the real reason is more  
> subtle than that.
>
> Can anyone elaborate a once and for all explanation for this?
Because it is model logic and should thus belong in your model. Your  
database is just a means of storing and retrieving your data. I do  
believe database constraints and relations can provide an extra means  
of ensuring data integrity, but the fact remains that your  
relationships should be defined in your model. Some people think of  
the database as their model and put their model logic in the database  
while in fact it''s not.

This might not be the best analogy, but it''s all I can come up with  
at the moment: it''s not because when I buy a parachute and the box  
says: "Ready for you to jump", I''m just going to hop on a
plane and
jump out without checking myself.


Best regards

Peter De Berdt

On Thu, Feb 09, 2006 at 10:35:25AM +0100, Xavier Noria
wrote:
> When I explain RoR to someone I don''t have a convincing argument
that
> explains why everything is so automatic and transparent, but  
> relationships have to be encoded by hand in models. That shows I  
> don''t understand well that part of AR.
> 
> There''s the argument that says that not all databases offer
metadata
> about foreign keys, but the natural question then is that, if the  
> reason is that one, why it does not automate this when your database  
> do offer that metadata. I am sure the real reason is more subtle than  
> that.
> 
> Can anyone elaborate a once and for all explanation for this?
I wondered the same thing when I first started.

I can think of a few reasons why this would not be automatically
generated. For one, there''s a performance hit in autoloading related
objects if you don''t need them. I think that the objects
aren''t
actually loaded until they''re needed, but I''m not sure about
that.

Unlike the standard crud operations, there''s a lot that can go
wrong when you try to infer semantic relationships from structure.

Although, yeah, I think it would be nice to have an equivalent for the
scaffold for this, so it can take its best guess to get you up and
running quickly, and then you can fix the parts that aren''t right.

I think the answer you''re looking for is that the relationship
management features in AR are still evolving. That means there may not
be a "once and for all explanation".

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On Feb 9, 2006, at 14:37, Peter De Berdt wrote:
> On 09 Feb 2006, at 10:35, Xavier Noria wrote:
>
>> When I explain RoR to someone I don''t have a convincing
argument
>> that explains why everything is so automatic and transparent, but  
>> relationships have to be encoded by hand in models. That shows I  
>> don''t understand well that part of AR.
>>
>> There''s the argument that says that not all databases offer  
>> metadata about foreign keys, but the natural question then is  
>> that, if the reason is that one, why it does not automate this  
>> when your database do offer that metadata. I am sure the real  
>> reason is more subtle than that.
>>
>> Can anyone elaborate a once and for all explanation for this?
>
> Because it is model logic and should thus belong in your model.  
> Your database is just a means of storing and retrieving your data.  
> I do believe database constraints and relations can provide an  
> extra means of ensuring data integrity, but the fact remains that  
> your relationships should be defined in your model. Some people  
> think of the database as their model and put their model logic in  
> the database while in fact it''s not.
I see, but in my view by the same reasoning you shouldn''t build  
classes based on table metadata. And we do.

I believe the problem is technical, like not being able to guess the  
correct relationships in some cases or something like that.

-- fxn

On Feb 9, 2006, at 8:27 AM, Xavier Noria wrote:
> On Feb 9, 2006, at 14:37, Peter De Berdt wrote:
>
>> On 09 Feb 2006, at 10:35, Xavier Noria wrote:
>>
>>> When I explain RoR to someone I don''t have a convincing
argument
>>> that explains why everything is so automatic and transparent, but  
>>> relationships have to be encoded by hand in models. That shows I  
>>> don''t understand well that part of AR.
>>>
>>> There''s the argument that says that not all databases
offer
>>> metadata about foreign keys, but the natural question then is  
>>> that, if the reason is that one, why it does not automate this  
>>> when your database do offer that metadata. I am sure the real  
>>> reason is more subtle than that.
>>>
>>> Can anyone elaborate a once and for all explanation for this?
>>
>> Because it is model logic and should thus belong in your model.  
>> Your database is just a means of storing and retrieving your data.  
>> I do believe database constraints and relations can provide an  
>> extra means of ensuring data integrity, but the fact remains that  
>> your relationships should be defined in your model. Some people  
>> think of the database as their model and put their model logic in  
>> the database while in fact it''s not.
>
> I see, but in my view by the same reasoning you shouldn''t build  
> classes based on table metadata. And we do.
>
> I believe the problem is technical, like not being able to guess  
> the correct relationships in some cases or something like that.
This is exactly the reason. It''s impossible to differentiate a one-to- 
many join that currently only has one corresponding row in the
''many''
table from a one-to-one join merely on table and database metadata.  
You can know a join existing based on foreign key constraints, but  
you can''t know the type of join. Which is why we specify it.



--
Jason Perkins
jperkins@sneer.org

"The computer allows you to make mistakes
faster than any other invention, with the
possible exception of handguns and tequila."

On Thu, Feb 09, 2006 at 08:31:36AM -0600, Jason Perkins wrote:
[...]
> >I believe the problem is technical, like not being able to guess  
> >the correct relationships in some cases or something like that.
> 
> This is exactly the reason. It''s impossible to differentiate a
one-to-
> many join that currently only has one corresponding row in the
''many''
> table from a one-to-one join merely on table and database metadata.  
> You can know a join existing based on foreign key constraints, but  
> you can''t know the type of join. Which is why we specify it.
Although I don''t think rails enforces this, 1:1 joins should have the
foreign key reference field as part of the primary key.

It is possible to tell the join types apart:

1:n --> standard foreign key
1:1 --> foreign key is in primary key (or is only primary key)
n:n --> mapping table

It''s a bit more difficult to discern the "acts_as" extensions
to the
basic relationship types, but even that should be obvious in some
cases (like if you have a self-referential key).

Still, the scaffolding isn''t perfect, so I wouldn''t see any
reason to
expect automatic relationship inference to be. It should be able to be
good enough.

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On 2/9/06, Jason Perkins <jperkins@sneer.org>
wrote:
>
> On Feb 9, 2006, at 8:27 AM, Xavier Noria wrote:
>
> > On Feb 9, 2006, at 14:37, Peter De Berdt wrote:
> >
> >> On 09 Feb 2006, at 10:35, Xavier Noria wrote:
> >>
<snip>
> >
> > I believe the problem is technical, like not being able to guess
> > the correct relationships in some cases or something like that.
>
> This is exactly the reason. It''s impossible to differentiate a
one-to-
> many join that currently only has one corresponding row in the
''many''
> table from a one-to-one join merely on table and database metadata.
> You can know a join existing based on foreign key constraints, but
> you can''t know the type of join. Which is why we specify it.
>
>
Type of join?  Why not just punt and say if you have a foreign key
defined on you, you belong to the other table, if you are referenced
by the other table, you have many.  You will be right 99% of the time,
and if you''re not, you just need to s/many/one/ .  This would be a
boon to people who are migrating to rails.  I can still feel my
tendons from the time I spent typing all my relationships into models.
 I would have loved to do

ruby script/generate model-guess-relationships-please modelname tablename

especially since I also had to set_table_name and set_primary_key and
:foreign_key => foo for all of them.

- Ian
>
> --
> Jason Perkins
> jperkins@sneer.org
>
> "The computer allows you to make mistakes
> faster than any other invention, with the
> possible exception of handguns and tequila."
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Feb 9, 2006, at 08:42 AM, Adam Fields wrote:
> On Thu, Feb 09, 2006 at 08:31:36AM -0600, Jason Perkins wrote:
> [...]
>>> I believe the problem is technical, like not being able to guess
>>> the correct relationships in some cases or something like that.
>>
>> This is exactly the reason. It''s impossible to differentiate a
one-
>> to-
>> many join that currently only has one corresponding row in the
''many''
>> table from a one-to-one join merely on table and database metadata.
>> You can know a join existing based on foreign key constraints, but
>> you can''t know the type of join. Which is why we specify it.
>
> Although I don''t think rails enforces this, 1:1 joins should have
the
> foreign key reference field as part of the primary key.
That''s clever. The downside to that is that it makes the database  
structure difficult to modify with that information now encoded in  
the primary key of a table and I imagine that''s why the normal method  
that I''ve seen to do a 1:1 is a foreign key in the second table -  
with a unique constraint if your database supports them. If not, no  
unique constraint and you encode that information in your application  
framework.



--
Jason Perkins
jperkins@sneer.org

"As democracy is perfected, the office of president represents,
more and more closely, the inner soul of the people.  On some
great and glorious day the plain folks of the land will reach
their heart''s desire at last and the White House will be adorned
by a downright moron."        -  H.L. Mencken (1880 - 1956)

On Feb 9, 2006, at 6:42 AM, Adam Fields wrote:
> On Thu, Feb 09, 2006 at 08:31:36AM -0600, Jason Perkins wrote:
> [...]
>>> I believe the problem is technical, like not being able to guess
>>> the correct relationships in some cases or something like that.
>>
>> This is exactly the reason. It''s impossible to differentiate a
one-
>> to-
>> many join that currently only has one corresponding row in the
''many''
>> table from a one-to-one join merely on table and database metadata.
>> You can know a join existing based on foreign key constraints, but
>> you can''t know the type of join. Which is why we specify it.
>
> Although I don''t think rails enforces this, 1:1 joins should have
the
> foreign key reference field as part of the primary key.
>
> It is possible to tell the join types apart:
>
> 1:n --> standard foreign key
> 1:1 --> foreign key is in primary key (or is only primary key)
> n:n --> mapping table
1:1 could also simply be unique constraint (unique index) on the
foreign key field.

-- 
-- Tom Mornini

On Thu, Feb 09, 2006 at 08:34:59AM -0800, Tom Mornini
wrote:
> >It is possible to tell the join types apart:
> >
> >1:n --> standard foreign key
> >1:1 --> foreign key is in primary key (or is only primary key)
> >n:n --> mapping table
> 
> 1:1 could also simply be unique constraint (unique index) on the
> foreign key field.
For 1:1 mappings, I''ve always used the primary key field, because I
think of this as an "is_a" relationship rather than a
"has_a". In that
case, it makes sense to me to share the primary key between the two
tables. I use this mostly for polymorphic associations, and also
sometimes for things like nonflexible property sheets where it doesn''t
make sense to store the additional fields with the main table.

I suppose you might want to do it differently if your target table has
multiple 1:1 relationships with other tables, but I''ve never seen that
come up.

Just curious - can you give me an example of where you''d use this as a
non-primary key preferentially?

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On Feb 9, 2006, at 8:57 AM, Adam Fields wrote:
> On Thu, Feb 09, 2006 at 08:34:59AM -0800, Tom Mornini wrote:
>>> It is possible to tell the join types apart:
>>>
>>> 1:n --> standard foreign key
>>> 1:1 --> foreign key is in primary key (or is only primary key)
>>> n:n --> mapping table
>>
>> 1:1 could also simply be unique constraint (unique index) on the
>> foreign key field.
>
> For 1:1 mappings, I''ve always used the primary key field, because
I
> think of this as an "is_a" relationship rather than a
"has_a". In that
> case, it makes sense to me to share the primary key between the two
> tables. I use this mostly for polymorphic associations, and also
> sometimes for things like nonflexible property sheets where it
doesn''t
> make sense to store the additional fields with the main table.
>
> I suppose you might want to do it differently if your target table has
> multiple 1:1 relationships with other tables, but I''ve never seen
that
> come up.
>
> Just curious - can you give me an example of where you''d use this
as a
> non-primary key preferentially?
I don''t see they''re different in any way, or how one is an
advantage
over
the other, except perhaps for DB optimization.

Oracle, at one point, couldn''t use multi-key indexes unless the query  
you
wrote only accessed leftmost members of multicolumn indexes. In that  
case,
two separate indexes are more flexible than one. I''m not sure that this
limitation exists anymore...and I have no idea whatsoever whether  
this is
an issue in PostgreSQL or (shudder) MySQL.

In fact, if your database has the same limitation, and you make a multi
column primary key of (id,foreign_id), joins using that foreign_id are
going to be very costly.

-- 
-- Tom Mornini

On Feb 9, 2006, at 10:57 AM, Adam Fields wrote:
> For 1:1 mappings, I''ve always used the primary key field, because
I
> think of this as an "is_a" relationship rather than a
"has_a". In that
> case, it makes sense to me to share the primary key between the two
> tables. I use this mostly for polymorphic associations, and also
> sometimes for things like nonflexible property sheets where it
doesn''t
> make sense to store the additional fields with the main table.
>
> I suppose you might want to do it differently if your target table has
> multiple 1:1 relationships with other tables, but I''ve never seen
that
> come up.
>
> Just curious - can you give me an example of where you''d use this
as a
> non-primary key preferentially?
User has a login.

--
Jason Perkins
jperkins@sneer.org

"The computer allows you to make mistakes
faster than any other invention, with the
possible exception of handguns and tequila."

On Feb 9, 2006, at 9:10 AM, Tom Mornini wrote:
>> Just curious - can you give me an example of where you''d use
this
>> as a
>> non-primary key preferentially?
>
> I don''t see they''re different in any way, or how one is
an
> advantage over
> the other, except perhaps for DB optimization.
>
> Oracle, at one point, couldn''t use multi-key indexes unless the  
> query you
> wrote only accessed leftmost members of multicolumn indexes. In  
> that case,
> two separate indexes are more flexible than one. I''m not sure that
> this
> limitation exists anymore...and I have no idea whatsoever whether  
> this is
> an issue in PostgreSQL or (shudder) MySQL.
>
> In fact, if your database has the same limitation, and you make a  
> multi
> column primary key of (id,foreign_id), joins using that foreign_id are
> going to be very costly.
Sorry to reply to my own post, but I just realized another HUGE  
difference!

Your method of adding the foreign key into the primary key and my method
of placing a unique constraint on the foreign key (making it an  
alternate
key) have entirely different semantics, and my method is more compatible
with the Rails definition of has_one than yours.

With your method, this would be allowed:

insert into table (id,foreign_id) values (1,1)
insert into table (id,foreign_id) values (1,2)
insert into table (id,foreign_id) values (2,1)
insert into table (id,foreign_id) values (2,2)

Which means that your method is doubly incompatible with Rails'' has_one
semantics:

   1) id is supposed to be unique in a table, period.
   2) foreign table has_two entries in table. :-)

With my method, you''d get this:


insert into table (id,foreign_id) values (1,1)
insert into table (id,foreign_id) values (1,2) <- errors out (id is  
not unique)
insert into table (id,foreign_id) values (2,1)
insert into table (id,foreign_id) values (2,2) <- errors out  
(foreign_id is not unique)

-- 
-- Tom Mornini

On Thu, Feb 09, 2006 at 09:21:07AM -0800, Tom Mornini wrote:
[...]
> Your method of adding the foreign key into the primary key and my method
> of placing a unique constraint on the foreign key (making it an  
> alternate
> key) have entirely different semantics, and my method is more compatible
> with the Rails definition of has_one than yours.
You''ve missed what I was saying. In most cases, the foreign key IS the
primary key, not added to the primary key.

There''s no (id, foreign_id), there''s just id, which is itself
a
foreign key.

In Jason''s example of "User has a login", I''d
probably do it this way,
so that user.id = login.id for any given user/login. 

The only drawback to that, mentioned previously, is that then login
can only be associated with user in that way. If you want to associate
login with something else (also in a 1:1 relationship), you need to do
it differently. But as I said - I don''t think I''ve seen a case
in
practice where a subsidiary table is bound to more than one other
table with a 1:1 relationship.
> With your method, this would be allowed:
> 
> insert into table (id,foreign_id) values (1,1)
> insert into table (id,foreign_id) values (1,2)
> insert into table (id,foreign_id) values (2,1)
> insert into table (id,foreign_id) values (2,2)
> 
> Which means that your method is doubly incompatible with Rails''
has_one
> semantics:
> 
>   1) id is supposed to be unique in a table, period.
>   2) foreign table has_two entries in table. :-)
Right. Using the primary key as a foreign key gets you that.
> With my method, you''d get this:
> 
> 
> insert into table (id,foreign_id) values (1,1)
> insert into table (id,foreign_id) values (1,2) <- errors out (id is  
> not unique)
> insert into table (id,foreign_id) values (2,1)
> insert into table (id,foreign_id) values (2,2) <- errors out  
> (foreign_id is not unique)

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On Feb 9, 2006, at 9:56 AM, Adam Fields wrote:
> On Thu, Feb 09, 2006 at 09:21:07AM -0800, Tom Mornini wrote:
> [...]
>> Your method of adding the foreign key into the primary key and my  
>> method
>> of placing a unique constraint on the foreign key (making it an
>> alternate
>> key) have entirely different semantics, and my method is more  
>> compatible
>> with the Rails definition of has_one than yours.
>
> You''ve missed what I was saying. In most cases, the foreign key IS
the
> primary key, not added to the primary key.
Ah, got it, sorry for that!

Well, my gut is still the same, though for less technical reasons:

You''re method puts you off the rails. The foreign key is misnamed
with respect to Rails'' naming conventions.

--
-- Tom Mornini

On Feb 9, 2006, at 10:04 AM, Tom Mornini wrote:
> You''re method puts you off the rails.
Must. Type. More. Slowly...

Your method puts you off the rails.

-- 
-- Tom Mornini

On Thu, Feb 09, 2006 at 10:04:28AM -0800, Tom Mornini
wrote:
> >You''ve missed what I was saying. In most cases, the foreign
key IS the
> >primary key, not added to the primary key.
> 
> Ah, got it, sorry for that!
> 
> Well, my gut is still the same, though for less technical reasons:
> 
> You''re method puts you off the rails. The foreign key is misnamed
> with respect to Rails'' naming conventions.
Nope. Still called "id".

There''s nothing that says a foreign key has to be called anything in
particular.

Yes, when you set up the relations in rails, you need to override the
default names, but that''s expected behavior. There''s nothing
that says
that you have to stick to the default naming conventions, and there
are plenty of exceptions. Doing so doesn''t "put you off the
rails".

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On Feb 9, 2006, at 11:56 AM, Adam Fields wrote:
> On Thu, Feb 09, 2006 at 09:21:07AM -0800, Tom Mornini wrote:
> [...]
>> Your method of adding the foreign key into the primary key and my  
>> method
>> of placing a unique constraint on the foreign key (making it an
>> alternate
>> key) have entirely different semantics, and my method is more  
>> compatible
>> with the Rails definition of has_one than yours.
>
> You''ve missed what I was saying. In most cases, the foreign key IS
the
> primary key, not added to the primary key.
>
> There''s no (id, foreign_id), there''s just id, which is
itself a
> foreign key.
>
> In Jason''s example of "User has a login", I''d
probably do it this way,
> so that user.id = login.id for any given user/login.
>
> The only drawback to that, mentioned previously, is that then login
> can only be associated with user in that way. If you want to associate
> login with something else (also in a 1:1 relationship), you need to do
> it differently. But as I said - I don''t think I''ve seen a
case in
> practice where a subsidiary table is bound to more than one other
> table with a 1:1 relationship.
My concern is what happens when the business rule that one user is  
only ever associated with one login becomes one user can have more  
than one login.

1) modifying has_one to has_many is the trivial solution;
2) modifying a unique constraint on a column is nearly as trivial;
3) the foreign key as a primary key is going to be the most difficult  
to content with - especially on a production database. I''ll readily  
admit that I have no idea if migrations would help you with this or not.


--
Jason Perkins
jperkins@sneer.org

"The computer allows you to make mistakes
faster than any other invention, with the
possible exception of handguns and tequila."

On Feb 9, 2006, at 10:11 AM, Adam Fields wrote:
> On Thu, Feb 09, 2006 at 10:04:28AM -0800, Tom Mornini wrote:
>
>>> You''ve missed what I was saying. In most cases, the
foreign key
>>> IS the
>>> primary key, not added to the primary key.
>>
>> Ah, got it, sorry for that!
>>
>> Well, my gut is still the same, though for less technical reasons:
>>
>> You''re method puts you off the rails. The foreign key is
misnamed
>> with respect to Rails'' naming conventions.
>
> Nope. Still called "id".
Wrong column. I said the foreign key is misnamed.
> There''s nothing that says a foreign key has to be called anything
in
> particular.
>
> Yes, when you set up the relations in rails, you need to override the
> default names, but that''s expected behavior.
How can overriding default behavior be considered expected behavior?
Expected by who?
> There''s nothing that says that you have to stick to the default
naming
> conventions, and there are plenty of exceptions. Doing so doesn''t
"put
> you off the rails".
I completely, though respectfully, disagree.

Why do the extra work? Of what benefit is it to use your method, which
will cause extra work and more difficulty in comprehension for each and
every person who needs to work with the schema the Rails code in the
future?

Rails gives you the ability to override so that:

   1) People can''t complain and say "Rails is too
restrictive"
   2) It will work with legacy schemas
   3) It''s flexible enough to handle really weird situations

What it does do, is make it simple in the beginning and *particularly*
in the future, if you just follow the conventions. It''s the difference
between the carrot and stick...

So, IMHO, while what you''re doing is technically supported,
it''s
definitely
NOT on the Rails.

P.S. Did you know that you can write routes that will allow you to  
direct
      incoming requests to controllers with different names? Or that you
      can use layouts and views that don''t match the controller names?
      The framework is flexible enough to handle this, much as rope is
      flexible enough to be used for many purposes, including hanging
      oneself. I''m *not* suggesting there''s never a reason to
do any
of the
      things mentioned, but I *am* suggesting that there had better  
be some
      *very* good reasons to do so, or you''ve just created extra work  
for
      yourself, and for each and every person who will support your  
app in
      the future.

-- 
-- Tom Mornini

On Thu, Feb 09, 2006 at 12:15:04PM -0600, Jason Perkins
wrote:
> My concern is what happens when the business rule that one user is  
> only ever associated with one login becomes one user can have more  
> than one login.
>
> 1) modifying has_one to has_many is the trivial solution;
> 2) modifying a unique constraint on a column is nearly as trivial;
> 3) the foreign key as a primary key is going to be the most difficult  
> to content with - especially on a production database. I''ll
readily
> admit that I have no idea if migrations would help you with this or not.
To be honest, I stay away from 1:1 relationships most of the time
anyway, for exactly this reason. If there''s any chance that it will
become a 1:n relationship at some point in the future, you''re better
off modeling it that way up front. True 1:1 relationships are for
something very specific that doesn''t come up very often.

Other than that, this isn''t substantially more difficult than any
other schema change.

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

Hi ..

You wrote:
> 
> I believe the problem is technical, like not being able to guess the 
> correct relationships in some cases or something like that.
> 
My take is that it is hard to do correctly, and so it is better not to 
do it at all and leave it as an exercise for the reader ;)

Consider how the process of data modeling works: we start with a 
conceptual model, then we move to a physical model, then we go to an 
implementation.  As we move through the chain, our thinking turns more 
to practical matters (performance, security, DB specific matters (the 
use of stored procedures, etc.)).  In the end our implementation has a 
lot more stuff than the conceptual model.

So, what we are asking AR, or any other ORM, to do is unwind that 
process.  It is hard.  If the mapping through the model chain remained 
consistent, and we could assume a standard SQL, say SQL-92, and we 
assumed that the designer worked to 3rd normal form, then we may have 
some hope of being accurate.

If they didn''t, then the model generated by an ORM will require work. 
Perhaps it could get us 80% of the way, maybe not.  It would really 
depend on how clever the reverse engineering was.

One way that I have found that can help is to use a good DB modeling 
tool, such as Data Architect from Sybase, to reverse engineer databases. 
  Do that a few times and you get a grasp on why it is so tricky.

My $0.02, the authoritative will have to come from someone else :)

Regards,

-- 

-mark.

----------------------------------------------------
Mark Probert                 probertm at acm dot org
----------------------------------------------------

On Thu, Feb 09, 2006 at 10:29:12AM -0800, Tom Mornini
wrote:
> >Nope. Still called "id".
> 
> Wrong column. I said the foreign key is misnamed.
My bad then. I misunderstood what you said.
> >There''s nothing that says a foreign key has to be called
anything in
> >particular.
> >
> >Yes, when you set up the relations in rails, you need to override the
> >default names, but that''s expected behavior.
> 
> How can overriding default behavior be considered expected behavior?
> Expected by who?
Expected by the fact that there''s an API to do so, and doing so
doesn''t break the framework.

There are actually two questions here. One is whether you should ever
override the default naming conventions, and the other is whether it''s
appropriate in this particular case.

It''s not like you''re changing the source - the belongs_to,
has_one,
and has_many methods take parameters for changing the defaults. This
isn''t a hack, it''s part of the design, and these are standard
options.

I see this as being in the same class as find_by_sql. You''re doing
something that the default configuration doesn''t understand, and that
may or may not be a bad thing, judged on a case by case basis.
> >There''s nothing that says that you have to stick to the
default naming
> >conventions, and there are plenty of exceptions. Doing so
doesn''t "put
> >you off the rails".
> 
> I completely, though respectfully, disagree.
[...]
> So, IMHO, while what you''re doing is technically supported,
it''s
> definitely NOT on the Rails.
Interesting point, but where''s the line?

What set of constraints defines whether you''re "on the rails"
or not?
> P.S. Did you know that you can write routes that will allow you to  
> direct
>      incoming requests to controllers with different names? Or that you
>      can use layouts and views that don''t match the controller
names?
>      The framework is flexible enough to handle this, much as rope is
>      flexible enough to be used for many purposes, including hanging
>      oneself. I''m *not* suggesting there''s never a reason
to do any
> of the
>      things mentioned, but I *am* suggesting that there had better  
> be some
>      *very* good reasons to do so, or you''ve just created extra
work
> for
>      yourself, and for each and every person who will support your  
> app in
>      the future.
I see this as a slightly different case though, because if you''re
defining routes to alternate controllers, you''re bypassing internal
rails structures. When you''re choosing different names for foreign
keys, you''re defining how rails interacts with external entities, in
this case the database. I''d argue that when doing that, you should do
what''s best for the overall design, and that won''t always be
"the
rails way". It''s a point in rails''s favor that
it''s accomodating on
this front.

That''s true in the general case. In this particular case, I''m
not
convinced either way, and as I pointed out earlier, I often avoid 1:1
associations because of this ambiguity anyway.

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On Feb 9, 2006, at 01:43 PM, Adam Fields wrote:
>
> That''s true in the general case. In this particular case,
I''m not
> convinced either way, and as I pointed out earlier, I often avoid 1:1
> associations because of this ambiguity anyway.
It''s only when employing the method of constructing 1:1 relationships  
that you suggested that problems arise in both modifying the  
relationship to a 1:many and bucking the Rails naming convention  
causing the need to override the name of the foreign key for the  
has_one association. What else do you need to convince you that the  
method of using a foreign key as a primary key is suboptimal? There''s  
nothing gained over using either the Rails has_one and/or specifying  
the foreign key column as UNIQUE and their are several detriments.



--
Jason Perkins
jperkins@sneer.org

"The computer allows you to make mistakes
faster than any other invention, with the
possible exception of handguns and tequila."

On Feb 9, 2006, at 02:10 PM, Jason Perkins wrote:
>
> On Feb 9, 2006, at 01:43 PM, Adam Fields wrote:
>
>>
>> That''s true in the general case. In this particular case,
I''m not
>> convinced either way, and as I pointed out earlier, I often avoid 1:1
>> associations because of this ambiguity anyway.
>
> It''s only when employing the method of constructing 1:1  
> relationships that you suggested that problems arise in both  
> modifying the relationship to a 1:many and bucking the Rails naming  
> convention causing the need to override the name of the foreign key  
> for the has_one association. What else do you need to convince you  
> that the method of using a foreign key as a primary key is  
> suboptimal? There''s nothing gained over using either the Rails  
> has_one and/or specifying the foreign key column as UNIQUE and  
> their are several detriments.
...there are several detriments. Dang.

--
Jason Perkins
jperkins@sneer.org

"As democracy is perfected, the office of president represents,
more and more closely, the inner soul of the people.  On some
great and glorious day the plain folks of the land will reach
their heart''s desire at last and the White House will be adorned
by a downright moron."        -  H.L. Mencken (1880 - 1956)

On Thu, Feb 09, 2006 at 02:10:25PM -0600, Jason Perkins
wrote:
> It''s only when employing the method of constructing 1:1
relationships
> that you suggested that problems arise in both modifying the  
> relationship to a 1:many and bucking the Rails naming convention  
> causing the need to override the name of the foreign key for the  
> has_one association. What else do you need to convince you that the  
> method of using a foreign key as a primary key is suboptimal?
There''s
> nothing gained over using either the Rails has_one and/or specifying  
> the foreign key column as UNIQUE and their are several detriments.
But this is my point - 1:1 relationships are sometimes special, and
they sometimes can''t be expanded to 1:n, because that would make no
sense.

For a highly simplied example, say you''ve got two kinds of orders -
domestic and international. You''ve got some shared fields - order id,
customer id, etc..., but maybe you also have some fields that diverge
dramatically.

One way to do this is to put your shared fields in an "orders" table,
with an id primary key, then make two other tables, domestic_orders
and intl_orders. The latter two tables each have a foreign key
relationship of their primary key to the central orders table, so any
given order will appear either in the domestic_orders table or the
intl_orders table, with a placeholder for the common data in the
orders table, identified by the same key.

In this case, the 1:1 relationships exist to get multiple tables to
share a primary key space, and this makes more sense to me than giving
them each their own primary key and doing the relation as a separate
foreign key. It also avoids an additional lookup, if you already have
the primary key of the order (especially if you''re using the standard
rails convention of addressing items by their primary key instead of
some other key).

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

On 2/9/06, Adam Fields <rails23049809@aquick.org>
wrote:
> On Thu, Feb 09, 2006 at 02:10:25PM -0600, Jason Perkins wrote:
> > It''s only when employing the method of constructing 1:1
relationships
> > that you suggested that problems arise in both modifying the
> > relationship to a 1:many and bucking the Rails naming convention
> > causing the need to override the name of the foreign key for the
> > has_one association. What else do you need to convince you that the
> > method of using a foreign key as a primary key is suboptimal?
There''s
> > nothing gained over using either the Rails has_one and/or specifying
> > the foreign key column as UNIQUE and their are several detriments.
>
> But this is my point - 1:1 relationships are sometimes special, and
> they sometimes can''t be expanded to 1:n, because that would make
no
> sense.
>
> For a highly simplied example, say you''ve got two kinds of orders
-
> domestic and international. You''ve got some shared fields - order
id,
> customer id, etc..., but maybe you also have some fields that diverge
> dramatically.
>
> One way to do this is to put your shared fields in an "orders"
table,
> with an id primary key, then make two other tables, domestic_orders
> and intl_orders. The latter two tables each have a foreign key
> relationship of their primary key to the central orders table, so any
> given order will appear either in the domestic_orders table or the
> intl_orders table, with a placeholder for the common data in the
> orders table, identified by the same key.
>
> In this case, the 1:1 relationships exist to get multiple tables to
> share a primary key space, and this makes more sense to me than giving
> them each their own primary key and doing the relation as a separate
> foreign key. It also avoids an additional lookup, if you already have
> the primary key of the order (especially if you''re using the
standard
> rails convention of addressing items by their primary key instead of
> some other key).
>
> --
>                                 - Adam
>
> ** Expert Technical Project and Business Management
> **** System Performance Analysis and Architecture
> ****** [ http://www.everylastounce.com ]
>
> [ http://www.aquick.org/blog ] ............ Blog
> [ http://www.adamfields.com/resume.html ].. Experience
> [ http://www.flickr.com/photos/fields ] ... Photos
> [ http://www.aquicki.com/wiki ].............Wiki
> [ http://del.icio.us/fields ] ............. Links
>
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
I understand what you''re getting at with this relationship, but
I''m
not sure it''s all that practical.  You said using a foreign key as the
primary key avoids an extra lookup - perhaps I''m misunderstanding, but
I think it simply avoids one measley integer column.  That hardly
seems worth the trouble of of fighting with the framework when you can
just do ''belongs_to :order'' and be done with it.  I''m
not a DB expert,
but is one integer column going to be a big deal?  I don''t think so.

In your example I''m not sure it''s the correct domain model
anyway,
because it''s very reasonable to have more than one shipment per order,
shipping different products in the order as they become available. 
That''s not the point of this whole discussion though of course. 
Anyway I simply don''t see any reason to go through the trouble of this
when, following Rails conventions, it takes one extra integer column
and one line of code.

Pat

On Thu, Feb 09, 2006 at 02:12:32PM -0700, Pat Maddox
wrote:
> I understand what you''re getting at with this relationship, but
I''m
> not sure it''s all that practical.  You said using a foreign key as
the
> primary key avoids an extra lookup - perhaps I''m misunderstanding,
but
> I think it simply avoids one measley integer column.  That hardly
> seems worth the trouble of of fighting with the framework when you can
> just do ''belongs_to :order'' and be done with it. 
I''m not a DB expert,
> but is one integer column going to be a big deal?  I don''t think
so.
> 
> In your example I''m not sure it''s the correct domain
model anyway,
> because it''s very reasonable to have more than one shipment per
order,
> shipping different products in the order as they become available. 
> That''s not the point of this whole discussion though of course. 
> Anyway I simply don''t see any reason to go through the trouble of
this
> when, following Rails conventions, it takes one extra integer column
> and one line of code.
First, I''m not talking about "shipments", per se, but one way
to model
order types. I''d assume that if you wanted to also model shipments,
then that would be the kind of thing that would potentially be
convertible from a has_one to a has_many, although I''m not sure what
you gain by not just treating them as a has_many to start with.

As I said - these kinds of 1:1 relationships are "is_a" rather than
"has_a".

Second, it''s not the extra integer column that''s the problem,
it''s the
lookup. By that, I mean - say you have order.id. If you want the
details for the domestic order that goes with it, you can use
domestic_order.id to reference it. If you''re using rails-ish url
schemes, you can do:

/domestic_orders/show/<order.id>

If you''re using a different primary key for the domestic orders, you
have to instantiate the domestic_order object first (even if it
happens automatically for you) and then look up the primary key.

Granted, in most cases, one extra lookup isn''t going to kill you. But
it could be significant if you''re doing high volume.

There are other ways to model this kind of a relationship, but this is
the cleanest one I''ve found from an ER perspective.

-- 
				- Adam

** Expert Technical Project and Business Management
**** System Performance Analysis and Architecture
****** [ http://www.everylastounce.com ]

[ http://www.aquick.org/blog ] ............ Blog
[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.flickr.com/photos/fields ] ... Photos
[ http://www.aquicki.com/wiki ].............Wiki
[ http://del.icio.us/fields ] ............. Links

Would anyone like to re-open this thread after the recent "vigorous"
discussion (http://discuss.joelonsoftware.com/default.asp?joel.3.309321.126)
over at Joel on Software? (note: original posting NOT written by Joel)

Why doesn''t rails take advantage of some basic database functionality
to
remove the need to explicitly define relationships and data integrity for
basic data types (e.g. use field definitions such as int(11), varchar(60) to
automagically create rails validations).  I know DHH and others are against
business logic in the database, but this seems to be lower level stuff than
that.

Since the Joel on Software (JOS) thread is long, I will post a couple of eye
openers here for discussion:

"People are saying you just can''t guess relationships. Well, go
figure out
how Class::DBI::Loader in Perl does it, or go read Randal Schwartz''s
February 2005 Linux Magazine column, where he does it before
Class::DBI::Loader learned how."

"And if you autogenerate field validation. Yes, developers will need to
override with special validations for passwords and other fields, but
that''s
a lame reason not to do it in the first place. (If you had said "not enough
time to implement," I''d have said, "well, of course, that
makes sense.)"

Where I stand (as a rails newbie) it seems that (1) relationship auto
discovery and (2) data integrity auto discovery would save a lot of typing
for most cases.  The automatic model could be overridden (as per table names
and primary keys) in the rare(?) situations where this auto discovery is
incorrect or fails.

I am very curious to see what the rails community here thinks of this.

Perhaps if there is no consensus to add this to the rails core, it could be
implemented as a plug-in.

On 2/9/06, Ian Harding <iharding@destinydata.com>
wrote:
>
> On 2/9/06, Jason Perkins <jperkins@sneer.org> wrote:
> >
> > On Feb 9, 2006, at 8:27 AM, Xavier Noria wrote:
> >
> > > On Feb 9, 2006, at 14:37, Peter De Berdt wrote:
> > >
> > >> On 09 Feb 2006, at 10:35, Xavier Noria wrote:
> > >>
>
> <snip>
>
> > >
> > > I believe the problem is technical, like not being able to guess
> > > the correct relationships in some cases or something like that.
> >
> > This is exactly the reason. It''s impossible to differentiate
a one-to-
> > many join that currently only has one corresponding row in the
''many''
> > table from a one-to-one join merely on table and database metadata.
> > You can know a join existing based on foreign key constraints, but
> > you can''t know the type of join. Which is why we specify it.
> >
> >
>
> Type of join?  Why not just punt and say if you have a foreign key
> defined on you, you belong to the other table, if you are referenced
> by the other table, you have many.  You will be right 99% of the time,
> and if you''re not, you just need to s/many/one/ .  This would be a
> boon to people who are migrating to rails.  I can still feel my
> tendons from the time I spent typing all my relationships into models.
> I would have loved to do
>
> ruby script/generate model-guess-relationships-please modelname tablename
>
> especially since I also had to set_table_name and set_primary_key and
> :foreign_key => foo for all of them.
>
> - Ian
>
> >
> > --
> > Jason Perkins
> > jperkins@sneer.org
> >
> > "The computer allows you to make mistakes
> > faster than any other invention, with the
> > possible exception of handguns and tequila."
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060221/b9e771f1/attachment-0001.html

On 2/21/06, Eden Brandeis <ebrandeis@gmail.com>
wrote:
> Would anyone like to re-open this thread after the recent
"vigorous"
> discussion
> (http://discuss.joelonsoftware.com/default.asp?joel.3.309321.126
> ) over at Joel on Software? (note: original posting NOT written by Joel)
Please, let''s not re-open this thread, especially because of that JoS
thread.  Anyways, this is David''s response to it.  What more needs to
be said?  If you truly desire it, implement it as a plugin.

----------------------

1) Foreign keys are not rich enough to represent most associations.
Not only can''t they distinguish between 1-1 and 1-M, but they
can''t
hold any customization. So there''s no way to specify has_many
:active_clients, :conditions => "active = 1".

And if you can''t represent the vast majority of all associations
through introspection, I''d much rather be explicit about the few that
could have been guessed for the sake of consistency and coherence.

Note that the reverse is the case for attributes. The vast majority of
all attributes accurately describe how we want them presented in the
code since type casting happens automatically. So we only have to
specify the exception.

--
Rick Olson
http://techno-weenie.net

Eden

    > Would anyone like to re-open this thread after the recent 
"vigorous"  discussion
    > (http://discuss.joelonsoftware.com/default.asp?joel.3.309321.126

Please read :
   "Six Ground Rules for ?Rails Sucks? Articles"
 
http://blog.codahale.com/2006/02/17/six-ground-rules-for-rails-sucks-articles/


Alain

As a side comment out of left field... We could get more DRY if we used an
object-first
ORM instead of ActiveRecord. So, if you could get Og ported to rails, you could
define
your model in Ruby and let Og figure out the db (with all the caveats that come
with that).

b

Eden Brandeis wrote:
> Would anyone like to re-open this thread after the recent
"vigorous"
> discussion 
> (http://discuss.joelonsoftware.com/default.asp?joel.3.309321.126 
> <http://discuss.joelonsoftware.com/default.asp?joel.3.309321.126>)
over
> at Joel on Software? (note: original posting NOT written by Joel)
> 
> Why doesn''t rails take advantage of some basic database
functionality to
> remove the need to explicitly define relationships and data integrity 
> for basic data types ( e.g. use field definitions such as int(11), 
> varchar(60) to automagically create rails validations).  I know DHH and 
> others are against business logic in the database, but this seems to be 
> lower level stuff than that.
> 
> Since the Joel on Software (JOS) thread is long, I will post a couple of 
> eye openers here for discussion:
> 
> "People are saying you just can''t guess relationships. Well,
go figure
> out how Class::DBI::Loader in Perl does it, or go read Randal
Schwartz''s
> February 2005 Linux Magazine column, where he does it before 
> Class::DBI::Loader learned how."
> 
> "And if you autogenerate field validation. Yes, developers will need
to
> override with special validations for passwords and other fields, but 
> that''s a lame reason not to do it in the first place. (If you had
said
> "not enough time to implement," I''d have said,
"well, of course, that
> makes sense.)"
> 
> Where I stand (as a rails newbie) it seems that (1) relationship auto 
> discovery and (2) data integrity auto discovery would save a lot of 
> typing for most cases.  The automatic model could be overridden (as per 
> table names and primary keys) in the rare(?) situations where this auto 
> discovery is incorrect or fails.
> 
> I am very curious to see what the rails community here thinks of this. 
> 
> Perhaps if there is no consensus to add this to the rails core, it could 
> be implemented as a plug-in.
> 
> On 2/9/06, *Ian Harding* <iharding@destinydata.com 
> <mailto:iharding@destinydata.com>> wrote:
> 
>     On 2/9/06, Jason Perkins <jperkins@sneer.org
>     <mailto:jperkins@sneer.org>> wrote:
>      >
>      > On Feb 9, 2006, at 8:27 AM, Xavier Noria wrote:
>      >
>      > > On Feb 9, 2006, at 14:37, Peter De Berdt wrote:
>      > >
>      > >> On 09 Feb 2006, at 10:35, Xavier Noria wrote:
>      > >>
> 
>     <snip>
> 
>      > >
>      > > I believe the problem is technical, like not being able to
guess
>      > > the correct relationships in some cases or something like
that.
>      >
>      > This is exactly the reason. It''s impossible to
differentiate a
>     one-to-
>      > many join that currently only has one corresponding row in the
''many''
>      > table from a one-to-one join merely on table and database
metadata.
>      > You can know a join existing based on foreign key constraints,
but
>      > you can''t know the type of join. Which is why we specify
it.
>      >
>      >
> 
>     Type of join?  Why not just punt and say if you have a foreign key
>     defined on you, you belong to the other table, if you are referenced
>     by the other table, you have many.  You will be right 99% of the time,
>     and if you''re not, you just need to s/many/one/ .  This would
be a
>     boon to people who are migrating to rails.  I can still feel my
>     tendons from the time I spent typing all my relationships into models.
>     I would have loved to do
> 
>     ruby script/generate model-guess-relationships-please modelname
>     tablename
> 
>     especially since I also had to set_table_name and set_primary_key and
>     :foreign_key => foo for all of them.
> 
>     - Ian
> 
>      >
>      > --
>      > Jason Perkins
>      > jperkins@sneer.org <mailto:jperkins@sneer.org>
>      >
>      > "The computer allows you to make mistakes
>      > faster than any other invention, with the
>      > possible exception of handguns and tequila."
>      >
>      >
>      > _______________________________________________
>      > Rails mailing list
>      > Rails@lists.rubyonrails.org
<mailto:Rails@lists.rubyonrails.org>
>      > http://lists.rubyonrails.org/mailman/listinfo/rails
>      >
>     _______________________________________________
>     Rails mailing list
>     Rails@lists.rubyonrails.org <mailto:Rails@lists.rubyonrails.org>
>     http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails