Prośba o ocenę schematu bazy danych prostej aplikacji

0

Jeżeli ktoś znajdzie chwilę, bardzo prosze o ocenę schematu bazy danych aplikacji. Jest to taki mini quiz, gdzie są również inne czynności dodatkowo punktowane. Poniżej zamieszczam sql widoków, których nie ma na schemacie. Dodatkowo do widoku user_answers jest podpięty trigger instead of insert, który wprowadza dane do user_question. Dziękuję za słowa krytyki.

CREATE VIEW user_answers AS
	SELECT
	    au.user_id,
	    au.answer_id,
	    au.question_id,
	    a.is_correct,
	    q.points
	FROM user_question au
	    INNER JOIN answers a ON au.answer_id = a.id
	    INNER JOIN questions q ON au.question_id = q.id

CREATE VIEW user_points AS
    SELECT
        user_id,
        'answered_question' AS action_type,
        points
    FROM user_answers
    WHERE is_correct = TRUE
    UNION
    SELECT
        user_id,
        name AS action_type,
        points
    FROM activities

CREATE MATERIALIZED VIEW ranking AS
    SELECT
        user_id,
        row_number()
        OVER (
            ORDER BY points DESC) AS ranking,
        points,
        0 AS change --będzie dociągane z tabeli audytowej
    FROM (
        SELECT
            user_id,
            sum(points) AS points
        FROM user_points
        GROUP BY user_id
        UNION
        SELECT
            id AS user_id,
            0  AS points
        FROM users
        ORDER BY user_id
    ) total;
0

Zauważyłem jedną dodatkową niespójność. Tabelka user_answer z diagramu to obecnie user_questions i wygląda prawie tak samo, tylko że ma dodatkowo question_id. Dokładnie wygląda to tak:

CREATE TABLE user_question (
  id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('user_question_id_seq'::regclass),
  user_id INTEGER NOT NULL,
  question_id INTEGER NOT NULL,
  answer_id INTEGER NOT NULL,
  created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL DEFAULT now(),
  FOREIGN KEY (answer_id) REFERENCES public.answers (id)
  MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES public.questions (id)
  MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES public.users (id)
  MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
);
CREATE UNIQUE INDEX user_question_user_id_question_id_unique ON user_question USING BTREE (user_id, question_id);
CREATE INDEX user_question_user_id_index ON user_question USING BTREE (user_id);
CREATE INDEX user_question_question_id_index ON user_question USING BTREE (question_id);
CREATE INDEX user_question_answer_id_index ON user_question USING BTREE (answer_id);
0

Czym się różni tabela 'permission', a 'role' ? Czy system, który będzie używał tej projektowanej bazy danych wymaga właśnie takiej konstrukcji ? Czy nie można byłoby osadzić to w ramach jednej tabeli ?

0

Ponieważ nie chciałem tracić czasu na implementowanie roli/uprawnień od nowa to wykorzystuję https://github.com/romanbican/roles. Autor zaproponował taki schemat bazy. Pozwala to nadawać konkretnym rolom uprawnienia, jak i dawać uprawnienia użytkownikom niezależnie od roli.

0

W tabelach intersekcji (answer_user, permission_user itd) nie potrzebujesz id.
To 2 klucze obce z tabel nadrzędnych wyznaczają i składają się na klucz główny. Przykładowo kluczem w tabeli answer_user jest answer_id i user_id. Analogicznie dla pozostałych takich tabel w schemacie.

1 użytkowników online, w tym zalogowanych: 0, gości: 1