Does anyone have ideas on setting up a protocol for Customer ID's?
We want to move from printing customers' names on our paper work to using a Customer ID system instead.
I guess that would depend on what you want to achieve. There are lots of different protocols for an ID, from simple serial numbers, to "smart" numbers, all the way up to UUIDs. What is the number to be used for? A key field? Something your users will perform a search with? Something the customers would use to identify themselves when logging into the database?
Usually, if it's something users will interact with, I would recommend keeping it simple, since we humans are not good at keeping track of complex numbering schemes. Perhaps a serial ID or a "smart" number that concatenates the year the person's record was created with his state of origin with a serial ID (or something like that). However, if it's something you're looking at for a key field, then uniqueness and (possibly) use in queries over, say, the web might be considerations, so you might want to look at the Get ( UUID ) function or a serial ID that's validated unique.
Just some general thoughts. There are lots of possibilities (and they have been debated, with great enthusiasm, on this forum and others), depending on your needs.
Thanks for the points.
I've never heard of a smart number but that sounds like the way to go.
We are just trying to hold some security with our customer names.
So, I thought it wise to go with a Customer ID.
At the moment, we have customer names on all our work orders etc.
With business fluxuations, we use many temps on our production lines.
So, for security reasons, I would like to remove all customer names from our paper work which is viewed outside our office.
Since security is your main concern, the kind of "smart" number Mike describes that concatenates information about the customer may not be something you want to do. A basic serial number with no potentially identifying interpretation should be fine.
If you're feeling fancy, another thing you could do that could be interpreted as a "smart" number is to add a check digit. This could help the database identify transcription errors, like when a dyslexic temp mistypes the number. If you decide to try this, this custom function is a good start on the method I recommend. Your Customer table might include a serial field that auto-enters a serial number, and an ID field that auto-enters that serial number concatenated with the check digit.
Jeremy's point about check digits is a good one (especially when dealing with people whose typing skills approximate mine).
Of course, any "smart" scheme doesn't necessarily have to incorporate personal information, if that's a concern. It might only incorporate information about the case, the date the person became a customer, or perhaps the customer rep's ID. (Example: FileMaker tech support case numbers start with the year, the month, and the day the case was initiated.) The big advantage with such numbering schemes is they allow you to extract useful information from them without looking up the customer's record, should that be important to you.
On the other hand, if you don't care, then ... eh. Don't bother. Just be aware that you'll need to mind your serial entries if you have to update your tables (e.g. import from one copy to another) so you don't wind up with duplicates. (Because I'm lazy, and forgetful, I don't typically use serial numbering, but to each his own.)
While some users think in terms of numbers, most relate to names much better. Why do you want to switch? "Best Pies" tells me immediately who I'm talking about "BP1960" not so much.
While it's important to have a unique Customer ID (some sort of UUID) in the background to uniquely identify specific customers, it's not really necessary to burdent the users with this data. When they see "Best Pies" you (the programming) thinks "1838Q9T532568".
Working this way does require unique company names connected to each background Customer ID, but this can be done with two text fields: 1) Which the users see internally ("Best Pies - Fleet Street" and "Best Pies - Market Street") and 2) Another field which is used with Customer correspondence ("Dear Best Pies, Regarding the decreasing presence of cats..." in either case).
Not a Sweeny Todd fan, are you, Bob?
Sorry I missed this on my other reply - your need to actually hide the customer name. In this case the other suggestions can all work well. One variant on the smart scheme is to use the first three digits of the first word in a name and first three of the second. To those in the know, this often conveys enough information to be useful, but obscures it for others. Of course, you'll have to deal with one word name, names with less than 3 characters, and the like.
Retrieving data ...