Whats a good value for 'identity increment' for an 'Orders' table

Question

Whats a good value for an identity increment for an 'Orders' table? (orders as in shopping cart orders)

I want the order numbers to appear so that we have more orders than we really do, plus make it harder for users to guess order numbers of other users in cases where that might be a problem.

I dont want too big a value such that I might run out of values, and i also don't want a noticable sequence to be apparent.

I've settled on 42 for now

Answer 1

It is not usually a good (security) idea to expose IDs to end-users.

I would use a normal +1 autoincrement ID column, and have the user-visible order number be a string based off the current date. Maybe use date + number of orders so far today: "20080919336".

Answer 2

I would say the most important thing is to have your identity seed start out high like 156,786 or something. As for the increment, it might be good to use something odd so not all of you order numbers are even.

I must say however, that it is better to not use an Identity field for an order # that will be exposed to users. It's usually better to keep these things hidden in the database and have a separate field for the Order Number. This way, you can change an Order Number without messing up all your references. All your other tables will reference the Identity field (should be your primary key) and then you can just slap an index on the other Order # field to keep it unique.

You'll thank me later.

Answer 3

You could use a non-numerical Order Reference code like "ABC0123". Depends on your platform, but you can either use this as the Primary Key for your table or in addition to the automatically incremented identifier (which would then simply become the internal reference).

Also: if a user guessing an order number is an issue, you really need to think about some security measures.

Answer 4

Why increment? Using a GUID would make the number of orders unguessable, and make it almost impossible to guess an order URL (obviously you'd still want to check to see if whoever's viewing it is authorized to see it).

If you are determined to use a monotonically increasing integer ID, it just comes down to estimation how many orders you'll have until you run out. But they're always going to be guessable if somebody can see a handful of them and guess the sequence increment number. Then they'll know how many orders you've had exactly, and be able to guess URLs all day.

Answer 5

You can use the indenity value. Just set the seed high and then icrement that.

[ID] [int] IDENTITY(5497,73) NOT NULL,

I have a feeling that if people see they are order number 1, they would not care. What I would do is set it at 3 million with a increment of 1. It will be a large number, and go up. You can always reseed it if you think people aren't buying becuase they are the 5th person to order.

Answer 6

While it is the answer for everything BUT this question, 42 isn't a good bet.

One possibility is that you use the customer ID and appended increment number... but that wouldn't be an 'identity increment' with a seed on the orders table in that case.

Example: JoeBlow has a ID of 56 in the customers table, its his 18th order (5618) and to further mask your order count(?) you can do whatever else along those lines you like, append millseconds/random or something like that. This is a simple example

Answer 7

If you want to make it appear there are more orders than there really are, just pick an arbitrarily large id number to start with. But then, if it were me, I'd just set the increment to 1. To keep users from guessing order numbers, obfuscating the number might not be the best way to go.

If I'm user 123, and I place an order, number 4567, let's say the url to view the order looks like:

http://example.com/orders?id=4567

Say I'm feeling mischievous and decide to start playing with that url. What if I try:

http://example.com/orders?id=5000

If there's no order 5000 yet, what will it display? What about something as simple as "Invalid order id". But then, say I try:

http://example.com/orders?id=4568

And that order does exist, should it display the order? It could check the id of the user that created the order, and unless it's me (good old user #123), display the same error message, "Invalid order id". That could make it impossible for a user to tell whether any given order id exists unless they created the order themselves.

Answer 8

Why not have a pool of random numbers, and then take the next random number from your pool? This could be done by taking some data (user id, and a counter) and using some encryption/hashing algorithm.

Answer 9

1. create an incrementing Int as your internal PK. Yields superior fill factors for your storage and indexing performance.
2. create a hash, composite key, or GUID as an external AK - this value should be the one exposed on forms etc. US Drivers Licence # is a great example of contrived composite keys - generated from unique random number plus some arbitary combination of the driver name characters.
3. never have a GUID as a Clustered index - it causes terrible page fill issues