Numbering Items in Lists - The Best Approach?

Question

I'm creating a simple set of tables to store a number of lists. The first table, describes the lists that I have and second describes items on those lists - the tables are defined like so:

CREATE TABLE `listman_list` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(100) NOT NULL,
    `description` varchar(1000) NOT NULL,
    `id_counter` integer UNSIGNED NOT NULL
)

CREATE TABLE `listman_listitem` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `number` integer UNSIGNED NOT NULL,
    `title` varchar(100) NOT NULL,
    `description` varchar(10000) NOT NULL,
    `list_id` integer NOT NULL,
)

Each listitem has the number field which describes its unique number relative to the list. This is to enable listitems to be uniquely numbered for the list (but not globally, this numbering is distinct from its primary key). E.g. so I can have list one with items 1,2,3 annd list two with items 1,2,3 etc.

Now, what is the best way to number the items in the second table? At the moment I have a pair of triggers:

CREATE TRIGGER set_listitem_number
BEFORE INSERT ON listman_listitem
FOR EACH ROW
    SET NEW.number = (SELECT DISTINCT id_counter FROM listman_list id=NEW.list_id);


CREATE TRIGGER inc_listcounter
AFTER INSERT ON listman_listitem 
FOR EACH ROW
    UPDATE listman_list SET id_counter = id_counter+1 WHERE id=NEW.list_id;

The first sets the list item number from the list table, prior to insertion whilst the second increments the counter in the list table after it has been successfully inserted (I heard that modifying other tables before insertion had some undesirable consequences with MySQL if the transaction failed - as the before triggers would not be undone).

Is this a good way to do it - or should I do it under a transaction in the application code manually, or should I do it some other way entirely?

Thanks for your help - I may be fussing about trivia here, but I don't have a lot of experience with databases.

Answer 1

I don't understand your queries in the triggers. Don't you want to find the highest existing number (within a list) and increment it by one? I don't know the ins and outs of MySQL syntax, but this does not appear what you are doing. I would expect to see a Max() or Count() in this query.

Aside from that, I'd imagine that there will be a requirement for inserting new items at positions other than the last one, meaning that all the items need to be resorted. The trigger can't do that, so I recommend that you handle this in the application.

The problem of maintaining the sort order was discussed before, e.g. here.

Answer 2

What is wrong with the ID column -- the server takes care of incrementing these and making them unique.

Answer 3

Get rid of the id_counter column, it is just another piece of information to worry about keeping in sync. Get rid of the triggers as well, and perform all inserts through a stored proc. I'm pasting in some code written in T-SQL, so it may need slight changes in syntax to work in MySQL but it conveys the general idea.

CREATE PROC insert_listItem(@list_id int, @title varchar(100), @description varchar(1000))
AS 
INSERT listman_listitem(number, title, description, list_id)  
   SELECT COALESCE(MAX(number), 0) + 1,
   @title, 
   @description, 
   @list_id
FROM 
    listman_listitem 
WHERE 
    list_id = @list_id

This will find the greatest number that exists for the given list_id (or zero if none exist), increment it by one, and stuff that value into the new record.