Database Design (DDL)

Question

I've got a table for ice cream shops (parlor) and a table for the different flavours offered in a parlor. In addition there is a user table and a user should be able to rate flavours.

There is a one-to-many relationship between Parlor and flavour, where flavour is a weak entity. There is also a many-to-many relationship between Flavour and User, which then creates a new table called Vote.

My DDL-Script looks like the following:

CREATE TABLE parlor (
    parlor_id INTEGER AUTO_INCREMENT,
    name VARCHAR(255),
    street VARCHAR(255),
    street_numb INTEGER,
    zip INTEGER,
    PRIMARY KEY (parlor_id)
);

CREATE TABLE flavour (
    name VARCHAR(255),
    parlor_id INTEGER,
    PRIMARY KEY (name, parlor_id),
    FOREIGN KEY (parlor_id) REFERENCES parlor (parlor_id)
);

CREATE TABLE user (
    uid INTEGER AUTO_INCREMENT,
    username VARCHAR(255) UNIQUE,
    password BINARY(64),
    PRIMARY KEY (uid)
);

CREATE TABLE vote (
    date TIMESTAMP,
    ranking INTEGER(5),
    flavour VARCHAR(255),
    uid INTEGER,
    parlor_id INTEGER,
    PRIMARY KEY (date, uid, flavour, parlor_id),
    FOREIGN KEY (uid) REFERENCES user (uid),
    FOREIGN KEY (flavour) REFERENCES flavour (name),
    FOREIGN KEY (parlor_id) REFERENCES flavour (parlor_id)
);

My problem is, that I'm able to vote for a flavour that doesn't even exist in a parlor. For example:

INSERT INTO vote (date, ranking, flavour, uid, parlor_id) VALUES ('...', 5, 'Chocolate', 1, 10)

In a parlor with the ID 10, a user with the userID 1 rates the flavour 'Chocolate' with 5.

But when I do ...

SELECT * FROM flavour WHERE parlor_id=10;

there is no flavour 'chocolate'

Answer 1

Add an actual key to the flavors table (flavor_id INTEGER), set that as the primary key and have foreign keys reference that column. This will sort it your problem and improve your overall design.

You can "convert" your name, parlor_id key to a unique constraint to maintain uniqueness.