to N items capacity for a database

2018-06-12 05:52:51

I have a database where we cache various data, but it shouldn't grow too large, so I need to place a limit to how much items of the same type it holds, after which it should reject new items.

So as a simplified example if the database has 2 columns - COUNTRY and CITY , it should have no more than 100 cities from "Canada" and no more than 100 cities from "USA". If I already have 70 cities from "Canada" and want to add 35 more, I should add only 30 and then stop accepting new cities for "Canada" in the database.

Right now I'm thinking about querying the database with SELECT COUNT (*) WHERE COUNTRY=? each time I add an item and enforcing the policy that way.

Is there a better solution?

Several solutions come to mind. You seem to aim for keeping the first N entries, and the selec count(*) where country .. is probably the solution to this. You could assign counters to each entry for canada (1, 2,3 -- ) but this would lead to a rigid batch of data .. on MSSQL you could create a trigger of type "instead of", count occurrances and accept/reject based on the count. You could accept all input, store it in a data table, and only present the first 100 based on a query or a view with this query.

Ketil

Because there are only about 200 countries in the world, if you add 100 cities for each that will give you 20.000 records, which is not very much so you can also use the COUNT(*) for looking if you can add more cities, but if you want to add more than 100 cities per country, your solution won't scale very well (it will take more time, depending on the number of cities/country and if you have other columns in the table etc.)

In case you want to scale to more cities per country you could probably create an intermediate table where you store how many cities there are for every country and update a field in that table when you add cities to a country.

Also you could have the number of cities already added for a country, so you don't have to count them everytime you search for them.

So, step 1 :

Something like:

Country    Cities    MoreAllowed
Spain      95        1
USA        45        1
UK         100       0

And you could just query this table to see if you can still add more cities to a country.

Step 2:

In order to update this table you can create a trigger on INSERT for the table with cities and everytime you insert a city you update the number in this table.

By using this method you just have to run a simple

SELECT Cities, MoreAllowed
FROM countries_table
WHERE COUNTRY = --your_country--

and use the returned values in your application to determine if you add more cities and how many more.

I managed to do a solution using only triggers. In this example, I enforce a limit of up to 3 items per category, and I use timestamps to figure out which is the oldest and discard it.

CREATE TABLE mytable (category, item, timestamp);
CREATE TABLE mytable_counts (category UNIQUE, items_count);

CREATE TRIGGER update_mytable_counts AFTER INSERT ON mytable
BEGIN
    UPDATE mytable_counts
        SET items_count = items_count + 1
        WHERE category = NEW.category;
    INSERT OR IGNORE INTO mytable_counts VALUES(NEW.category, 1);
END;

CREATE TRIGGER enforce_limit AFTER UPDATE ON mytable_counts WHEN NEW.items_count > 3
BEGIN
    DELETE FROM mytable WHERE category=NEW.category AND timestamp NOT IN (SELECT timestamp FROM mytable WHERE category=NEW.category ORDER BY timestamp DESC LIMIT 3);
    UPDATE mytable_counts SET items_count = 3 WHERE ROWID=NEW.ROWID;
END;

I have not tried to optimize, the code, and I have no idea of how it will perform in bigger tables.

链接地址: http://www.djcxy.com/p/35040.html

上一篇: 不同的节点表示

下一篇: 数据库的N项容量