[Catalyst] Constants that refer to rows in a lookup table.

mla maurice.aubrey at gmail.com
Wed May 23 01:57:15 GMT 2007


Bill Moseley wrote:
> On Tue, May 22, 2007 at 04:36:06PM -0700, mla wrote:
>> The PKs seem arbitrary in this case. I don't know if they
>> should be referenced at all. I'd tend to use a subselect...
>>
>>   UPDATE carts SET cart_status_id = (
>>     SELECT id FROM cart_status WHERE name = 'Completed'
>>   );
>>
>> But in a more ORM way, maybe something like
>>
>>   my $status = Cart::Status->fetch(name => 'Pending');
> 
> Ya, but "Pending" is the description of the status, not the actual
> abstract status.
> 
>     Your cart is: [% cart.cart_status.name | html %]
> 
> Manager makes decision:
> 
>     update cart_status set name = 'Not Completed' where name = 'Pending';
> 
> This still works:
> 
>     Your cart is: [% cart.cart_status.name | html %]
> 
> 
> But everywhere you did this is now broken:
> 
>   my $status = Cart::Status->fetch(name => 'Pending');
> 
> 
> 
> But if I did this everywhere instead:
> 
>   my $status = Cart::Status->fetch(name => cart_pending_status );
> 
> I'd still be on the beach.
> 
> 
> But the question was how do I represent constants best?

Consider this change...

   create table cart_status (
     id      SERIAL PRIMARY KEY,
     status  text UNIQUE NOT NULL
     name    text NOT NULL,
     active  boolean NOT NULL DEFAULT TRUE
   );

   -- trust the sequence, Luke
   insert into cart_status (status, name) values ('pending', 'Pending');
   insert into cart_status (status, name) values ('completed', 'Completed');

Now you use id in your FKs, but reference the status row by
the 'status' column, which is effectively the constant name.

The id serves just as a synthetic key, but the unique constraint
on status is the real pk.

Now you can change name whenever you want and it affects nothing.
The name and status then won't necessarily match, but that's the
same as using a constant name.

That's the approach I usually take.

If you want to stick with actual, external constants referencing
synthetic keys, then maybe define the constants first and don't
rely on a serial column to populate the rows. Then if you're in
a dev environment with a different sequence state or something,
the constants won't be off.

Maurice



More information about the Catalyst mailing list