[AccessD] OT: The Great Primary Debate

Ken Ismert KIsmert at TexasSystems.com
Mon Jun 7 13:27:36 CDT 2004


Stuart:

Sorry for the delay in responding.

>Does it? What if you:
>1. Delete records and rebuild the table without
>   compacting the database.
>2. Create records with an algorithm that generates
>   records counting backwards from an end date
>3. Extend your table by adding earlier dates after
>   you build your first date range.

Notes: IDday0 = ID of earliest date in table, Day(ID) = Day number for ID,
counting from IDday0

1 & 2: The mathematical relationship holds. Day(ID) = Abs(ID - IDday0). This
holds true when the list is generated the first time, and when it is
regenerated.

Interestingly, whether backwards or forwards, the formula for counting the
days between two dates by their ID is the same: DaysBetween(ID1, ID2) =
Abs(ID1 - ID2)

3. That's why I said auto-generated! This is where relying on a ANPK can
cause you problems: you can't extend the table without ruining your
calculations.

For regular, sequential data (no interruptions) with a single field natural
key, or any data where a unique key can be mathematically calculated (as
above), you can make an argument that ANPK is redundant, and can actually
make the data more difficult to work with. This is where intent of the data,
and your data modeling style, plays the deciding role in your PK choice.

-Ken



On 4 Jun 2004 at 15:46, Ken Ismert wrote:

> On the other hand, the Autonumber is supposed to be a 'meaningless' unique
> ID. But, consider an auto-generated date dimension table, with consecutive
> date records. Think an Autonumber key is meaningless in this situation?
> Think again - it really represents the number of days since Day Zero (the
> earliest date record in your table).

Does it? What if you:

1.  Delete records and rebuild the table without compacting the database.
2.  Create records with an algorithm that generates records counting
backwards
from an end date
3. Extend your table by adding earlier dates after you build your first date
range.
4.  etc

If you rely on the ANPK to be meaningful and use it in calcuations base on
your
assumption, you can get into trouble very quickly.





--
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.





More information about the AccessD mailing list