Senior

Condition There are 3 entities - user, chat, message • User has a name and registration date • Chat has a name and creation date • Message has text, author, and creation date • A user can be in multiple chats simultaneously • A message must belong to a chat, and cannot belong to more than one chat at the same time • Describe the domain in the form of tables -- distributed -- message_count into chats limit 100 --getShard(time zone) -> shard_id -> 1 --getShard(time zone) -> shard_id -> 2 --shard1 [1..100] --shard2 [101..200] CREATE TABLE users ( id int PRIMARY KEY, name varchar(255) NOT NULL, reg_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE chats ( id int PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW(), message_count int NOT NULL CHECK <= 100 ); CREATE TABLE user_chats ( user_id int not null REFERENCES users(id), chat_id int NOT NULL REFERENCES chats(id), PRIMARY KEY (user_id, chat_id) ); CREATE TABLE messages ( id int PRIMARY KEY, chat_id int NOT NULL REFERENCES chats(id), author_id int NOT NULL REFERENCES users(id), created_at TIMESTAMP NOT NULL DEFAULT NOW(), text TEXT NOT NULL );

sobes.tech AI

Answer from AI

In the described domain, there are three main entities: users, chats, and messages. The relationships between them:

A user can be part of multiple chats (many-to-many).
A message belongs to exactly one chat.
A message has an author — a user.

Proposed table schema:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    reg_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE chats (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    message_count INT NOT NULL CHECK (message_count <= 100)
);

CREATE TABLE user_chats (
    user_id INT NOT NULL REFERENCES users(id),
    chat_id INT NOT NULL REFERENCES chats(id),
    PRIMARY KEY (user_id, chat_id)
);

CREATE TABLE messages (
    id INT PRIMARY KEY,
    chat_id INT NOT NULL REFERENCES chats(id),
    author_id INT NOT NULL REFERENCES users(id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    text TEXT NOT NULL
);

For distributed storage with sharding by time (e.g., by time zone), a function getShard(time_zone) can be implemented, which returns a shard_id (e.g., 1 or 2). Each shard covers a range of chat IDs:

shard1: chats with IDs from 1 to 100
shard2: chats with IDs from 101 to 200

This allows load distribution and storing data in different databases or clusters.

It is important to control message_count in the chats table to not exceed the limit of 100 messages, which may be related to business logic or shard limitations.

Previous Next