setting primary keys in two different tables.

[maura]

I have two tables that I am creating: a client table and
an auction table. I am using an abbreviation of the
clients name as the primary key, ie: FFCU for Fairbanks
Federal Credit Union. And I plan on doing the same for the
auction table, ie: SAA for Southern Auto Auction.

The problem is that sometimes an auction will be a client,
so I am going to add them in the client table. My question
is can I use the same abbreivation in the client table as
I did in the auction table? Will I get any conflicts down
the road in doing this?

 

[Tim Ferguson]

The problem is that sometimes an auction will be a client,
so I am going to add them in the client table. My question
is can I use the same abbreivation in the client table as
I did in the auction table? Will I get any conflicts down
the road in doing this?

No conflicts as far as the keys are concerned, but I do worry a bit about
your overall design.

Problem 1 - if SAA is in both tables, and each table has a different phone
number or address, how are you going to know which one is wrong?

Problem 2 - if SAA then falls out of favour, do you delete both records, or
just one, or forget how many you have and leave one in and one out by
accident or what?

You might be better off thinking about a Companies table, and using links
to connect that with ClientRelationships (these are usually called Sales,
aren't they?) and AuctionAgents, and so on.

Just a thought


Tim F

 

[Jeff Boyce]

Maura

Using a text abbreviation as a primary key is likely to cause you headaches.
Not guaranteed, but likely. You need something unique as a primary key, and
FFCU or SAA could be used for "Flying Foxes Care Unit" (a helicopter-based
animal rescue organization) and "Seasonal Affect Aficionados" (folks who get
off on being bummed out during long winters).

So, for your first paragraph, DON'T! If there will be no likely unique
identifier possessed by each every likely entry in your database, use the
Autonumber datatype. It's (supposedly) unique, arbitrary, and can be used
as both a row identifier and as a foreign key in related tables.

Next issue: You described two buckets of data, clients and auctions. How
did you arrive at this classification? Did you work through a normalization
process to derive these tables? Are there attributes, besides having an
"organization name" that these two tables have in common? And what other
info/buckets does your database have -- and how are those related to these
two?

As for your last question, whatever primary key you use in your client
table, you can use as a foreign key in your auction table (but I'd still
recommend looking into "normalization" and using an Autonumber instead of
initials).

Good luck

Jeff Boyce
<Access MVP>