composite primary key and unique key as a foreign key

Question

Ok Here is the situation.

• A university has more than one faculty.
• Each faculty has more than one department.

I do not want any duplicated faculty or department. So I defined the three tables below.

CREATE TABLE university (
    id INT PRIMARY KEY AUTO_INCREMENT,
    long_name VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(45) NOT NULL,
) Engine=InnoDB;

CREATE TABLE school_faculty (
    id INT UNIQUE NOT NULL AUTO_INCREMENT,
    name VARCHAR(45),
    universityID INT,
    PRIMARY KEY (name, universityID),
    FOREIGN KEY (universityID) REFERENCES university (id)
) Engine=InnoDB;

CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(45) NOT NULL,
    schoolfacultyID INT NOT NULL,
    FOREIGN KEY (schoolfacultyID)
        REFERENCES school_faculty (id)
) Engine=InnoDB;

Please tell me if it's wrong or if there is a better way. I am struggling with it and kinda helpless.

Answer 1

Looks ok.

You could normalize things a little more by having a table of just falculty/department names and convert your department/falculty tables to be (universityID, facultyNameID). But that level of normalization is probably overkill. While it's highly likely there's more that one Physics or Basket Weaving faculty in all the universities, the few extra bytes you'd use by keeping multiple copies of the word "Physics" in the table is not going to break the bank.

The same applies to the departments, and the same "probably not a problem" as well.