Hi Ken, I am almost certain this is a convention rather than a technical limitation. Think Ruby on Rails - convention is a great tool for minimizing the overhead of configuration (one less XML node!).
Best Wishes, Peter -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken Dunnington Sent: Friday, March 03, 2006 2:29 PM To: [email protected] Subject: Re: [Reactor For CF] Primary Keys Hi Peter, thanks for the response. I should have specified that my keys are all integers, and that the Address table already has an addressID as the pk, which is why relating from Address.user to User.userID didn't work. What I'm curious about is if there is a more specific reason for requiring the use of primary keys in Reactor's object definitions. For example, if you were to create a hasOne relationship and relate from="user" to="userID", there is already a provision in the ORM to throw an error on ambiguous records, so it's not to ensure uniqueness, what is it for? Technically, the statement "SELECT * FROM Address, User WHERE Address.user = User.userID" would return the same records as "SELECT * FROM Address, User WHERE User.address = Address.addressID" assuming that the data was entered properly in the first place. I agree that the convention is to link to a PK and that's usually what I do. I'm wondering if this restriction in the framework has to do with elements of the DB world that are beyond me, which wouldn't be too difficult since I don't do much beyond simple table design and normalization. :) Incidentally, I changed the object XML and Reactor did the rest - I didn't have to change any of my code, which was very cool. On 3/3/06, Peter Bell <[EMAIL PROTECTED]> wrote: > > 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 > > > > -----Original Message----- > 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 > > 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? > > 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/ -- > Reactor for ColdFusion Mailing List -- [email protected] -- > Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Quidquid latine dictum sit, altum sonatur. - Whatever is said in Latin sounds profound. -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
