Dynamically generate short URLs for a SQL database?

Question

My client has database of over 400,000 customers. Each customer is assigned a GUID. He wants me to select all the records, create a dynamic "short URL" which includes this GUID as a parameter. Then save this short url to a field on each clients record.

The first question I have is do any of the URL shortening sites allow you to programatically create short urls on the fly like this?

Uvarovite

Would your client be satisfied with redirecting his hits through one of the public shorteners (goo.gl, bit.ly, etc...)? I don't think they'd let you generate a shortened URL via their service that doesn't use them as the redirector. Any reason you can't implement your own shortening algorithm and run a short-redirector on the client's site instead? – Cimah

They might also object to being hit 400,000 times in a bulk process! If you did it gracefully and did one a second that would take over 4.5 days continuously, but doing it faster would be "unfriendly". – Pteryla

Answer 1

TinyUrl allow you to do it (not widely documented), for example:

http://tinyurl.com/api-create.php?url=http://www.stackoverflow.com/

becomes http://tinyurl.com/6fqmtu

So you could have

http://tinyurl.com/api-create.php?url=http://mysite.com/user/xxxx-xxxx-xxxx-xxxx

to http://tinyurl.com/64dva66.

The guid doesn't end up being that clear, but the URLs should be unique

Note that you'd have to pass this through an HTTPWebRequest and get the response.

Pteryla

Answer 2

You can use Google's URL shortner, they have an API.

Here is the docs for that: http://code.google.com/apis/urlshortener/v1/getting_started.html

Lair

Answer 3

This URL is not sufficiently short:?

http://www.clientsdomain.com/?customer=267E7DDD-8D01-4F38-A3D8-DCBAA2179609

NOTE: Personally I think your client is asking for something strange. By asking you to create a URL field on each customer record (which will be based on the Customer's GUID through a deterministic algorithm) he is in fact essentially asking you to denormalize the database.

Onieonion

Answer 4

The algorithm URL shortening sites use is very simple:

1. Store the URL and map it to it's sequence number.
2. Convert the sequence number (id) to a fixed-length string.

Using just six lowercase letter for the second step will give you many more (24^6) combinations that the current application needs, and there's nothing preventing the use of a larger sequence at some point in time. You can use shorter sequences if you allow for numbers and/or uppercase letters.

The algorithm for the conversion is a base conversion (like when converting to hex), padding with whatever symbol represents zero. This is some Python code for the conversion:

LOWER = [chr(x + ord('a')) for x in range(25)]
DIGITS = [chr(x + ord('0')) for x in range(10)]
MAP = DIGITS + LOWER

def i2text(i, l):
        n = len(MAP)
        result = ''
        while i != 0:
                c = i % n
                result += MAP[c]
                i //= n
        padding = MAP[0]*l
        return (padding+result)[-l:]

print i2text(0,4)
print i2text(1,4)
print i2text(12,4)
print i2text(36,4)
print i2text(400000,4)
print i2text(1600000,4)

Results:

0000
0001
000c
0011
kib9
4b21

Your URLs would then be of the form http://mydomain.com/myapp/short/kib9.

Jaynejaynell