Rails core - Feb 2006 - Composite primary key support in ActiveRecord?

Hi,

As I understand, composite primary keys aren''t supported in  
ActiveRecord yet.  May I ask if there are plans for this feature?   
Better yet, if this feature is under development, how''s the progress  
going?

I''m not trying to use legacy databases.  I tend to think that using  
multiple integer column id''s (composite primary key) are often  
natural way to implement one-to-many relationships, for things like  
"forum replies".  For example, ID for the third comment of forum  
topic #87 should be (87, 3) not (87, 1074), format being (topic_id,  
reply_id).

Currently, I''m trying to work around this by using
''TopicReply.find
(:fist, conditions => ["topic_id=? AND id=?", 87, 3]''
instead of
''TopicReply.find(1074)''.  However, I haven''t figured
out yet how to
save such objects..

Any comment will be greatly appreciated.

Thanks,

daesan

On 2/18/06, Dae San Hwang <daesan@gmail.com>
wrote:
> Hi,
>
> As I understand, composite primary keys aren''t supported in
> ActiveRecord yet.  May I ask if there are plans for this feature?
> Better yet, if this feature is under development, how''s the
progress
> going?
There is currently no work underway to support composite keys with
active record.  At least in rails itself.  There might well be some
plugins under development.
> I''m not trying to use legacy databases.
Then stick with surrogate ids
> I tend to think that using
> multiple integer column id''s (composite primary key) are often
> natural way to implement one-to-many relationships, for things like
> "forum replies".  For example, ID for the third comment of forum
> topic #87 should be (87, 3) not (87, 1074), format being (topic_id,
> reply_id).
You shouldn''t be thinking about the IDs,  they''re only there
for the
sake of persistance,  consider them the logical equivalent of an
address in memory.  It doesn''t matter what the number is,  just that
there *is* a number.




--
Cheers

Koz

Thank you for the reply!

I especially liked your analogy of ID to memory address though I''m  
not yet 100% convinced.  Following are my reasonings behind the  
preference for multiple integer column id''s:

1. I tend to think URL as a part of the user interface.  If the user  
look at (usually simple and structured in rails application) URL,  
wouldn''t she be confused by the seemingly random and very large  
number assigned to her reply post?  Topic''s id #87 corresponds to the  
87th-ness nature of the topic posted in the forum.  Reply id #3 would  
correspond to the 3rd-ness nature of the reply to the given topic,  
reply id #1074 would not.  I know, I know, I might sound like someone  
who is suffering ocd..  ;)

2. This one is a bit more practical issue.  Let''s say I''m
building a
public forum hosting service.  (It is indeed something I have in  
mind.)  At some point, my site hosts 10,000 forums, each having its  
own forum_id.  Let''s say each forum has average of 10,000 topics,  
each having its own topic_id.  And each topic has average of 100  
replies, each having its own reply_id.  If I have to use a flat  
integer id space, there will be replies with id like #10,000,000,000.  
(10,000 * 10,000 * 100)  Now, 32-bit unsigned integer can''t handle it  
since its max value would 4,294,967,295.  I could just use 64-bit  
unsigned integer, I guess, but it does take more spaces and what if  
my site becomes even more successful to the extent even 64-bit  
integer will max out?

In some ways, id''s are like memory addresses, but no user ever get to  
see the memory address values..  Are there other reasons against the  
use of multiple integer column id''s other than the fact that  
ActiveRecord doesn''t support it?

Thanks,

daesan


On Feb 19, 2006, at 2:57 AM, Michael Koziarski wrote:
> On 2/18/06, Dae San Hwang <daesan@gmail.com> wrote:
>> Hi,
>>
>> As I understand, composite primary keys aren''t supported in
>> ActiveRecord yet.  May I ask if there are plans for this feature?
>> Better yet, if this feature is under development, how''s the
progress
>> going?
>
> There is currently no work underway to support composite keys with
> active record.  At least in rails itself.  There might well be some
> plugins under development.
>
>> I''m not trying to use legacy databases.
>
> Then stick with surrogate ids
>
>> I tend to think that using
>> multiple integer column id''s (composite primary key) are often
>> natural way to implement one-to-many relationships, for things like
>> "forum replies".  For example, ID for the third comment of
forum
>> topic #87 should be (87, 3) not (87, 1074), format being (topic_id,
>> reply_id).
>
> You shouldn''t be thinking about the IDs,  they''re only
there for the
> sake of persistance,  consider them the logical equivalent of an
> address in memory.  It doesn''t matter what the number is,  just
that
> there *is* a number.
>
>
>
>
> --
> Cheers
>
> Koz
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core

