Remove last item from table and reset a new item above first

Best way to create a dynamic of this is through a database control, where every change of user status would be recorded status, which user belongs and the date of change.

An example of a bank schema:

CREATE TABLE IF NOT EXISTS 'user_status' (
 'id' INT NOT NULL AUTO_INCREMENT COMMENT '',
 'name' VARCHAR(45) NOT NULL COMMENT '',
 'status' VARCHAR(45) NULL COMMENT '',
 'created_at' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
PRIMARY KEY ('id')  COMMENT '')
ENGINE = InnoDB

Okay, we set up the bank. This bank will record each change of user status, along with the date that will automatically be filled with CURRENT_TIMESTAMP .

  

Remembering that in the environment of your application you will need to create a foreign key for ID of the user. Referencing the registry to it.

Each status move in your application will generate an insert query for this table:

INSERT INTO 'user_status' ('name', 'status') VALUES ('FulanoA', 'está online')

In this way, status change from FulanoA to está online will be registered.

To retrieve these statuses, just run a query in this table, searching for the latest status moves by entering the field created_at ;

SELECT 'us'.'name', 'us'.'status' FROM 'user_status' us us ORDER BY 'us'.'created_at' DESC LIMIT 4

This query will return the last 4 records ordered by the change date, it will output the way you want.

With each new status change the last record will exit the table and a new one will enter the beginning of it, even a little game that showed in the example with FulanoZ .

Considerations

I'll make some considerations with this modeling in mind.

Database

It's a bit costly to perform these constant hits on the bank, with every change you record and show, imagine this on a grand scale. Cost of I / O will be high for your application. Imagine this on on-demand service?

So what's the best way out?

Analyze the need for this interaction, for which purpose and value of this functionality x cost it.

You need to analyze and see if the demand is worth the performance cost, just to show history of moving users status.

Alternatives

Working with these records in a bank is not recommended, but if you want to use a bank for these transactions I recommend the noSQL which will have the lowest cost for these frequent queries.

We also have Redis

  

Redis is a NoSQL database that works with the idea of key value. The key as well as its value can be almost anything like a Hash in Ruby or a Map in Java. This is a very easy way to save data such as the user's cache, or task queues.

Which is most advisable for this task, which is just noSQL .

Well, I owe a little more code, as this question is not very much about how to and how it works . All alternatives covered here can be done in several languages, depending on the analysis of your project and demand.

I recommend studying the alternatives put here, as this is really something that will eat your performance with flour if not well modeled.

Well, I hope you have clarified something.

The best thing to do is to create a 'dataCadastro' column and delete the SQL form Delete form table where dataCadastro = min (dataCadastro)