MySQL BigInt ID Generation and Friendly URLs

On my quest to find the best way to generate a numerical BigInt id for my MySQL primary key rows for some of my tables. I don't want to rely on the internal clock. I DO care about how the URL of my site will look like look and I don't want to put GUID in the URL.


Let's assume that I have the following tables:

- BOXES
- ITEMS
- USERS

Each user can have several boxes. Each box can have several items. The ITEMS table has a FK to BOXES (BOX_ID).

What I thought about is to use compound key (BIGINT) as primary key that takes into account both the user ID (INT) and a Counter for the specific user.In the URL put the `USERNAME_ID (INT)` and append the TOTAL_NUMBER_OF_BOXES which I save in the table (how many boxes the user have - need to make a select first to get that number before insert) to that number at the end. I will use this number in the URL, for example:

    http://mywebsite.com/boxes/214748364723

2147483647 is the user Id (auto incremented in the table) and 23 is the total number of boxes (INT).

When I read the URL, I extract the number and query the BOX table by that number.

What I'm trying to achieve is to use numerical BigInt primary keys, making them unique and take advantage of some of the cumulative user data that I store in the user's for that.

I wanted to know what do you think of such solution. **Another option** is create that number not as primary key, but as a regular columns with index. So I can use GUID as the primary key, but query the data based on other numerical value which I use in the URL.

So my main focus is to find a solution that will help me scale the database later on by using non auto-incremental fields, use GUID instead, not rely on internal clock (invites problems), but at the same time create a URL with numerical values instead of characters that I can extract and query the database using that data.

I want to here your opinion about that and any suggestions or improvements will be welcomes.

I wanted to use a numerical BigInt sequential id like using uuid_short(), but I am afraid of relying on the internal clock to generate and Id.

My application will be hosted on Amazon EC2 and I use MYSQL with ASP.NET 4.5. Thanks.