simple discussion on ID allocation

[jasongr]

Hi People

I have a dilemma regarding the correct way I should allocate IDs for a new table I am designing.
The table holds information on folders that users can open in the system.
The following information is saved on each folder:

*) Folder ID
*) User ID (The ID of the user who created the folder
*) Name of the folder
... Possibly more data can be added later on

I am wondering what should be the best way to allocate IDs to the folders.
The primary key in the table is (Folder ID, User ID).
Each user can create as many folders as she wants.
When a user creates a folder, I need to add a new record to the table, and I am not sure how to allocate the ID for the folder.
I can use auto_increment for the folder ID, but would mean that if User X creates his first folder,
then the folder ID will be some number that MySQL will allocate and not the number 1.
What I WOULD like to do is to have MySQL treat the folder ID with auto_increment attributes within the user ID value.
So The first folder that each user will create will get the ID 1, the second 2 and so on.

Is this possible?
If not, what would be the best way to achieve what I want without running into duplication problems
(that I know auto_increment solves)

Thanks in advance for any help

Regards
Jason

[mattyblah]

Is there any particular reason why you need all users folders to be sequential to the user? what you are asking for is a maintenance nightmare. it's best to have an auto_incrementing field for folder id so that:

folder 1 user 1
folder 2 user 1
folder 3 user 2
folder 4 user 3
folder 5 user 1

That would be the easiest and simplest way to achieve what you want. Otherwise you'd have the following (in pseudo-code):

check if user has folders
if no folders, assign folder 1
else assign next sequential id

what if the user has 3 folders, folder 1, folder 2 and folder 3. If they delete folder 2, do you then reassign folder 2 or assign folder 4? just some stuff to think about.

[mdjo]

mySQL has a feature to do the auto_increment within a higher-level field for cases like this. See:

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Alternatively, you could check the highest value currently used and increment each time. Though this creates issues of 2 users getting the same value -- the whole advantage of using auto_increment is that you don't have to worry about locks and all that.