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

Part number as primary key?

P: n/a
I am about to revamp the most referenced table in my whole database and
am unsure the best way to approch it. The table's primary key is also
used as a part number for all the items in the table.

My first problem is that I need to seperate the single part number
field into multiple (3 or more). I could make multiple primary key's
for each of the new fields, but some would be null, and I hate dealing
with multiple primary keys. Plus this change will affect about 50
tables, and countless forms and reports.

My thinking is that I need a true primary key, totally seperate from
the part number field(s). Maybe an autonumber??

My other problem is "splitting" that part number field. The reason I am
splitting is is because there are different "types" of parts and have
different part numbering schemes. Other than that ALL the data is the
same, and they are referenced throughout the DB exactly the same (same
forms, reports, etc... for the most part). So I can't see splitting it
up into multiple tables.

I want to do this right. Does anyone have any tips or advise? It would
be greatly appretiated.

Thanks in advance,
Jeff

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
JumpinJeff wrote:
I am about to revamp the most referenced table in my whole database
and am unsure the best way to approch it. The table's primary key is
also used as a part number for all the items in the table.

My first problem is that I need to seperate the single part number
field into multiple (3 or more). I could make multiple primary key's
for each of the new fields, but some would be null, and I hate dealing
with multiple primary keys. Plus this change will affect about 50
tables, and countless forms and reports.

My thinking is that I need a true primary key, totally seperate from
the part number field(s). Maybe an autonumber??

My other problem is "splitting" that part number field. The reason I
am splitting is is because there are different "types" of parts and
have different part numbering schemes. Other than that ALL the data
is the same, and they are referenced throughout the DB exactly the
same (same forms, reports, etc... for the most part). So I can't see
splitting it up into multiple tables.

I want to do this right. Does anyone have any tips or advise? It would
be greatly appretiated.

Thanks in advance,
Jeff


A surrogate Primary Key is a good idea in your case, but not for some of the
reasons you stated. A Table cannot have more than one PK so that option is out.
It can have one PK consisting of multiple fields, but in that case none of them
can be Null so that option is also out. That pretty much leaves a surrogate as
the best choice.

A surrogate wouldn't have to be an AutoNumber, but that would work as good as
anything else.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #2

P: n/a
<<My thinking is that I need a true primary key, totally seperate from the
part number field(s). Maybe an autonumber??>>
YES, YES, a thousand times YES!!!

If you truly need a three part part number and each part serves a specific
and distinct purpose, use three field in the part table for the three parts
of the part number. Concatenate the three parts where you need the full part
number.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com


"JumpinJeff" <Ju********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
I am about to revamp the most referenced table in my whole database and
am unsure the best way to approch it. The table's primary key is also
used as a part number for all the items in the table.

My first problem is that I need to seperate the single part number
field into multiple (3 or more). I could make multiple primary key's
for each of the new fields, but some would be null, and I hate dealing
with multiple primary keys. Plus this change will affect about 50
tables, and countless forms and reports.

My thinking is that I need a true primary key, totally seperate from
the part number field(s). Maybe an autonumber??

My other problem is "splitting" that part number field. The reason I am
splitting is is because there are different "types" of parts and have
different part numbering schemes. Other than that ALL the data is the
same, and they are referenced throughout the DB exactly the same (same
forms, reports, etc... for the most part). So I can't see splitting it
up into multiple tables.

I want to do this right. Does anyone have any tips or advise? It would
be greatly appretiated.

Thanks in advance,
Jeff

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.