How to spot all transitive dependencies in mysql database design?

Question

I've read up on 3NF and transitive dependencies but had a couple of questions I'm unsure if I have achieved 3NF with my database design.

Context: I want to create a basic bankings design where customers have required to give full name, date of birth, email, telephone number and opening balance to open an account. Each account opened will have a unique sort code and account number. Customers can open loans and therefore receive loan payments from customers on a particular date every month.

CODE

CREATE TABLE customer (
    customer_id INT AUTO_INCREMENT,
    first_name VARCHAR(100)  NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (customer_id),
    UNIQUE (email)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE account (
    account_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    account_type_id VARCHAR(15) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    opening_balance DECIMAL(10,2) NOT NULL DEFAULT 51,
    account_number INT(8) NOT NULL,
    sort_code VARCHAR(8) NOT NULL,
    created_date DATE DEFAULT now(),
    branch_id INT NOT NULL,
    PRIMARY KEY (account_id),
    UNIQUE(sort_code),
    UNIQUE(account_number)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 

CREATE TABLE address (
    address_id INT AUTO_INCREMENT,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255) DEFAULT NULL,
    postal_code VARCHAR(10) NOT NULL,
    state VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    PRIMARY KEY (address_id),
    UNIQUE(postal_code)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT,
    account_id INT NOT NULL,
    transaction_date DATETIME NOT NULL DEFAULT now(),
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    reason VARCHAR(255) DEFAULT "n/a",
    PRIMARY KEY (transaction_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE transaction_types (
    transaction_type_id VARCHAR(15),
    description TEXT NOT NULL,
    PRIMARY KEY (transaction_type_id),
    UNIQUE(description)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE loans (
    loan_id INT,
    account_id INT NOT NULL,
    monthly_rate DECIMAL(10,2) NOT NULL,
    duration_months INT NOT NULL,
    first_payment DATE NOT NULL,
    monthly_due_date DATE NOT NULL,
    PRIMARY KEY (loan_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


Happy to answer any questions, and take additional feedback if my tables aren't complying with 3NF.

Answer 1

Composite index is called for

UNIQUE(sort_code),
UNIQUE(account_number)

-->

UNIQUE(sort_code, account_number)

(This is based on my understanding that sort_code or routing_number is a unique number for a bank or bank branch.)

Unique is not called for

PRIMARY KEY (address_id),
UNIQUE(postal_code)

-->

PRIMARY KEY (address_id),
INDEX(postal_code)

on the assumption that there are [potentially] many addresses in a postal_code.

There are approximately 1.8 million postcodes in the UK and approximately 30 million individual postal addresses. Each separate postcode usually identifies the address to within 80 properties (with an average of 15 properties per postcode), although large businesses may have a unique code.

If you say UNIQUE(post_code), then the table won't have more than about 1.8M rows. The name of the table implies that you really want up to 80M rows. (Yeah, your bank does not have a monopoly, but that is not my point.) That table represents exactly one (or zero) row per address in the country, hence PRIMARY KEY(address_id).

With INDEX(postal_code), you can do SELECT * FROM tbl WHERE postal_code = 'W1A0AX to find all the dozens of locations in that one post_code.

A side issue: Decide whether to store the space or remove it. Then do that consistently for both storing and searching by postal_code. If you allow lower case letters, that can be handled by a suitable COLLATION.

Rarely 2 Uniques

Note: a PRIMARY KEY is a UNIQUE index. transaction_types is one of the few valid examples of where two UNIQUE indexes is 'correct' for a table. That table is a mapping between an id and a string. I recommend you check all other uses of UNIQUE.

An INDEX helps look with performance in JOIN and simple lookups. Both UNIQUE and FOREIGN KEY create a BTree Index as a side effect.

Etc

You seem to be carried away with UNIQUE; check all the rest of the uses in your schema.

(I realize this does not address your question about 3NF. I think you will automatically root out the 3NFs as you work with the schema.)