I'm working on a sales app that will run on mobile devices. I am looking for some nice ways to generate unique yet human readable customer id and invoice id numbers that won't colide when we sync up with the main db.

Using a temporary id on the device and assigning an actual id upon syncing might work, but if the salesperson prints an invoice out in the field, the temporary invoice number on the clients document will not reflect the actual invoice id the main db.

Straight incrementing values won't work for obvious reasons.

GUID's and timestamps are not human readable.
Customer service:"Can I have your invoice number please?"
Client: "Sure, its -1874719792"

I could concatenate the salesperson id with an incrementing value for the customer id (34-5678). And then concatenate the new customer id with an incrementing value to generate the invoice id (34-5678-0001). Does not seem very elegant.

I could allocate a group of id's for each device to use every time it sync's up. Again not real elegant.

Any other ideas? Are there any resources out there for this type of thing?

Brian

tough question

i would allocate a block of numbers

not elegant, but better than some other schemes

I'd propose something rather deviant (but then again, I'm often rather deviant when solving database related problems).

Have two columns for each of the "id" values. One column is for display only, and the other is the actual key value. When a row is added on the mobile device, allow both columns to be the same. When the row is merged into the "master" database check for key conflicts and adjust it to a unique value if needed. You may end up with 10,000 customers with a display value of 1, and 1,000,000 invoices with a display value of 1, but who cares? As long as the actual key value stays straight, you could have every customer have a display value of 1 because it is only the display value, not the real key!

Oh yeah, for composite Alternate Keys (like client number, invoice number) you may want to keep them unique too. That way each client can only have one "invoice #1", even though these are only display values... It might confuse people to have more than 1 of them! This poses a problem, since that might force you to change display values as you merge data into the master database, but one possible solution is to only assign the display value when the row is merged to the master (use a "dummy" display value like zero when inserting rows on a mobile device).

-PatP

pat, sometimes you are just too devious for your own good

let's examine how your strategy holds up in practice

Customer service:"Can I have your invoice number please?"
Client: "Sure, it's 937"

now, are you sure you have the right client? hmmmm???

I'm with you on that!

Once upon a long ago, we had a DOS/VSE system based on Mark IV. It had clients, who had projects, which had bills. For any given client, there was only one project 1000. For any given client/project, there was only one bill 1. We still had a gazillion project 1000s, and more bill 1s than I care to count.

When I converted the monstrosity to SQL Server (in a rescue of an existing conversion project), I went to an ID based scheme. While most of our users are unaware of the change (and still think in projects with two digit bill numbers), the underlying system runs on the id values.

It works lovely, for thousands of users and millions of bills.

-PatP

nice anecdote, but it doesn't answer the question

if a client calls in to the help line and says "my project number is 1000" how do you know which client it is?

my point is, this strategy doesn't solve the posted question

What part of the posted question did I miss? Those are nice, human readable numbers, aren't they? Do you see a flaw in my logic, other than the fact that it doesn't work the way that you expect?

-PatP

okay, i'll slow it down for you

your master database has uploaded multiple invoices with the same number

customer calls in and says "my invoice number is 12345"

which customer is it?

I would assume that the customer would know who they are. Most (and I suspect all) of ours know that.

Why would you presume that there is some mystic relationship between a display invoice number and a customer? I can understand why you'd expect a relationship between an invoice id (be that an integer, GUID, or something else) and a customer, but what does the number have to do with it? The number is a nice, human readable thing while the id may be a rather ugly machine readable thing. There isn't any relationship between the number and the id in my mind.

Oh yeah, and where in the original problem specification does it say anything about the invoice number needing to identify the client?

-PatP

the customer knows who they are, sure, but do they necessarily know their customer number? no, and then you have to put them on hold while you try to look up their customer name

but hey, okay, i've had enough, you win, you always mount such a masterful argument

brian, go ahead and give every invoice the number 1000

but make sure the customers alway tattoo their customer numbers on their arms so that they know who they are

That's the problem with "nice, human readable" things. That fundamental requirement more or less precludes uniqueness, which is near and dear to relational design.

I'm certainly not saying that my way is the only way. I'm not even saying that I think it is the best way for every business. I do firmly believe that it meets the requirements in the original problem specification though, and that it is a viable solution in the real world because we've been doing it for at least a couple of decades for thosands of employees, hundreds of thousands of clients, and many millions of bills!

Our client numbers can be up to seven digits, but the folder numbers within a client are usually four digit numbers... The bills are larger, since many of them are now using the underlying bill id.

Our clients realize that folder numbers are specific to them, that their folder 2500 might be quite different from someone else's. While they don't think of it in terms of a composite alternate key, they understand that the client number is needed with the folder id to uniquely identify a specific project. Even our employees probably don't have any clue that there is a monsterous number that uniquely identifies the project too, unless they are one of the few that can write queries.

-PatP

integers are nice, human readable things, and last time i looked, integers are unique

so you're saying that a pre-allocated block of numbers taken from a range of integers "precludes uniqueness"??

it is to laugh

On the scale that I'm talking about (ten plus digits), most of our users consider the ids to be "too big" for easy use.

They also like the fact that a given location can create a standard where folder numbers have meanings, so that for that location, folder 1234 for all of their clients is where they charge the recreational pharmaceuticals. They remember that 1234 has a specific meaning, but not that the list of ids (which in this case are still integers, albiet thousands of them) that represent those folders for all of their clients have that given meaning.

-PatP

Yes, integers can be unique. No, using them in pre-allocated blocks has no impact on uniqueness. Once you start using the numbers this way for any length of time though, the users stop thinking of them as being easy to remember or use, even though the machine has no problem!

-PatP

nevertheless, they can read them off the printed invoice, can't they?

hey, i said i was gonna stop arguing with you, and i will

hey brian, go ahead and implement pat's solution

don't forget those tattoos