This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I'm starting a new project and I see in the LS help docs that "KeyTable" is the default and recommended strategy for PK generation. I'm not familiar with the "KeyTable" concept, is it something that you guys created specifically for LS? Or is it a popular and well-known ORM design pattern that other ORMs/DataAccess projects are also using and I just haven't heard of before? I normally use SQL Server int identity autoincrement columns for my PK's on all tables, but I'm curious what the real-world benefits might be of the KeyTable approach, but I haven't heard of it before so I'm a little shy ;) |
|
|
|
|
It's pretty well-known and we can't claim credit for it! You might know it as "hi-lo" rather than "key table." Here's Martin Fowler's account (the link goes to some Java implementation stuff but it gives a bit more of an explanation if you scroll up a few pages): http://books.google.co.nz/books?id=FyWZt5DdvFkC&pg=PT249&lpg=PT249&dq=fowler+key+table&source=bl&ots=eFwqYvWt6D&sig=vyz5_jbVR8sLLh7M82XxDiAmBjA&hl=en&ei=wwqMTefeEoGusAPa1Kz0CA&sa=X&oi=book_result&ct=result&resnum=2&ved=0CBsQ6AEwAQ#v=onepage&q=fowler%20key%20table&f=false And David Hayden has a nice post describing what KeyTable does, how it does it and what the benefits are. The second paragraph of this post may also shed some light. |
|
|
|
|
Thanks for the links. One thing that seems apparent is that using the KeyTable approach will make it much more difficult to insert rows into tables using SQL Scripts, for setting up database test data, for example. Since we can't just insert new rows and let SQL server assign the identity, any data inserts we want to do via sql scripts will have to lookup the next PK IDs from the KeyTable, insert the rows with those keys, then update the KeyTable. Is my assumption correct? It seems this would make data initialization scripts (to setup specific data scenarios for unit and integration tests) much more complicated to write? |
|
|
|
|
No, generally it is easier because the keytable doesnt need to be sequential and you can allocate yourself ranges as required - also particularly for test data where you want to use specific identifiers you dont have to worry about turning off the identity insert and then turning it back on again as part of the script. For test data I would just insert the rows needed and then set the value of the keytable entry to be a number which is higher than your test data, e.g. lets say you are inserting a couple of hundred rows of test data then just start with a keytable value of 1000. If you are dealing with data insertion at a later point, lets say the keytable is at 1029010 and you need to insert 45 rows. Then just update the keytable to reflect the work you will be doing (e.g. update it to 1029060) and you can then insert your new rows using the allocated range you have assigned.
Jeremy |
|
|
|
