How do I assign a rotating category to database entries in the order the records come in?

Question

I have a table which gets entries from a website, and as those entries go into the database, they need to be assigned the next category on a list of categories that may be changed at any time.

Because of this reason I can't do something simple like for mapping the first category of 5 to IDs 1, 6, 11, 16.

I've considered reading in the list of currently possibly categories, and checking the value of the last one inserted, and then giving the new record the next category, but I imagine if two requests come in at the same moment, I could potentially assign them both the same category rather then in sequence.

So, my current round of thinking is the following:

• lock the tables ( categories and records )
• insert the newest row into records
• get the newest row's ID
• select the row previous to the insertl ( by using order by auto_inc_name desc 0, 1 )
• take the previous row's category, and grab the next one from the cat list
• update the new inserted row
• unlock the table

I'm not 100% sure this will work right, and there's possibly a much easier way to do it, so I'm asking:

A. Will this work as I described in the original problem? B. Do you have a better/easier way to do this?

Thanks ~

Answer 1

I would do it way simpler... just make a table with one entry, "last_category" (unsigned tinyint not_null). Every time you do an insert just increment that value, and reset as necessary.

Answer 2

I'm not sure I understand your problem, but as I understand it you would like to have something like

category | data
-----------------
0        | lorem
1        | ipsum
....     | ...
4        | dolor
0        | sit
...      | ...

How about having a unique auto_increment column, and let category be the MOD 5 of this column?

Answer 3

If you need 100% correct behaviour it sounds like you will need to lock something somewhere so that all your inserts line up properly. You might be able to avoid locking the category table if you use a single SQL statement to insert your data. I'm not sure how MySQL differs but in Oracle I can do this:

insert into my_table (id, col1, col2, category_id)
select :1, :2, :3, :4, c.id -- :1, :2, etc are bind variables. :1 corresponds to the ID.
from 
  (select 
      id, -- category id
      count(*) over (partition by 1) cnt, -- count of how many categories there are
      row_number() over (partition by 1 order by category.id) rn -- row number for current row in result set
   from category) c
where c.rn = mod(:1, cnt)

This way in one statement I insert the next record based on the categories that existed at that moment. The insert automatically locks the my_table table until you commit. It grabs the category based on the modulus of the ID. This link shows you how to do a row-number in mysql. I'm not sure if count(*) requires group by in mysql; in oracle it does so I used a partition instead to count the whole result set.