suggestion regarding primary key...

[Chummly66]

hey everyone,

Was working on something and was curious as to what you all would suggest.

I am setting up a simple table with the following: customer first, last, company name, and phone.

I also have a second table to which I want to link together with the above table, which has equipment information.

My goal is to not have duplicate customers in the customer table. Basically, I have a search field searching the phone number. If the phone number is not found, I add the customer; if it is found then I select that customer. Then through a form, add the equipment.

My question would it be a good or bad idea to make the Phone Number field it my primary key? Its a text field currently. Atleast I would know there wouldnt be any duplicate phone numbers in the database. Also, if I did do this, I could also use this field to relate the two tables together correct?

Or, should I just use a separate ID field for my primary key?

thanks for any insight and advice!
Richard

 

[antonyx]

first i think you should consider that customers may change their phone number

or also the very RARE possibility, that someone moves out of their home, and a new customer moves into that address and has that phone number.

however, a phone number clearly identifies a person uniquely.
i have a similar function in my database, you enter a phone no. and if it is a new customer then you can add it.

i use a customer ID tho, and i would recommend that in my opinion.

 

[Chummly66]

Antonyx,

thanks for the feedback. the numbers I work with are cellular so yes, they do sometimes change but not often. And some custoemrs do have more than one, so there would always be a chance the same customer may be in the database twice that way. But my records I keep are based on the phone number.

thanks again!!

Richard

 

[Lilliabeth]

I don't see any benefit at all to making PhoneNumber be the PK.

Make a CustomerID field for PK, and create an index on PhoneNumber.

"Rarely changes" is not the same thing as "never changes".

What if you want to enter a customer but you don't know their phone number yet?

 

[stix4t2]

Using a unique ID is a good idea for the customer. You can still index the actual phone numbers and prevent duplicates. I like the idea of indexing the customers and then using a foreign key in the phone table. Duplicate customers is a bad idea because it becomes a problem when changing the customer's information. If they change address or other info, will you verify that there is more than one record and edit multiple records. Plus you can identify customers with multiple phones.

But you are the one who is managing the business logic. Duplicate customer information may not be a problem.

 

[JoeAtWork]

The Primary Key should be something that is always guaranteed to be unique. Don't even consider using the phone number for this - that's practically guaranteeing that your table won't keep its data integrity.

There are other ways to minimize duplicate customers. For example, when the user is entering a new record, in the BeforeUpdate event of the PhoneNo field you could do a lookup to see if that number already exists and if it does, alert the user - perhaps with the option of cancelling the new record and jumping to the existing one instead.

 

[Chummly66]

awesome feedback everyone!! Much appreciated.

Richard