jwcolby
jwcolby at colbyconsulting.com
Thu Jun 25 13:50:08 CDT 2009
> Farewell surrogate keys?
I think not.
My primary reason for surrogate keys is simply that using an ever increasing number of fields as the
FOREIGN KEYS down into child tables just sucks. It sucks from an implementation perspective and it
sucks from a speed / storage size issue.
Your data in a great great grandchild table can be dwarfed by the FK of the parent record.
And try refactoring the tables when the business rules change and add a field way up the chain to
the PK of the great-great-grandparent table. You will be rethinking natural keys in a hurry.
John W. Colby
www.ColbyConsulting.com
Shamil Salakhetdinov wrote:
> Hi All,
>
> Have a look:
>
> http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Cont
> inues.aspx
>
> Farewell surrogate keys?
>
> Thank you.
>
> --
> Shamil
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Thursday, June 25, 2009 3:45 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Primary keys and entry points (was: Learning .Net --
> PHP Instead?)
>
> Hi Shamil
>
> As Jim tells, if you can't guarantee that your object will live at one
> location only and can/may be mixed with similar objects from elsewhere, use
> a Guid to identify it. As I'm sure you know, in Access this has been
> available since A97 (or A95?) where replication was introduced.
>
> A reason to not use a Guid is ressources. It takes a little more space and -
> random as it is by nature - you will most often need other keys for sorting,
> a timestamp for example.
> For one project I mixed this. Simplified, a Guid was used for the Company
> table, while all other tables in some way related to the Company table and
> could use a normal Autonumber.
> In another project I (again simplified) used a compound key for the Company
> table (or the the object identity) with one field to identify to location
> and one field with an Autonumber. If this is what you have in mind, you are
> of course right, that the single Id autonumber must be given up.
>
> /gustav
>
>
>>>> shamil at smsconsulting.spb.ru 25-06-2009 01:10 >>>
> Hi Gustav,
>
> Yes, "the single exit point is not a B/W discussion" (as most of the others
> we keep under this thread) - did I say I'd not use the same construct you
> presented in your posting? - in fact I mean I can use both depending on how
> I will come to my solution/what case I will program first...
>
> As for "PK great debate" - I'm now just guessing that ADO.NET Entity
> Framework could break one of the last and most "rigid" of my habits to use
> AutoNumber/Identity PK for all and every tables, and not only for lookup
> tables but also for relation tables: using of AutoNumber/Identity PK comes
> IMO mainly from technical/common sense (but again based on technical
> issues(?)) reasons, and ADO.NET Entity Framework, I guess, can "annul all
> that reasons", and cascade update can help to solve "fragile" natural
> primary key issue (hear, hear (JC ? :)) - cascade update is the greatest
> evils - and I do not use it currently...). And there could be GUID field
> introduced instead of Autonumber/Identity PK - but just to keep "object
> identity" in this becoming so distributed world with myriad times replicated
> data objects...
>
> --
> Shamil
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Thursday, June 25, 2009 1:48 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Primary keys and entry points (was: Learning .Net --
> PHP Instead?)
>
> Hi Shamil
>
> You may do so but why would you? If you don't have an Id as the primary key
> of the underlying table, you will have to construct one, and I just don't
> think it is worth wasting time on this when applying an Id resolves the task
> in a second. I even apply the Id to a table of countries because the ISO
> codes _may_ change (rarely I know) but - more importantly - I don't have to
> decide for using the alpha or the numeric ISO code as the key.
>
> As for the single exit point, I don't think this a B/W discussion. Sometimes
> it is convenient to drop out at once when a simple condition is not met as
> in the example from the link:
>
> if (account == null) return;
>
> at the top of a method.
>
> However, I wouldn't put this somewhere many lines down as it is impossible
> to read; if you find such a line you will have to study every other part to
> look for similar lines. Thus, I much prefer the traditional construct:
>
> if (account != null)
> {
> // 20 lines of code
> // (that are totally irrelevant if account is null)
> // later...
> }
> // and out we pop
>
> This you can browse in a split second, and should you later need to apply
> some code for the case (account == null) it is very easy to insert that
> following an "else".
>
> /gustav
>
>
>>>> shamil at smsconsulting.spb.ru 24-06-2009 20:41 >>>
> <<<
> Natural PKs on the other hand...
> ;)
> Hi John,
>
> Do you mean "great PK debate"? :)
>
> I feel that I will soon give up even "use Autonumber/Identity field for
> every table" principle, which I do strictly follow for many years, in the
> case ADO.NET Entity Framework will become my main Data Access/Manipulation
> technology...
>
> It's unbelievable...
>
> --
> Shamil
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, June 24, 2009 8:16 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Learning .Net -- PHP Instead?
>
> And I contend that if the garbage collector is airtight then the need is
> less.
>
> VBA OTOH has a garbage collector that belongs to a union, it only works on
> Mondays, Wednesdays, the
> third Fridays and never on holidays. Unless of course it is just out on
> strike...
>
> I think from reading the articles and the comments underneath that there is
> no consensus. An
> opinion is an opinion is an opinion. Man is a rationalization machine and
> while you can rationalize
> as many exit points as you want, I can rationalize only a single exit point.
>
> Natural PKs on the other hand...
>
> ;)
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Shamil Salakhetdinov wrote:
>> Hi Arthur and Charlotte,
>>
>> I used to use the same "one exit point" principle over the years
> (especially
>> in my VBA programming) but I must note it's becoming obsolete nowadays: I
> do
>> not use it almost at all anymore in my C#/VB.NET development.
>>
>> Have a look what is the reasoning behind this "new approach" to not bother
>> about "one exit point" principle:
>>
>> http://ubuntuforums.org/showthread.php?t=631745
>>
>> http://discuss.joelonsoftware.com/default.asp?joel.3.325456.34
>>
>> Everything seems to be changing in programming these days but one who has
>> been in programming for some time can rather clearly see we're "just"
>> getting repeating/rethinking of "good old habits" but on higher current
>> level of evolution spiral of computer science, programming methods and
>> practices...
>>
>> Thank you.
>>
>> --
>> Shamil
>
>