Proper Database Design with Foreign Keys

Question

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:

Discord ID (unique ID for every person using discord)
List of URLs (each URL is tied to a certain discord member)
List of Votes (each vote has a score, the voter ID, and the URL)

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:

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

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

Jeff Holt · Accepted Answer · 2017-06-30 18:01:42Z

2

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).

answered Jun 30, 2017 at 18:01

Jeff Holt

3,2623 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Goodies Over a year ago

If I change this to PostgreSQL, the foreign keys are shown. Would that be better? Database is simple enough to where the type really doesn't matter.

Nick Over a year ago

@jeff6times7 talks here not about FKs, but rather about synthetic PKs (numeric IDs) -- think about what is the proper "real key" in all your tables, what must be unique? Can a user vote for the same image twice? If not, you need to use (voter_id, image_id) pair as a PK, or if you prefer to keep synthetic PKs, you need to add UK (using create unique index ... using btree(voter_id, image_id);). Lack of such UKs is very common mistake when people choose to work with numeric synthetic PKs.

Nick Over a year ago

Also, if you go with synthetic PKs, continue asking this question (what is my real PK? what must be unique here?) for each table you create. E.g.: should images.url be unique? Or (member_id, lower(url)) pair in that table?

Jeff Holt Over a year ago

@Goodies Rather than trying to do a big upfront design, I would strongly recommend you use an iterative approach to software development such that each iteration includes 1) develop new feature, 2) test/trace code with new feature, 3) bless/rebuke the functional spec, and 4) bless/rebuke the performance spec. We just simply cannot guarantee we get anything correct on the first pass so we'll have to create infrastructure to cover the fact that we miss. Overall, I think you have enough design to proceed with your first iteration.

FuzzyChef · Accepted Answer · 2017-06-30 22:58:20Z

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

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.
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.
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.
The target_id column in votes is completely redundant, as that information already exists in the images table.
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).
using mixed-case identifiers is generally a bad idea in SQL, as identifier (table) names are case-sensitive in strange ways.
Limiting URLs to 64 characters seems shortsighted; do you have an application constraint here you need to match?
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;

Now, for the advanced class: write a trigger which automatically maintains total_score and total_votes.

Collectives™ on Stack Overflow

Proper Database Design with Foreign Keys

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related