[AccessD] Access and SQL Server

Let us distinguish two problems:

The first is the "egg" problem. I have 200 chickens each of which lays
several eggs per day, each of which is plonked into an arbitrary-numbered
case. At some point, it may be interesting to know which chicken laid which
eggs and into which cartons they were placed. Most often, this level of
detail is not interesting, but occasionally it is vital and potentially
life-saving.

The second is the "serial number" problem. Every crankshaft or windshield or
manifold coming off an assembly line has a unique serial number, unlike the
aforementioned eggs. Each one of these parts can be traced to a shift and a
line and perhaps ultimately to a worker.

Big difference in these problems, and big difference in which attributes we
choose to model.

IME, I have dealt more with the egg problem than the serial number problem,
but in recent years this has changed. To further complicate things, this
latter problem has been compounded by the PITA issue (Point in Time
Archictecture; for details on this problem and its solution see my piece at
Red Gate's site).

Arthur

On Mon, Mar 7, 2011 at 11:58 AM, Gustav Brock <Gustav at cactus.dk> wrote:

> Hi Jim
>
> I'm not so sure about that. As far as I know, maintenance cost of a an
> autonumber index is close to zero for adding records, zero for updates of
> other fields, and tables that large typically are for appending/reading
> only.
>
> But, of course, scenarios exist where you have to optimise where possible.
>
> /gustav
>
>
> >>> jimdettman at verizon.net 07-03-2011 16:22 >>>
> Gustav,
>
>  Well you may shoot yourself in the foot at times with that approach.
>
>  In the book/author linking table example I gave, what if you had millions
> of rows?  Your going to maintain an extra index on the auto number key
> simply because you want one when the book ID/Author ID works fine as a key
> and you need a index on it anyway?
>
>  Sorry, but that just doesn't make any sense to me.
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Monday, March 07, 2011 10:18 AM
>  To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Access and SQL Server
>
> Hi Jim
>
> In, say, a grid you need to be able to identify any row quickly and easily
> even if the table of this grid is the mother of all tables.
> Nothing beats an Id of autonumber in this respect: Always the same name,
> same data type, same behaviour, same methods, same everything.
>
> I realise it may require an index more, but that disadvantage is ignorable
> compared to the huge advantages gained by a consistent use of an autonumber
> Id for every table - which to me includes any lookup table as well. It
> makes
> life safe and so much easier. No considerations: Is this a tiny table? Or a
> lookup table only? Or?. Just do it, add the Id, and move on.
>
> /gustav
>
>
> >>> jimdettman at verizon.net 07-03-2011 15:29 >>>
>
> I would not use an auto number as a key until I needed to use it as a FK
> some where.
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>