Creating SQL Tables for a Game's Leaderboard-CodePudding

Newbie here having trouble wiring up some tables for a game's leaderboard. Would appreciate help! I have 3 tables; here's an ER diagram:

   ┌─────┐                       ┌─────┐
   │ IPs ├─||─────────────────|<─┤Names│
   └──┬──┘                       └──┬──┘
      │                             │
      │                             │
      │                             │
      │                             │
      │                             │
      │         ┌──────┐            │
      └─||───O<─┤Scores├─|O──────||─┘
                └──────┘

Basically, I'd like to have each IP have multiple names, and each score map to an IP's name.

However, I run into this error

ERROR:  there is no unique constraint matching given keys for referenced table "names"

when I try:

CREATE TABLE IPs(
    ip CIDR NOT NULL,
    PRIMARY KEY(ip)
);
    
CREATE TABLE Names(
    name VARCHAR(25) NOT NULL,
    ip CIDR NOT NULL,
    CONSTRAINT ip_fk FOREIGN KEY(ip) REFERENCES IPs(ip), 
    PRIMARY KEY(name, ip)
);

CREATE TABLE Scores(
    score INT NOT NULL,
    name VARCHAR(25) NOT NULL,
    ip CIDR NOT NULL,
    created_at TIMESTAMP NOT NULL,
    CONSTRAINT name_fk FOREIGN KEY(name) REFERENCES Names(name), 
    CONSTRAINT ip_fk FOREIGN KEY(ip) REFERENCES IPs(ip), 
    PRIMARY KEY(score, name, ip)
);

How should I wire these tables up?

CodePudding user response：

You are trying to reference non-unique columns. From the Scores table, you are referencing both name and ip from the Names table, which combined are unique, but you are referencing them separately.

I would highly recomend adding autoincrement identifiers to each table instead of using a combination of every single column as primary key.

Primary keys are suposed to be a light short unique value that identifies a full row, using the full row as primary key has bad performance and makes foreign key references unnecesarily complex, producing a lot of duplicated data in your database.

CREATE TABLE IPs(
    id INT NOT NULL AUTO_INCREMENT,
    ip CIDR NOT NULL,
    PRIMARY KEY(id)
);
    
CREATE TABLE Names(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    ip INT NOT NULL,
    CONSTRAINT ip_fk FOREIGN KEY(ip) REFERENCES IPs(id), 
    PRIMARY KEY(id)
);

In the Scores table, it is not necesary to add a reference to the IPs table, it is enough to reference the Names table, because the Names table already contains the IPs reference.

CREATE TABLE Scores(
    id INT NOT NULL AUTO_INCREMENT,
    score INT NOT NULL,
    name INT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    CONSTRAINT name_fk FOREIGN KEY(name) REFERENCES Names(id), 
    PRIMARY KEY(id)
);

CodePudding user response：

I also found a possible solution that uses natural keys. Instead of creating 2 separate foreign keys in Scores, I instead create a single composite foreign key:

CREATE TABLE Scores(
    score INT NOT NULL,
    name VARCHAR(25) NOT NULL,
    ip CIDR NOT NULL,
    created_at TIMESTAMP NOT NULL,
    CONSTRAINT fk FOREIGN KEY(name, ip) REFERENCES Names(name, ip), 
    PRIMARY KEY(score, name, ip)
);