mysql foreign key for entire table

Question

I am new to databases, but I have gone over some tutorials and learned most of the essentials (I am a long time programmer). However, I need some help getting around the limitations of relational databases. If I have a picture I can create a simple table for it as such

"CREATE TABLE picture(
file VARCHAR(150),
rating INT
)";

If I want to keep track of who rates for the picture, I can either hard code a preset number of voters like so (in this example 3 anonymous votes)

"CREATE TABLE picture(
file VARCHAR(150),
rating INT,
vote1 INT,
vote2 INT,
vote3 INT
)";

Or for an unlimited number I can create a new table as such

"CREATE TABLE ratingTemplate
(
rater INT,
rating INT
)";

But for every picture entry in the picture table I want to have a reference to this entire ratingTemplate table. Is there any proper way to use foreign keys to achieve this? Currently I am micromanaging it by creating new ratingTemplate tables and making sure to store their names in the corresponding picture table entry.

Answer 1

Yes, you would have to have a table that keeps references to picture table as well as user table.

CREATE TABLE IF NOT EXISTS PICTURE (
    PICTURE_ID  BIGINT NOT NULL AUTO_INCREMENT,
    FILE            VARCHAR(250),
    PRIMARY KEY (PICTURE_ID)
);

CREATE TABLE IF NOT EXISTS USER (
    USER_ID     BIGINT NOT NULL AUTO_INCREMENT,
    --....REST OF COLUMNS HERE...

    PRIMARY KEY(USER_ID)
);

CREATE TABLE IF NOT EXISTS PICTURE_RATING (
    RATING_ID       BIGINT NOT NULL AUTO_INCREMENT,
    PICTURE_ID  BIGINT NOT NULL,
    USER_ID     BIGINT NOT NULL,
    RATING      DOUBLE,

    PRIMARY KEY (RATING_ID),
    FOREIGN KEY (PICTURE_ID) REFERENCES PICTURE(PICTURE_ID),
    FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID)
);