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

JumpinJeff wrote:
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

<<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
resource (AT) pcdatasheet (DOT) com
www.pcdatasheet.com




"JumpinJeff" <JumpinJeff (AT) gmail (DOT) com> wrote