CODEWITHSUNDEEP
databasepostgresql
Goodies
Goodies

Reputation: 4681

Proper Database Design with Foreign Keys

Because I've never designed a database, I wanted to make sure that the design I'm using, while simple, follows general idiomatic design patterns.

Essentially, a friend is making a discord bot that allows you to submit photos and have others rate them. Putting the obvious trolling opportunities aside, here are the data fields that are needed:

What I don't particularly like about this design is that it maintains two scores: a running total that will be divided by the total number of votes of that user, and each vote in particular.

My questions are:

  1. Is this design proper?
  2. Using this design, how can I ensure that each person can only vote for each url once?

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");

Upvotes: 3

Views: 468

Answers (2)

FuzzyChef
FuzzyChef

Reputation: 4071

Responding to the first part of the question, "Is this design proper", the short answer is "no".

  1. If discord_ids are unique, you do not need another ID column in members. The discord_id is the primary key of the members table.
  2. If Image URLs are unique, that could be the primary key of the Images table. That's really up to you; some people don't like using long text strings as keys. I'll assume you're one of them.
  3. The Votes table shouldn't have an ID column at all. It's a many-to-many join table. Your key there is (voter_id, image_id). This also has the effect of preventing members from voting more than once.
  4. The target_id column in votes is completely redundant, as that information already exists in the images table.
  5. Neither voter_id nor image_id in Votes should be Serial. Instead, they should be INT. Score, which is presumably a numeric score, should be NUMERIC or INT (I'll use INT since total_score is bigint).
  6. using mixed-case identifiers is generally a bad idea in SQL, as identifier (table) names are case-sensitive in strange ways.
  7. Limiting URLs to 64 characters seems shortsighted; do you have an application constraint here you need to match?
  8. You should add CASCADE to all of your foriegn keys, so that you can easily delete members or images.

As such, below is your revised schema:

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;

Upvotes: 1

Jeff Holt
Jeff Holt

Reputation: 3212

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

If we assume that you are not going to change the relations, their keys, and their non-key attributes, then all you need to do to satisfy #2 is to put a unique constraint on VOTES (VOTER_ID, IMAGE_URL).

Upvotes: 2

Related Questions