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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Are the Part Nos and Customer Codes unique?
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi Norie. yes they are
0
Anders Ebro (Microsoft MVP)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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)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
aikimarkCommented:
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 BrockCIOCommented:
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
PatHartmanCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.