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/