On 2/18/06, Dae San Hwang <daesan@gmail.com>
wrote:
> Thank you for the reply!
>
> I especially liked your analogy of ID to memory address though I''m
> not yet 100% convinced.  Following are my reasonings behind the
> preference for multiple integer column id''s:
>
> 1. I tend to think URL as a part of the user interface.  If the user
> look at (usually simple and structured in rails application) URL,
> wouldn''t she be confused by the seemingly random and very large
> number assigned to her reply post?  Topic''s id #87 corresponds to
the
> 87th-ness nature of the topic posted in the forum.  Reply id #3 would
> correspond to the 3rd-ness nature of the reply to the given topic,
> reply id #1074 would not.  I know, I know, I might sound like someone
> who is suffering ocd..  ;)
>
> 2. This one is a bit more practical issue.  Let''s say I''m
building a
> public forum hosting service.  (It is indeed something I have in
> mind.)  At some point, my site hosts 10,000 forums, each having its
> own forum_id.  Let''s say each forum has average of 10,000 topics,
> each having its own topic_id.  And each topic has average of 100
> replies, each having its own reply_id.  If I have to use a flat
> integer id space, there will be replies with id like #10,000,000,000.
> (10,000 * 10,000 * 100)  Now, 32-bit unsigned integer can''t handle
it
> since its max value would 4,294,967,295.  I could just use 64-bit
> unsigned integer, I guess, but it does take more spaces and what if
> my site becomes even more successful to the extent even 64-bit
> integer will max out?
>
> In some ways, id''s are like memory addresses, but no user ever get
to
> see the memory address values..  Are there other reasons against the
> use of multiple integer column id''s other than the fact that
> ActiveRecord doesn''t support it?
>
> Thanks,
>
> daesan
>
>
> On Feb 19, 2006, at 2:57 AM, Michael Koziarski wrote:
>
> > On 2/18/06, Dae San Hwang <daesan@gmail.com> wrote:
> >> Hi,
> >>
> >> As I understand, composite primary keys aren''t supported
in
> >> ActiveRecord yet.  May I ask if there are plans for this feature?
> >> Better yet, if this feature is under development, how''s
the progress
> >> going?
> >
> > There is currently no work underway to support composite keys with
> > active record.  At least in rails itself.  There might well be some
> > plugins under development.
> >
> >> I''m not trying to use legacy databases.
> >
> > Then stick with surrogate ids
> >
> >> I tend to think that using
> >> multiple integer column id''s (composite primary key) are
often
> >> natural way to implement one-to-many relationships, for things
like
> >> "forum replies".  For example, ID for the third comment
of forum
> >> topic #87 should be (87, 3) not (87, 1074), format being
(topic_id,
> >> reply_id).
> >
> > You shouldn''t be thinking about the IDs,  they''re
only there for the
> > sake of persistance,  consider them the logical equivalent of an
> > address in memory.  It doesn''t matter what the number is, 
just that
> > there *is* a number.
> >
> >
> >
> >
> > --
> > Cheers
> >
> > Koz
map.connect ''topic/:topic/:reply''

/topic/87/3

@reply = Reply.find_by_topic_id_and_id(params[:topic], params[:reply])

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

Daesan,
> > 2. This one is a bit more practical issue.  Let''s say
I''m building a
> > public forum hosting service.  (It is indeed something I have in
> > mind.)  At some point, my site hosts 10,000 forums, each having its
> > own forum_id.  Let''s say each forum has average of 10,000
topics,
> > each having its own topic_id.  And each topic has average of 100
> > replies, each having its own reply_id.  If I have to use a flat
> > integer id space, there will be replies with id like #10,000,000,000.
> > (10,000 * 10,000 * 100)  Now, 32-bit unsigned integer can''t
handle it
> > since its max value would 4,294,967,295.  I could just use 64-bit
> > unsigned integer, I guess, but it does take more spaces and what if
> > my site becomes even more successful to the extent even 64-bit
> > integer will max out?
You''d really run 10,000 forums out of one database with shared tables?
The argument is for the sake of argument. If you''re that successful,
you wouldn''t make that choice.

