[FIXED] Many to One relationship?

Issue

The following MySQL code...

CREATE TABLE Employee (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    departmentId TINYINT UNSIGNED NOT NULL
        COMMENT "CONSTRAINT FOREIGN KEY (departmentId) REFERENCES Department(id)",
    firstName VARCHAR(20) NOT NULL,
    lastName VARCHAR(40) NOT NULL,
    email VARCHAR(60) NOT NULL,
    ext SMALLINT UNSIGNED NULL,
    hireDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    leaveDate DATETIME NULL,
    INDEX name (lastName, firstName),
    INDEX (departmentId)
)

CREATE TABLE Department (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(40),
    UNIQUE (name)
)

... defines a many to one relationship between an employee and a department. In other words, an employee can only be in one department, but a department can have many employees. But can someone explain this in more detail? How does the above code tell me that?

Solution

It is sometimes easy to discern the relationship between two (or more) tables by looking at the foreign key of referring table.

A simple rule is, if the foreign key is a primary key itself in its own table, the relationship is probably 1 to 1, whereas if the foreign key is not a primary key in its own table, the relationship is probably 1 to many, where the table having the foreign key at the "many" end.

In your example, for departmentID in Employee, it is not a primary key. Therefore, in this situation, a Department can be referenced by a lot of Employees at the same time, hence the "1 to many" relation can be established.

However, in your quoted example from mkyong.com, both tables (stock and stock_detail) use stock_id as primary key. In this situation, the "1 to 1" relation can be established.

Consider this: Both stock and stock_detail tables will only contain ONE single record having a certain value as stock_id. I made a sample here.

Answered By - Amao Mao