Access/SQL tables using text data type as a primary key

In Access/SQL tables how advisable is it to use text data type as a primary key.
I am building a database specifically for manufacturing where Part numbers  and Customer Codes
, both text fields,  need to be stored as the primary key. Is this advisable?

At the moment the tables are in Access but will in future be moved to a cloud SQL database.
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
1) Even in stricter regulated businesses I've seen that two different parts (e.g. by material) have the same part number (they have the same functionality).
2) Codes may reappear over long terms for different companies.
3) Codes consist also of formatting. Formatting can change over time. Different formats may exist at the same time.
4) Codes are usually not normalized.
5) Storage size is bigger.
6) Query performance is lower, but this normally not in a significant range.

1+2 can lead to unusable data over time. This is a small risk. But outcome can lead to completely unusable data. Which can require manual reentry.
3+4 can lead to wrong data by missing the goals of normalization.

In many cases you also need a DKNF table and a history approach. Using a natural key is bad, as many BI developers know (dimensional modelling).

Start with a single (internal) number as surrogate primary key for your parts. Make the natural part number at unique. Same for Customer codes.

Now you can simply address the above possible problems why they occur. But changes then would not affect the entire system.
1
 
NorieVBA ExpertCommented:
Are the Part Nos and Customer Codes unique?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi Norie. yes they are
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Anders Ebro (Microsoft MVP)Connect With a Mentor Microsoft DeveloperCommented:
There are a few points to consider. A join on a string field will generally be slower than a join on a integer field, simply because the amount of data to compare is bigger. On the plus side, sometimes by using a string ID field, we can avoid doing a join at all, if all we really needed anyway was the string from the table.

A good ID should be non-changing, and unique. An issue with string IDs is that they are often not truly unique, and truly permanent. We might have been told they are, but there are exceptions. Or what if two manufacturers decide to use the same id for a product? Therefore life can be simpler by sticking to a autonumber ID.  

So I would say it depends on how it is going to be used.
1
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
Murray,

  You should give this a quick read-through:

https://www.experts-exchange.com/articles/2041/The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html

  as it will give you a better understanding of what your asking about, and the issues involved.

 To answer the question directly though, in short, it's not a great idea as has been said.

Jim.
0
 
aikimarkConnect With a Mentor Commented:
How long are these data (Part numbers  and Customer Codes)?

You can use text PK value, but you'll generally pay a performance price.  It would be better to use an autonumber value as the PK.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
You will regret it, sooner or later.

It just is so much easier, that every time you meet a key, it is a Long.
0
 
PatHartmanConnect With a Mentor Commented:
Sounds like the two keys are coming from another application over which you have no control.  That really is the tipping point for me.  I've been through more than one part conversion for a big manufacturing company and they are not pretty.  Primary keys do not have to be visible to the user and many applications hide autonumbers.  They are simply the glue that ties everything together.

Repeating what the others have said.
Use an autonumber as the PK and make a unique index on the natural key.  That way if the other application decides they need to change their "key", it is simply a data change for you.  No joins or queries or relationships will be impacted.  All your searching and filtering can still be done with the natural key because you've created the unique index so the users would never even know what you did behind the scenes.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you all very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.