On Feb 18, 2006, at 10:56 AM, Dae San Hwang wrote:
> Thank you for the reply!
>
> I especially liked your analogy of ID to memory address though I''m
not
> yet 100% convinced.  Following are my reasonings behind the preference 
> for multiple integer column id''s:
>
> 1. I tend to think URL as a part of the user interface.  If the user 
> look at (usually simple and structured in rails application) URL, 
> wouldn''t she be confused by the seemingly random and very large
number
> assigned to her reply post?  Topic''s id #87 corresponds to the 
> 87th-ness nature of the topic posted in the forum.  Reply id #3 would 
> correspond to the 3rd-ness nature of the reply to the given topic, 
> reply id #1074 would not.  I know, I know, I might sound like someone 
> who is suffering ocd..  ;)
>
> 2. This one is a bit more practical issue.  Let''s say I''m
building a
> public forum hosting service.  (It is indeed something I have in 
> mind.)  At some point, my site hosts 10,000 forums, each having its 
> own forum_id.  Let''s say each forum has average of 10,000 topics,
each
> having its own topic_id.  And each topic has average of 100 replies, 
> each having its own reply_id.  If I have to use a flat integer id 
> space, there will be replies with id like #10,000,000,000. (10,000 * 
> 10,000 * 100)  Now, 32-bit unsigned integer can''t handle it since
its
> max value would 4,294,967,295.  I could just use 64-bit unsigned 
> integer, I guess, but it does take more spaces and what if my site 
> becomes even more successful to the extent even 64-bit integer will 
> max out?
I don''t think this is going to be an issue.  I don''t think
you''re going
to accumulate over 9 quintillion posts in one database in the lifetime 
of the forum, you''d likely partition well before hitting this limit.  
(I''m assuming postgresql''s bigserial here for the 9
quintillion value,
i''m not familiar with other dbs).

You might check out acts as tree or acts as nested set (or create 
something along those lines) which should allow you to structure it the 
way you want without requiring composite keys.

-Scott

> 1. I tend to think URL as a part of the user interface.  If the user
> look at (usually simple and structured in rails application) URL,
> wouldn''t she be confused by the seemingly random and very large
> number assigned to her reply post?  Topic''s id #87 corresponds to
the
> 87th-ness nature of the topic posted in the forum.  Reply id #3 would
> correspond to the 3rd-ness nature of the reply to the given topic,
> reply id #1074 would not.  I know, I know, I might sound like someone
> who is suffering ocd..  ;)
Rick''s reply covers that,  use routes.   Additionally, you may want to
consider adding another ''index'' column rather than using the
IDs like
that.

> 2. This one is a bit more practical issue.  Let''s say I''m
building a
> public forum hosting service.  (It is indeed something I have in
> mind.)  At some point, my site hosts 10,000 forums, each having its
> own forum_id.  Let''s say each forum has average of 10,000 topics,
> each having its own topic_id.  And each topic has average of 100
> replies, each having its own reply_id.  If I have to use a flat
> integer id space, there will be replies with id like #10,000,000,000.
> (10,000 * 10,000 * 100)  Now, 32-bit unsigned integer can''t handle
it
> since its max value would 4,294,967,295.  I could just use 64-bit
> unsigned integer, I guess, but it does take more spaces and what if
> my site becomes even more successful to the extent even 64-bit
> integer will max out?
If you''re storing forum posts, you''re looking at a lot of
data,  like
say, 1k per row.   If you''ve maxed out a 64 bit integer,
you''ve got
exabytes - yottabytes of storage.   No one has that much storage,  
Not  google, or high end scientific supercomputers.

