By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,345 Members | 1,667 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,345 IT Pros & Developers. It's quick & easy.

Database normalization question

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

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.


Nov 13 '05 #2

P: n/a
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.


Nov 13 '05 #3

P: n/a
> > 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.Acct_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?
Nov 13 '05 #4

P: n/a
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.Acct_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.

Nov 13 '05 #5

P: n/a
> >>>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.Acct_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!
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.