How to sort by date column if there are other clustering keys that ensure the uniqueness of the record

Question

I am writing a test project in the subject area "Password storage system".

create table users
(
    login               text primary key,
    access_token        text,
    access_token_expire timestamp,
    password            text
);

create table credentials
(
    user_login              text,
    resource_name           text,
    resource_login          text,
    changed_at              timestamp,
    created_at              timestamp,
    id                      uuid,
    password_security_level int,
    resource_password       text,
    primary key (user_login, resource_name, resource_login)
)

create table credentials_history
(
    credential_id     uuid,
    resource_password text,
    changed_at        timestamp,
    primary key (credential_id, changed_at)
);

The problem arises with the credentials table, the problem is that I chose the user_login, resource_name, resource_login fields as part of the primary key, because they will guarantee the uniqueness of a certain account on a certain resource for a certain user and I will be able to search by unique fields for the account, but I want these data to be sorted in descending order created_at, and then in the application to issue them in parts using pagination. The only option that came to my mind was to make two tables instead of one and name them credetials_unique & credentials_sorted_by_created_at, credetials_unique I will use as now credentials, that is, check before creating whether such a user account already exists before creating and guarantee uniqueness, and in the second table credentials_sorted_by_created_at I will insert everything the same, but already there will be PartitionKey(user_login) & ClusteringKey(created_at)

Tell me, please, better options, is this practiced at all, or is there a problem at the very beginning?

A similar story with credentials_history, in theory I will then have tables

table to check if such an account already exists (the name confuses me) credentials_by_resource_name_and_resource_login PK((user_login), resource_name, resource_login)

table to display information to the user in sorted order. credentials PK((user_login), created_at)

table to check if such a password has already been used on such an account credentials_history_by_password PK((credential_id), resource_password)

table to display information to the user in sorted order. credentials_history PK((credential_id), changed_at)

Answer 1

You include created_at as part of the clustering keys like below if you think that will guarantee/enhance you the uniqueness of the record in that table,

CREATE TABLE IF NOT EXISTS
...
PRIMARY KEY((user_login), resource_name, resource_login, created_at)
WITH CLUSTERING ORDER BY(resource_name ASC, resource_login ASC, created_at DESC)
...

Another option is to create a materialized view like as follows:

--Source Table--
CREATE TABLE test.so_231206 (
    a text,
    b text,
    c text,
    d timestamp,
    PRIMARY KEY (a, b, c)
) WITH CLUSTERING ORDER BY (b ASC, c ASC);

and the MV will look like,

--MV--
CREATE MATERIALIZED VIEW test.so_231206_mv AS
    SELECT a, b, c, d
    FROM test.so_231206
    WHERE a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL AND d IS NOT NULL
    PRIMARY KEY (a, b, c, d)
    WITH CLUSTERING ORDER BY (b ASC, c ASC, d DESC);

and you'll only write into the source table,

cqlsh:test> insert into so_231206 (a,b,c,d) VALUES ( '1','1','1',totimestamp(now()));
cqlsh:test> insert into so_231206 (a,b,c,d) VALUES ( '2','2','2',totimestamp(now()));

reading from source:

cqlsh:test> select * from so_231206;

 a | b | c | d
---+---+---+---------------------------------
 2 | 2 | 2 | 2023-12-07 13:07:25.067000+0000
 1 | 1 | 1 | 2023-12-07 13:06:53.822000+0000

(2 rows)

now, reading from the MV:

cqlsh:test> select * from so_231206_mv ;

 a | b | c | d
---+---+---+---------------------------------
 2 | 2 | 2 | 2023-12-07 13:07:25.067000+0000
 1 | 1 | 1 | 2023-12-07 13:06:53.822000+0000

(2 rows)

This will be working as below even if your source table has d with null value:

cqlsh:test> insert into so_231206 (a,b,c) VALUES ('3','3','3');
cqlsh:test> select * from so_231206;

 a | b | c | d
---+---+---+---------------------------------
 3 | 3 | 3 |                            null
 2 | 2 | 2 | 2023-12-07 13:07:25.067000+0000
 1 | 1 | 1 | 2023-12-07 13:06:53.822000+0000

(3 rows)
cqlsh:test> select * from so_231206_mv ;

 a | b | c | d
---+---+---+---------------------------------
 2 | 2 | 2 | 2023-12-07 13:07:25.067000+0000
 1 | 1 | 1 | 2023-12-07 13:06:53.822000+0000

(2 rows)

As you can see, it is automatically ordered by d with descending order as we've stated it to do in the schema creation phase.