You''ll be fine.
> In some ways, id''s are like memory addresses, but no user ever get
to
> see the memory address values..  Are there other reasons against the
> use of multiple integer column id''s other than the fact that
> ActiveRecord doesn''t support it?
Look at it another way,  if you can live without it,  why try to use it?



--
Cheers

Koz

On Feb 19, 2006, at 4:12 AM, Kevin Clark wrote:
> You''d really run 10,000 forums out of one database with shared
tables?
> The argument is for the sake of argument. If you''re that
successful,
> you wouldn''t make that choice.
My point was really that usage of surrogate id''s can quickly become a  
problem when the data model is nested like that.  (id''s at the end  
node will increase exponentially.)

daesan

_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

I tend to agree with Dae.

It doesn''t matter if my app will hit the 10,000,000,000 id number.
It''s a matter of doing "the right thing". Say I have an
Invoice and
InvoiceLines. I REALLY want my invoice lines to be structurally (I
mean in the DB schema) dependent to the Invoice. I think (and all my
DB teachers think the same) that it should exist a VERY, VERY good
reason for not doing it like that. And the only reason I''ve ever come
across is when I have a really deep dependency structure. I mean more
than 3 or 4 levels. Then, yes. I eventually create a one-to-one
mapping using a unique id to reference a complex composed key.

I know that supporting composite keys is not a MUST, but it''s a SHOULD.

I understand ActiveRecord doesn''t support it now in favor of
simpleness, but aventually it should have that option.

Diego

2006/2/18, Dae San Hwang <daesan@gmail.com>:
>
> On Feb 19, 2006, at 4:12 AM, Kevin Clark wrote:
>
>
> You''d really run 10,000 forums out of one database with shared
tables?
>
> The argument is for the sake of argument. If you''re that
successful,
>
> you wouldn''t make that choice.
> My point was really that usage of surrogate id''s can quickly
become a
> problem when the data model is nested like that.  (id''s at the end
node will
> increase exponentially.)
>
> daesan
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core
>
>
>

> I know that supporting composite keys is not a MUST, but it''s a
SHOULD.
Your invoice lines will have an invoice_id relating them to the
invoice,  it doesn''t get much more related than that.   If
you''re
seeing the ids as anything other than serialised object-identity, then
you''re over using.

The very very very good reason not to support composite keys is that
it doesn''t give any tangible benefits over single, unique IDs.  Even
less so with an ORM like AR.  If you want, you can add your own unique
constraints.
> I understand ActiveRecord doesn''t support it now in favor of
> simpleness, but aventually it should have that option.
It won''t, for the same reasons we don''t support all the other
DBA
centric options.  The costs outweight the benefits.

If you want to work this way, the soon-to-be-real rbatis persistence
is your best option.


--
Cheers

Koz

On Feb 18, 2006, at 12:28 PM, Dae San Hwang wrote:
> On Feb 19, 2006, at 4:12 AM, Kevin Clark wrote:
>
>> You''d really run 10,000 forums out of one database with shared
>> tables?
>> The argument is for the sake of argument. If you''re that
successful,
>> you wouldn''t make that choice.
>
> My point was really that usage of surrogate id''s can quickly
become
> a problem when the data model is nested like that.  (id''s at the  
> end node will increase exponentially.)
FWIW, Basecamp has Accounts, each of which has Projects, each of  
which has Posts, each of which has Comments.

Basecamp has been running fine for two years, and we are no where  
_near_ the 32-bit id limit for comment ids. If it becomes a problem  
(years from now) we''ll deal with it then, but remember: "premature
optimization is the root of all evil."

- Jamis

2006/2/18, Michael Koziarski
<michael@koziarski.com>:
> > I know that supporting composite keys is not a MUST, but it''s
a SHOULD.
>
> > I understand ActiveRecord doesn''t support it now in favor of
> > simpleness, but aventually it should have that option.
>
> It won''t, for the same reasons we don''t support all the
other DBA
> centric options.  The costs outweight the benefits.
Ok. that''s all I need to know. I can live with that. AR is too good to
not use it because of that.
>
> If you want to work this way, the soon-to-be-real rbatis persistence
> is your best option.
I don''t know rbatis. I suppose I''ll know soon. :-D

Cheers,
diegal

Thank you Koz and others who replied to my email.

