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

Question

How I would remove the last item from a table by selecting the last four.

Ex:

+-----------------+--------+
+------ ITEM -----+-VALOR--+
+--------------------------+

1: FulanoA        + está online
2: FulanoA        + está ausente
3: FulanoB        + se cadastrou
4: FulanoC        + não está

based on this mini table I wanted to add an item from it by removing the latter and adding another one.

Ex:

5: FulanoZ       + entrou

and where Fulanoc is to be removed and inserted above Fulanoa.

I wanted to use simple and/or simplified codes.

Answer 1

Best way to create a dynamic of this is through a database control, where each change of user status would be recorded the status, which user belongs and the date of the 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 database. In this database will be recorded each change of user status, along with the date that will be filled automatically with CURRENT_TIMESTAMP.

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

With each status drive in your application you will generate an Insert query for this table:

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

This way will be registered status change FulanoA for está online.

To recover these status just run a query in this table, searching for the last status drives ordernando by 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, will be output the way you want.

With each new status change the last record will leave the table and a new one will enter at the beginning of it, same game that demonstrated in the example with FulanoZ.

Considerations

I will make some considerations taking into account this modeling.

Database

It’s a little expensive to performance these constant hit’s in the bank, with each change you register and show, imagine this on a large scale. Cost of I/O will be high for your application. Imagine this in on-demand service?

So what better way out?

Analysis of the need for this interaction, for what purpose and value of this functionality x its cost.

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

Alternatives

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

We also have the 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. Being a very easy way to save data like user cache, or task queues.

Which is the most advisable for this task, which is precisely noSQL.

Well, I owe a little more code, since this doubt is not very much related to how to do and yes how it works. All alternatives addressed here can be made in several languages, depends on the analysis of your project and demand.

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

Well, I hope you’ve cleared something up.

Answer 2

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