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.