I''m now convinced that I don''t really need multiple integer
column
id''s, or at least don''t really need to worry about them yet. 
I guess
it was another small relic in myself of favoring theoretic purity  
over pragmatism. ;)

Thanks again,

daesan


On Feb 19, 2006, at 4:25 AM, Michael Koziarski wrote:
> If you''re storing forum posts, you''re looking at a lot of
data,  like
> say, 1k per row.   If you''ve maxed out a 64 bit integer,
you''ve got
> exabytes - yottabytes of storage.   No one has that much storage,
> Not  google, or high end scientific supercomputers.
>
> You''ll be fine.


_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core

On Feb 18, 2006, at 3:30 PM, Michael Koziarski wrote:
>> I know that supporting composite keys is not a MUST, but it''s
a
>> SHOULD.
>
> Your invoice lines will have an invoice_id relating them to the
> invoice,  it doesn''t get much more related than that.   If
you''re
> seeing the ids as anything other than serialised object-identity, then
> you''re over using.
>
> The very very very good reason not to support composite keys is that
> it doesn''t give any tangible benefits over single, unique IDs. 
Even
> less so with an ORM like AR.  If you want, you can add your own unique
> constraints.
Unfortunately, it''s beyond the control of poor saps like me who are  
trying to integrate with legacy databases old enough to graduate from  
elementary school.  (c:  All the newer tables here fortunately have  
single PKs, but the older ones...not so much.  Mostly we''re just  
reading, but occasionally we will want to save some composite PK rows.
>> I understand ActiveRecord doesn''t support it now in favor of
>> simpleness, but aventually it should have that option.
>
> It won''t, for the same reasons we don''t support all the
other DBA
> centric options.  The costs outweight the benefits.
The cost being a broad, ugly increase in AR code complexity?  Would a  
patch ever be accepted for it, or is the ripple effect of composite  
keys just too big?

OTOH, does anyone have advice for implementing this as a plugin?   
Worst case scenario, I can just implement some custom helper methods  
per composite key table, to save the data, but I''d prefer a more  
integrated solution.  The less ad hoc the better.

John

-- 
John R. Sheets
http://umber.sourceforge.net
http://writersforge.sourceforge.net

On Sun, Feb 19, 2006 at 02:25:22AM +0900, Dae San Hwang
wrote:
> Hi,
> 
> As I understand, composite primary keys aren''t supported in
ActiveRecord yet.  May I ask if there are plans for this feature?
> Better yet, if this feature is under development, how''s the
progress going?
> 
> I''m not trying to use legacy databases.  I tend to think that
using multiple integer column id''s (composite primary key) are often
> natural way to implement one-to-many relationships, for things like
"forum replies".  For example, ID for the third comment of forum
> topic #87 should be (87, 3) not (87, 1074), format being (topic_id,
reply_id).
> 
> Currently, I''m trying to work around this by using
''TopicReply.find(:fist, conditions => ["topic_id=? AND
id=?", 87, 3]'' instead of
> ''TopicReply.find(1074)''.  However, I haven''t
figured out yet how to save such objects..
> 
> Any comment will be greatly appreciated.
> 
> Thanks,
> 
> daesan
> _______________________________________________
> Rails-core mailing list
> Rails-core@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails-core
I hear that AR lacks support for composite keys on nearly a daily basis now. 
This
always strikes me at odds with reality as we utilize composite foreign keys on a
daily basis in many of our applications.  The only sensible place I''ve
found to
utilize composite foreign keys in a relation exists in join tables, which
generally
are not (but can easily be) mapped to an AR model for finds.  It''s
unlikely that
for Inserts on the TopicReply model you would not already have a Topic or Reply 
instance available to use the common @topic.replies << @reply or 
@topic.create_reply(Reply.create(params[:reply])).  The composite foreign key
is validated on save from both the @topic.replies << @reply and on the DB
itself (if supported).
On one-to-many, the need for composite keys simply doesn''t exist, and
many methodologies
exist to work around the link and large integer id issue, routes being my 
preferred way to implement the pretty links.
-- 
TJ Vanderpoel GCIA,GCIH
tj@rapidreporting.com


_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core