How do I implement a database/table as a stack

I have a state-machine which needs to push/pop some file-names for different users. I would traditionally use stacks as the choice of data structure, but this needs to be done using a database since I don't have a way to retain the data structure between incoming web-requests.

I was wondering what would be a good way to implement the stack functionality using databases ?

I need to support:

• push(fileName, user) : push a fileName for the user

• pop(user) : Pop the top-most fileName for the user

EDIT:

I am prototyping an idea, and so I am using sqlite3 with python.

Thanks!

If you're asking about which database to use, it really depends on personal preference and what you want out of it. As I'm only familiar with MySQL, I'll answer the other part of the question assuming MySQL:

you will want to use INNODB because your table is going to be write-intensive and for large tables, the row-locking of INNODB will be a life-saver over MyISAM.

As far as table design, it seems you only really need one table:

CREATE TABLE `wordpress`.`` (
`id` smallint(4) NOT NULL AUTO_INCREMENT UNSIGNED,
`user` varchar(30) NOT NULL,
`filename` varchar(255) NOT NULL,
`date_insert` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE `userFile`(user, filename)
) ENGINE=`InnoDB`;

I went with an arbitrary 'id' column set to AUTO_INCREMENT because the primary key is replicated in every entry of every index. So, doing a primary key of (user, filename) could cause performance issues if your filenames are extremely long.

The size of your 'id' column depends on how big your table is going to grow. Unsigned Smallint will give you 65k rows.

User and filenames are varchar, because they'll vary on length drastically I assume.

The date_insert is just a way to order your results based on when it was inserted (helpful for your POP)

StackExchange Database Administrators Q#1410, answer score: 6