Hello
Ken,
A
couple of general comments.
Many
people these days do use an %Entity_Name%ID field that is some kind of integer
(in MSSQL, typically smallint, int or bigint) as a primary key for each table.
In my system, every table has a %TableName% ID that is used for uniquely
identifying records and for foreign key relationships (User table has UserID -
Address table has AddressUserID - nice and easy and
automatable).
There
is no inherent reason why an ORM couldn't be written to join on any field. You'd
have to add the field name to the configuration file (unless you explicitly
defined the FK in the db and someone wrote a script to pull that out) and it
would have to get the datra type of the field to figure out whether or not to
wrap quotes around it, but it is straightforward and something I have considered
from time to time. However, as your application size increases, you typically
get better performance searching on integers than strings, so using an ID field
is a general best practice.
Also,
because this is such a widespread practice - especially for green field
developments, it is easier for other developers to get up to speed with your
data dictionary if you follow the convention.
So, I
think it is a good practice to follow for performance and broader readability,
but I'm sure Doug could add it if he had to (e.g. client demand and a nice big
check!). In the end I decided not to add the feature to my system only because
it mada a lot of the code a little less clean and more complex for no compelling
benefit. I have many clients who key off of a string (product sku or username)
and usually just write a little import script to assign the appropriate integer
keys on import. I know it's wasting space, but disks are cheap and integers are
small!
Anyone
thing it would be a good idea for an ORM to natively support string foreign
keys?
Best
Wishes,
Peter
Peter
-- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/-----Original Message-----While designing the user management part of my application, I decided to link my user table and address table by way of a 'user' field in the address table. However, 'user' wasn't the primary key, so this broke things when I tried to call getAddressRecord() on my user object. Was this just a bad design decision on my part, or a limitation of ORM frameworks? I know that Reactor needs the primary key(s) to make the associated records work, and I'm not suggesting there's anything wrong with that, I'm just curious if there is a particular purpose for this limitation. Is there a way to use 'relate from="x" to="y"' on non-primary keys? Or is that something best left up to the gateway to handle?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken Dunnington
Sent: Friday, March 03, 2006 11:26 AM
To: [email protected]
Subject: [Reactor For CF] Primary Keys
PS - I've already switched my tables around to work properly, so I'm just asking out of curiosity. :)
-- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
