mysql foreign key for entire table

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

开发者_StackOverflow中文版

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.

---

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

---

You have not identified the primary key for the 'picture' table, but as presented the primary key would appear to be the 'file' column. You would need to add a column to the 'ratingTemplate' table named 'file' that would also be be varchar(150. Your primary key in the 'ratingTemplate' would be a combination of 'file' and 'rater'. The 'file' column in the 'ratingTemplate' table would be a foriegn key to the 'file' column int he 'picture' table.

Sample Query:

SELECT  picture.file, rater, rating
FROM    picture INNER JOIN ratingTemplate ON picture.file = ratingTemplate.file
WHERE   picture.file = 'filenameiwant'

Another approach would be to add a surrogate primary key to the 'picture' table. Perhaps named 'FileId' as an integer. In that case, you would use the 'FileId' column in the 'ratingTemplate' as the foriegn key instead of 'file'. For large data sets this would execute faster and use less space.

Sample query:

FROM    picture INNER JOIN ratingTemplate ON picture.FileId = ratingTemplate.FileId
WHERE   picture.FileId = 257