CODEWITHSUNDEEP
database-designrdbmsddl
LimitX
LimitX

Reputation: 625

Database Design (DDL)

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'

Upvotes: 0

Views: 56

Answers (2)

Alexey Soshin
Alexey Soshin

Reputation: 17721

That's because your flavour PK is (name, parlor_id), but vote references only part of it: 

FOREIGN KEY (flavour) REFERENCES flavour (name)

Of course your could do a complex FK:

FOREIGN KEY (flavour, parlour_id) REFERENCES flavour (name, parlour_id)

But actually it would be better if you simply use regular PK on flavour:

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

And reference this ID:

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

Upvotes: 1

Amit
Amit

Reputation: 46341

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.

Upvotes: 1

Related Questions