Clever Geek Handbook

Correct database design using foreign keys

Since I have never designed a database, I wanted to make sure that the design I am using, while simple, follows common idiomatic design patterns.

Basically, a friend creates a Discord bot that lets you submit photos while others rate them. Introducing the obvious trolling capabilities, here are the data fields that are required:

  • Discord ID (unique ID for each person using discord)
  • List of URLs (each URL is tied to a specific chasm member)
  • Votes list (each vote has a score, voter ID and URL).

What I particularly dislike about this project is that it supports two accounts: the total, which will be divided by the total of this user's votes, and each vote in particular.

My questions:

  • Is this construction correct?
  • Using this design, how can I ensure that each person can only vote on each URL?

Database design

https://dbdesigner.net output:

CREATE TABLE "Members" (
    "id" serial NOT NULL,
    "discord_id" bigint NOT NULL,
    "total_score" bigint NOT NULL,
    "total_votes" bigint NOT NULL,
    CONSTRAINT Members_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);

CREATE TABLE "Images" (
    "id" serial NOT NULL,
    "url" TEXT(64) NOT NULL,
    "member_id" bigint NOT NULL,
    CONSTRAINT Images_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);

CREATE TABLE "Votes" (
    "id" serial NOT NULL,
    "voter_id" serial NOT NULL,
    "target_id" serial NOT NULL,
    "score" serial NOT NULL,
    "image_id" serial NOT NULL,
    CONSTRAINT Votes_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);


ALTER TABLE "Images" ADD CONSTRAINT "Images_fk0" FOREIGN KEY ("member_id") REFERENCES "Members"("discord_id");

ALTER TABLE "Votes" ADD CONSTRAINT "Votes_fk0" FOREIGN KEY ("voter_id") REFERENCES "Members"("discord_id");
ALTER TABLE "Votes" ADD CONSTRAINT "Votes_fk1" FOREIGN KEY ("target_id") REFERENCES "Members"("discord_id");
ALTER TABLE "Votes" ADD CONSTRAINT "Votes_fk2" FOREIGN KEY ("image_id") REFERENCES "Images"("id");
+3


source to share


2 answers


Since I cannot see the foreign key references, and I cannot see your code (i.e. the SQL statements), I cannot know for sure if your synthetic keys are a good idea. But at first glance it seems that your real key for VOTES is (VOTER_ID, IMAGE_URL).



Assuming you are not going to change the relationship, their keys and their non-key attributes, then all you have to do to satisfy # 2 is to set a unique constraint on VOTES (VOTER_ID, IMAGE_URL).

+2


source


Answering the first part of the question: "Is this construction correct", the short answer is "no".

  • If the discord_ids are unique, you don't need a different member ID. Discord_id is the primary key of the members table.
  • If the image URLs are unique, this could be the primary key in the Images table. It really depends on you; some people don't like using long text strings as keys. I assume you are one of them.
  • The "Votes" table should not have an "ID" column. This is a table with many to many. Your key is (voter_id, image_id). It also prevents members from voting more than once.
  • The target_id column in votes is completely redundant as this information already exists in the image table.
  • Neither voter_id nor image_id in votes should be serial. Instead, they should be INT. The score, which is presumably a numeric score, should be NUMERIC or INT (I'll use INT since total_score is bigint).
  • using IDs in mixed code is generally a bad idea in SQL as ID (table) names are case sensitive in weird ways.
  • Limiting URLs to 64 characters seems short-sighted; do you have an app constraint that you need to map?
  • You have to add CASCADE to all your foriegn keys so you can remove members or images easily.


So your revised schema is below:

CREATE TABLE "members" (
    "discord_id" bigint NOT NULL,
    "total_score" bigint NOT NULL,
    "total_votes" bigint NOT NULL,
    CONSTRAINT members_pk PRIMARY KEY ("discord_id")
);

CREATE TABLE "images" (
    "id" serial NOT NULL,
    "url" VARCHAR(64) NOT NULL,
    "discord_id" BIGINT NOT NULL,
    CONSTRAINT images_pk PRIMARY KEY ("id"),
    CONSTRAINT images_url UNIQUE ("url")
);

CREATE TABLE "votes" (
    "voter_id" INT NOT NULL,
    "image_id" INT NOT NULL,
    "score" INT NOT NULL,
    CONSTRAINT votes_pk PRIMARY KEY (voter_id, image_id)
);

ALTER TABLE "images" ADD CONSTRAINT "images_fk0" 
FOREIGN KEY ("discord_id") REFERENCES "members"("discord_id")
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "votes" ADD CONSTRAINT "votes_fk0" 
FOREIGN KEY ("voter_id") REFERENCES "members"("discord_id")
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "votes" ADD CONSTRAINT "votes_fk2" 
FOREIGN KEY ("image_id") REFERENCES "images"("id")
ON DELETE CASCADE ON UPDATE CASCADE;
+1


source






More articles:


All Articles