6

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other customer data. So, right now I am just working on the customers table, but want to make sure I follow good normalization and efficiency guidelines in case this thing grows beyond current expectations.

Right now, we have ~3,000 customers. So, we have customer codes we use to ID our customers. Each one can be a max of 20 characters, is alphanumeric, and will always be unique per customer (Foo, Inc. might have a cust code of FOOINC and might have 25 sub accounts, but there will only be one FOOINC master entity. If we ever did biz with another company called Foo Technologies Inc, we would create a new code like FOOTECH or something).

I am not a DBA, but have designed several DB's in the past and traditionally use a SQL identity field as the PK. In this instance, I've been thinking of using the customer code. What are the pros/cons of doing this? On the one hand, it seems logical to use a unique identifier as the PK if I have one, which I do. On the other, I know string PKs are slower than int PKs - this database will definitely grow, but it will never be millions of rows. The 3,000 customers are over 7 years of business, so it will be quite awhile before we even get to tens of thousands of rows.

I've researched this question and the debate usually ends with a "it depends on the data" - so please, teach me...in this situation, what would you consider when planning the table? What would you use for the PK? Any advantage to sticking an autoincrementing INT in there anyway? Any issues with indexing and inserting records?

FYI, the table layout just needs the following:

-CustomerCode(nvarchar(20))
-CustomerName(nvarchar(50))
-Address1(nvarchar(50))
-Address2(nvarchar(50)) - nullable
-Address3(nvarchar(50)) - nullable
-ZipCode (nvarchar(9))

Bonus question - worth sticking with 3NF and making a separate table holding city, state, with ZipCode as a PK and making a relationship to the customers table? Or don't worry about 3NF and keep the city/state in the customer table to avoid some joins?

Thanks for the help! Let me know if you need any other details.

Improve this question
6
  • 2
    For the bonus question: technically the ZIP does not necessarily map 1:1 to a city/state; it's a post-office defined boundary, not a politically defined boundary. Realistically, though, I doubt you'd ever get bitten by it. Definitely add city and state as separate fields so you can do things like "list all customers in state ZZ" without having to parse Address3.
    – Hellion
    Commented Nov 1, 2011 at 20:39
  • @Hellion - thanks for the comment. I would definitely put city, state, and zip in separate fields - the question was, it is worth putting city/state/zip in a separate table and looking it up through a join on the customer's zip code? +1 for pointing out the non 1:1 relationship between zip and city state, but don't think it matters here - all I need is a particular zip's city/state designation. If my customer is in zip 12345, all I need to know is that = Fooville, NY.
    – Jim
    Commented Nov 1, 2011 at 21:00
  • The problem is that 1 zip can have many city/states. If your customer is in a small town there could be multiple towns that share the zip. Also, USPS changes these mappings all the time so you can't expect to use ZIP as a PK into anything except a ZIP code table. As an aside, ZIP+4 would be a single city/state, but there's still no guarantee that USPS won't change the linkage out from under you, which may or may not be what you would expect.
    – Joel Brown
    Commented Nov 1, 2011 at 23:56
  • I've tried the "zip code/postal code table route" it just wasn't worth it. Town names didn't change that often ;-) Zip Codes didn't even change -that- often and it is more convenient having city, state and zip in the address table. You almost always want to grab some part of it, you sometimes need to search by it and just cuts out one join from the mix
    – Mike Walsh
    Commented Nov 2, 2011 at 0:23
  • Build a prototype, and measure its performance. See, for example, this recent SO answer.
    – Mike Sherrill 'Cat Recall'
    Commented Nov 2, 2011 at 0:43

3 Answers 3

Reset to default
3

Pros - It is the natural key, it makes sense and it will likely be searched on, I presume?

Cons - The default behavior (which is totally changeable) is for a primary key to be the clustered index. An alphanumeric doesn't make the best candidate because inserts can cause page splits because they aren't set on an ever increasing value like an identity column. The Int identity column will take less space (4bytes) compared to the character data (40+bytes for the unicode) . This makes your other indexes larger since the clustered key is part of them. If you ever change how you identify your customers and make customer codes, this all breaks - going with a surrogate insulates you from those type of changes.

In this situation, I tend to optimize for the insert performance and go with an identity column more often than not for the clustered key and primary key. I really like integer clustered indexes. (Now I know your question was not about clustered index, it was about primary key... You could still choose some other column to be the clustered index and make this your primary key, you could also put a unique constraint on this and treat it as a natural key but not make it your primary key).

I would at the very least index this with a unique constraint and treat it like a natural key. I just don't know if you really need to make it your primary key.

Kimberly Tripp is a trusted resource who has a lot to say about primary keys and (more so) clustered keys on her blog - https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

This is all just my opinion - YMMV.

Improve this answer
3

Your value makes a good primary key, but a crappy clustering key. I'd put an identity column on the table for the clustering key, allowing you to use your big string as the primary key. This allows you to use your key value as the primary key and the identity as the bases of the clustered index.

Improve this answer
2
  • 1
    I like Denny's point - it is a bit of what I was saying but reading his answer gives me pause on something neither of us thought of - I actually still like making the surrogate key the primary key and clustered key because now all the foreign keys are more compact. All your foreign keys are 4 byte integers now instead of that 20 character nvarchar string. That means the foreign key indexes are smaller and fit on less pages. One more reason I'd make a surrogate key as PK and use create a unique index for the natural key but not define it as PK. Again YMMV, though. There are those who'd disagree
    – Mike Walsh
    Commented Nov 2, 2011 at 0:20
  • 3
    Pretty sure you can use any unique column for the foreign key so you should be able to just make the clustered index a unique clustered index (which you'll want to do anyway to save space on the uniqueifier). Then use that for the FKs on the other tables.
    – mrdenny
    Commented Nov 2, 2011 at 2:25
2

What happens when your customer FOO, Inc. changes its name (gets bought out by another company like Northwest Airlines which is now Delta, or like Accenture which used to be Anderson Consulting)? You have to change all instances of "FOOINC" to something else, and if that's your primary key and there are other tables that have foreign key references to it, you've bought yourself a ton of headaches.

Bottom line: don't do it. Primary keys should almost always be integers - just auto-incremented identity fields where the index itself is purely for the use of the computer, so it has no intrinsic value related to the problem domain.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.