Best Way to Generate Order Numbers for an Online Store

Best way to generate order numbers for an online store?

As a customer I would be happy with:

year-month-day/short_uid

for example:

2009-07-27/KT1E

It gives room for about 33^4 ~ 1mln orders a day.

What is the best format for a customer number, order number?

Go with all numbers or all letters. If you must mix it up, then make sure there are no ambiguous characters (Il1m, O0, etc.).

When displayed/printed, put spaces in every 3-4 characters but make sure your systems can handle inputs without the spaces.

Edit:
Another thing to consider is having a built in way to distinguish orders, customers, etc. e.g. customers always start with 10, orders always start with 20, vendors always start with 30, etc.

How to generate Unique Order Id (just to show touser) with actual Order Id?

If your requirements are:

• It must be reversible (i.e. given just the "random" ID, you can find the original order_id)
• No extra columns
• You don't want to show the original/internal order_id to the user at all

then I would recommend some kind of two-way encryption. Hashing won't work as you can't find the original value from a hash.

I'm also adding that it should be human-friendly e.g. someone can call it out over the phone to you

I'm going to use a very simple two way encryption class located here, which was written by Tony Marston.

We want the solution to be human-friendly so let's remove some of the scramble chars. I've left only uppercase characters, numbers and the space and dash symbols. All of these can be easily communicated using the standard phonetic alphabet, and the forced use of uppercase removes any confusion as to what a character is.

These are the scramble strings I used (I used this online word scrambler rather than trying to scramble the string myself):

    $this->scramble1 = '0123456789-ABCDEFGHIJKLMNOPQRSTUVWXYZ ';
    $this->scramble2 = 'UKAH652LMOQ FBDIEG03JT17N4C89XPV-WRSYZ';

So the code to create our human-friendly order id is:

<?php

include 'encryption_class.php';

$crypt = new encryption_class();

$key = "A-COMPLETELY-RANDOM-KEY-THAT-I-HAVE-USED";
// Min length of 8 for encrypted string
$min_length = 8;

$order_id = 123456789;

print "Original: " . $order_id . PHP_EOL;

$encrypt_result = $crypt->encrypt($key, $order_id, $min_length); 

print "Encrypted: " . $encrypt_result . PHP_EOL;

// DECRYPT
$decrypt_result = $crypt->decrypt($key, $encrypt_result);

print "Decrypted: " . $decrypt_result . PHP_EOL;

?>

(You need to download and save the *encryption_class* file locally, and include it).

I ran that code from the command line and received the following output:

Original: 123456789
Encrypted: 2UD5UIK9S
Decrypted: 123456789

Now we have our short, human-friendly order_id, which can be used in a URL such as http://myapp.example.com/order/view/2UD5UIK9S, and you never need to display or communicate the internal order_id to your users.

Notes:

The encrypted code will be unique once your order_id is unique (since it's a PK it will be)

This should not be used as a password encryption/decryption routine - don't store passwords, store hashes.

Make sure your secret key is random, complex and contains only the characters in your $scramble variables.

It obfuscates the order_id only.

Edit:

Although padding the input string (order_id) generates a certain amount of ramdomness, you could combine this with @biakaveron's answer to create a URL like http://myapp.example.com/order/view/5cc46aea44e898c3b4e1303eb18d8161302cd367/2UD5UIK9S

e-shop implementation: Status for Orders?

If you can calculate it, you should calculate it. Otherwise you have redundant data and run the risk of having inconsitencies.

That's not to say you can't add a status column for performance reasons or to make querying easier, but start without it and make sure your authoritative data stays that way. Personally I prefer to stick with the calculate-on-query approach unless I can prove the performance isn't good enough.

smart way to generate unique random number

You could build a table with all the possible numbers in it, give the record a 'used' field.

1. Select all records that have not been 'used'
2. Pick a random number (r) between 1 and record count
3. Take record number r
4. Get your 'random value' from the record
5. Set the 'used' flag and update the db.

That should be more efficient than picking random numbers, querying the database and repeat until not found as that's just begging for an eternity for the last few values.

How to store complex product/order data in MySQL?

At the very least you need:

Products (one row per product)
    ProductID
    Size

Orders (one row per order)
    OrderID

OrderDetails (one row per product per order)
    ProductID
    OrderID
    Size

Note that each 'size' is its own ProductID. You'll probably want to have yet another ID that groups products that are the same 'base' product, but in different sizes.

So if Order #1 has three products, and Order #2 has four, then OrderDetails will have seven rows:

OrderID ProductID Quantity
1       234       2
1       345       9
1       456       30
2       432       1
2       234       65
2       654       8
2       987       4

---

Related Topics