Define an order for a sqlite table?

Question

I'm currently creating a sqlite database in which I need to define a ranking system. Each rank must have a unique position in the hierarchy. The first approach I thought of was to create a position attribute that indicates the position of the rank like this:

CREATE TABLE IF NOT EXISTS ranks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    position INTEGER NOT NULL UNIQUE,
)

The problem is that if I ever want to insert a grade in the middle of the list, I have to redefine the position of all the grades above it.

After some research, I came across other solutions that created a parent_id attribute which is a foreign key that references id and seemed to retrieve the data with "recursive queries", but I don't know if this type of approach is good in this case.

Do you know the best way to do this?

Answer 1

POSITION AS FLOAT:

CREATE TABLE IF NOT EXISTS ranks_real (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    position REAL
);
DELETE FROM ranks_real;
INSERT INTO ranks_real (name, position) values ('ONE', 1);
INSERT INTO ranks_real (name, position) values ('TWO', 2);
INSERT INTO ranks_real (name, position) values ('BETWEEN ONE AND TOO', (1+2)/2.0);
SELECT * FROM ranks_real ORDER BY position;

POSITION/SUBPOSITION doesn't work in the same way, because you have to reorder the subposition- values, if you want to insert between rank 11/1 and 11/2 - so the real- field is the easiest way

TEXT You could even use text fields for this in the way as binary trees work - 0 for smaller, 1 for higher, but its mor complicated and if you have many items there can be really long texts.

CREATE TABLE IF NOT EXISTS ranks_text (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    position TEXT
);
DELETE FROM ranks_two_indexes;
INSERT INTO ranks_two_indexes (name, position) values ('A', 'R0');
INSERT INTO ranks_two_indexes (name, position) values ('Z', 'R1');
INSERT INTO ranks_two_indexes (name, position) values ('M', 'R01');
INSERT INTO ranks_two_indexes (name, position) values ('G', 'R001');
INSERT INTO ranks_two_indexes (name, position) values ('D', 'R0001');
INSERT INTO ranks_two_indexes (name, position) values ('P', 'R011');
INSERT INTO ranks_two_indexes (name, position) values ('N', 'R0101');
SELECT * FROM ranks_two_indexes ORDER BY position