RE: [Reactor For CF] Primary Keys

[Peter Bell]

Title: Message

Hi Chris,   Interesting to see real data testing ints vs. strings as primary keys. The only time I think the problem might be worse is if you have a lot of intricate joins. We have an ORM that makes it easy to handle inheritance, composition and aggregation and it allows you to tune the model you use (table per class, concrete class or base class for inheritance), but if someone gets carried away with their object model they can end up with queries including multiple joins and I'm guessing if all of those joins used strings it would exacerbate any performance implications of using strings. How much of a deal that would be, I couldn't say (but will try to test!).    Best Wishes, Peter   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Blackwell Sent: Saturday, March 04, 2006 3:29 PM To: reactor@doughughes.net Subject: RE: [Reactor For CF] Primary Keys Hi All,   ARF supports uuid’s as a primary key and I often use them in my applications, as I find them easy to work with. Having the ability to to use uuid’s in Reactor would also allow db’s like Oracle which rely on sequences instead of an autonumber field to be easily supported by handling primary key generation in Reactor.   In terms of performance difference between integers and strings as primary keys, yes there is a difference.  I’ve previously tested performance of integers vs uuid’s on MySQL with large datasets (500,000 records) and the difference was measured in 1/10th’s of milliseconds, so I’ve never really worried about it.  However if your application and dataset is so big or under such heavy load that the small performance hit of using uuid’s as a key is a problem, then you probably shouldn’t be using Reactor either.   One draw back of uuid’s is the space taken up storing them, but storage is cheap J   Cheers, Chris   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peter Bell Sent: 03 March 2006 19:06 To: reactor@doughughes.net Subject: RE: [Reactor For CF] Primary Keys   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: reactor@doughughes.net 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 -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- Reactor for ColdFusion Mailing List -- reactor@doughughes.net -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/