[AccessD] Clustered Index Debate - Was: RE: Primary keys andentry points

...damn! ...we haven't had a good war around here for a while now :)

William

--------------------------------------------------
From: "Shamil Salakhetdinov" <shamil at smsconsulting.spb.ru>
Sent: Thursday, June 25, 2009 3:30 PM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Clustered Index Debate - Was: RE: Primary keys 
andentry	points

> Hi John,
>
> Let me note that this thread is discussed in context of
>
> "ADO.NET Entity framework plays farewell for manual SQL coding".
>
> Please read this blog entry:
>
> http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Cont
> inues.aspx
>
> with Anthony Thomas comment (here is one excerpt):
>
> <<<
> In either case, the table KEY (because it is "the" key) will be used more
> frequently than most other attributes or composites for restriction on the
> broadest set of available queries. As such, defining this key clustered 
> has
> enormous benefit, and more so than the surrogate, which is typically 
> unknown
> to the end users.
>>>>
>
> Now, after you've got read all the above, and assuming that ADO.NET Entity
> Framework is used as the main data access/manipulation technology can you
> still state strongly that using natural keys instead of surrogate keys
> "sucks from an implementation perspective and it sucks from a speed /
> storage size issue."?
>
> Thank you.
>
> --
> Shamil
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, June 25, 2009 10:50 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Clustered Index Debate - Was: RE: Primary keys and
> entry points
>
> > 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
>>
> <<< snip >>>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus 
> signature
> database 4189 (20090625) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.esetnod32.ru
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>