OK I have hit an issue, now I was going to look at having two auto incrementing columns, but I cant using InnoDB. So I am after a Solution.

I have a table called queue. Everything to do with my task queue should go into it, But each time something happens with a task I want it to write a new entry, so I can see what has been happening with my tasks. But I also Need a Primary ID for each entry, and a different key for the Tasks

So any suggestions, in how I might go about conquering this small issue.

CREATE TABLE IF NOT EXISTS `Queue` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TaskID` int(11) NOT NULL,
`Task` varchar(255) NOT NULL,
`Client` varchar(30) NOT NULL,
`AddedInfo` varchar(255) NOT NULL,
`Status` varchar(20) NOT NULL,
`Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
);
  • Is this table using the MyISAM Storage Engine ??? – RolandoMySQLDBA Jun 17 '14 at 18:53
up vote 0 down vote accepted

If the Queue table is MyISAM, I have just the thing for you.

OPTION #1

First, you should store task names in its own table

CREATE TABLE IF NOT EXISTS `Tasks` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Task` varchar(255) NOT NULL,
    PRIMARY KEY (`ID`).
    UNIQUE KEY (`Task`)
) ENGINE=MyISAM;

Then, remove Task and TaskID from Queue and change the primary key to (TaskID,ID)

CREATE TABLE IF NOT EXISTS `Queue` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `TaskID` int(11) NOT NULL,
    `Client` varchar(30) NOT NULL,
    `AddedInfo` varchar(255) NOT NULL,
    `Status` varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`TaskID`,`ID`)
) ENGINE=MyISAM;

Then, you would do something like this each time

INSERT IGNORE INTO Tasks (Task) VALUES ('Mow the Lawn');
SELECT ID INTO @GivenTaskID FROM Tasks WHERE Task = 'Mow the Lawn';
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Valued Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Forgot to Pay','Suspended');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Has Not Paid in 90 Days','Deadbeat');
INSERT IGNORE INTO Tasks (Task) VALUES ('Plant New Flowers');
SELECT ID INTO @GivenTaskID FROM Tasks WHERE Task = 'Plant New Flowers';
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Pamela Edwards','First Time Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Pamela Edwards','Made First Payment','Active');

OPTION #2

If you do not want to introduce a new table for tasks then redo the table like this

CREATE TABLE IF NOT EXISTS `Queue` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Task` varchar(255) NOT NULL,
    `Client` varchar(30) NOT NULL,
    `AddedInfo` varchar(255) NOT NULL,
    `Status` varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`Task`,`ID`)
) ENGINE=MyISAM;

Then, you would do something like this each time

INSERT INTO Queue (Task,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Valued Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Forgot to Pay','Suspended');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Has Not Paid in 90 Days','Deadbeat');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Plant New Flowers','Pamela Edwards','First Time Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Plant New Flowers','Pamela Edwards','Made First Payment','Active');

EPILOGUE

In both cases, you will have ID of 3 for 'Mow the Lawn' and an ID of 2 for 'Plant New Flowers'

I have done this before. See my posts

GIVE IT A TRY !!!

  • Looks like I have to re look at this. Having the two tables might be the easiest way to do this. – user41198 Jun 17 '14 at 19:16

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.