Database normalization question

[deko]

I've heard it's best to have a meaningless primary key (such as an
autonumber field) as opposed to a pk that contains volatile data. This
sounds reasonable enough. In fact, I think I'd need a good reason *not* to
use an autonumber field for a pk. But how do I normalize with an autonumber
pk? My guess is I pick a field to use as a "pseudo pk" when defining
dependencies.

Let's say I follow the single field, autonumber pk philosophy. I'll never
have a composite pk and thus won't be concerned with 2NF, but will be
concerned with columns that are not dependant upon the pk (or rather the
"pseudo pk") for 3NF normalization.

If I have an "Accounts" table, for example, I might have an AcctNumber
(Long) field and an Acct_ID (Autonumber) pk field. The Acct_ID is
meaningless, so I'd have to ask, "What data is this table designed to
store?" - easy enough: Account numbers. So when adding new fields I
normalize to the AcctNumber field (whether it's indexed or not). This means
fields like Account Type and Creation Date might be added to the table, but
Customer Name and Telephone Number would go in a separate table.

Does this sound about right?

Thanks in advance for your comments.

[alysandra]

deko wrote:

I've heard it's best to have a meaningless primary key (such as an
autonumber field) as opposed to a pk that contains volatile data. This sounds reasonable enough. In fact, I think I'd need a good reason *not* to use an autonumber field for a pk. But how do I normalize with an autonumber pk? My guess is I pick a field to use as a "pseudo pk" when defining
dependencies.

Let's say I follow the single field, autonumber pk philosophy. I'll never have a composite pk and thus won't be concerned with 2NF, but will be
concerned with columns that are not dependant upon the pk (or rather the "pseudo pk") for 3NF normalization.

If I have an "Accounts" table, for example, I might have an AcctNumber (Long) field and an Acct_ID (Autonumber) pk field. The Acct_ID is
meaningless, so I'd have to ask, "What data is this table designed to
store?" - easy enough: Account numbers. So when adding new fields I
normalize to the AcctNumber field (whether it's indexed or not). This means fields like Account Type and Creation Date might be added to the table, but Customer Name and Telephone Number would go in a separate table.

Does this sound about right?

Thanks in advance for your comments.

[rkc]

deko wrote:

I've heard it's best to have a meaningless primary key (such as an
autonumber field) as opposed to a pk that contains volatile data. This
sounds reasonable enough. In fact, I think I'd need a good reason *not* to
use an autonumber field for a pk. But how do I normalize with an autonumber
pk? My guess is I pick a field to use as a "pseudo pk" when defining
dependencies.

Let's say I follow the single field, autonumber pk philosophy. I'll never
have a composite pk and thus won't be concerned with 2NF, but will be
concerned with columns that are not dependant upon the pk (or rather the
"pseudo pk") for 3NF normalization.

If I have an "Accounts" table, for example, I might have an AcctNumber
(Long) field and an Acct_ID (Autonumber) pk field. The Acct_ID is
meaningless, so I'd have to ask, "What data is this table designed to
store?" - easy enough: Account numbers. So when adding new fields I
normalize to the AcctNumber field (whether it's indexed or not). This means
fields like Account Type and Creation Date might be added to the table, but
Customer Name and Telephone Number would go in a separate table.

Does this sound about right?

Sounds exactly right. The AcctNumber identifies the Account relation.
Everything in the Account relation should be dependent on the AcctNumber.

The Acct_ID autonumber is thrown in, not really as part of the
Account relation, but as a device to uniquely identify a physical
record stored by the database system.

[deko]

> > Does this sound about right?

Sounds exactly right. The AcctNumber identifies the Account relation.
I assume you're using the term relation in a generic sense - i.e. if I have
an Accounts table and a Transactions table you're *not* saying a
relationship should be established using the AcctNumber field.
Everything in the Account relation should be dependent on the AcctNumber.
Do you mean everything in the Accounts *table* should be dependent on the
AcctNumber?
What do you mean by "Account relation"?
The Acct_ID autonumber is thrown in, not really as part of the
Account relation, but as a device to uniquely identify a physical
record stored by the database system.

Yes, and relationships between tables (e.g. between Accounts and
Transactions) are built using the tblAccounts.Acc t_ID (one) and the foreign
key tblTransactions .Acct_ID (many).

As an aside, a foreign key is simply a field that is the "many" side of a
one-to-many relationship - is this correct?

[rkc]

deko wrote:

Does this sound about right?

Sounds exactly right. The AcctNumber identifies the Account relation.

I assume you're using the term relation in a generic sense - i.e. if I have
an Accounts table and a Transactions table you're *not* saying a
relationship should be established using the AcctNumber field.

Right again. A relation is what more or less becomes the structure of a
normalized table.

Everything in the Account relation should be dependent on the AcctNumber.

Do you mean everything in the Accounts *table* should be dependent on the
AcctNumber?
What do you mean by "Account relation"?

O.K. Just substitute table for relation. When you are deciding what
belongs in an Accounts table you don't decide that based on the fact
that an autonumber will identify the physical row in the database.
The concept of an Account does not include an autonumber that no
human will ever see. It includes an Account number that they will.
A bank check does not have the autonumber generated by the database
being used printed on it. You don't have to write your account
autonumber on the back of a third party check in order to cash it.
Account type is in the table because it denotes the type of account
AcctNumber identifies. Creation Date is in the table because it
establishes when the account identfied by AcctNumber was created.
The primary key of the Customer table is in the table because it
establishes a relationship to the customer that owns the account
identfied by AcctNumber.

The Acct_ID autonumber is thrown in, not really as part of the
Account relation, but as a device to uniquely identify a physical
record stored by the database system.

Yes, and relationships between tables (e.g. between Accounts and
Transactions) are built using the tblAccounts.Acc t_ID (one) and the foreign
key tblTransactions .Acct_ID (many).

Right again. If you have decided to buy into the theory that all tables
that are going to be related to other tables should have an autonumber
primary key.

[deko]

> >>>Does this sound about right?

Sounds exactly right. The AcctNumber identifies the Account relation.

I assume you're using the term relation in a generic sense - i.e. if I have

an Accounts table and a Transactions table you're *not* saying a
relationship should be established using the AcctNumber field.

Right again. A relation is what more or less becomes the structure of a
normalized table.

Everything in the Account relation should be dependent on the

AcctNumber.

Do you mean everything in the Accounts *table* should be dependent on the AcctNumber?
What do you mean by "Account relation"?

O.K. Just substitute table for relation. When you are deciding what
belongs in an Accounts table you don't decide that based on the fact
that an autonumber will identify the physical row in the database.
The concept of an Account does not include an autonumber that no
human will ever see. It includes an Account number that they will.
A bank check does not have the autonumber generated by the database
being used printed on it. You don't have to write your account
autonumber on the back of a third party check in order to cash it.
Account type is in the table because it denotes the type of account
AcctNumber identifies. Creation Date is in the table because it
establishes when the account identfied by AcctNumber was created.
The primary key of the Customer table is in the table because it
establishes a relationship to the customer that owns the account
identfied by AcctNumber.
The Acct_ID autonumber is thrown in, not really as part of the
Account relation, but as a device to uniquely identify a physical
record stored by the database system.

Yes, and relationships between tables (e.g. between Accounts and
Transactions) are built using the tblAccounts.Acc t_ID (one) and the foreign key tblTransactions .Acct_ID (many).

Right again. If you have decided to buy into the theory that all tables
that are going to be related to other tables should have an autonumber
primary key.

Sounds good. Thanks for the feedback!