Java Backend Table Relationship

Trong bài này chúng ta sẽ tìm hiểu về các loại quan hệ giữa các bảng dữ liệu cũng như cách cấu hình bằng Hibernate sao cho đúng và hiệu quả. 

Có 3 loại quan hệ là 1-1, 1-n, n-n 

1. 1-1

SQL Script:

DROP DATABASE IF EXISTS ThachPhamDevDb;

-- Create database

CREATE DATABASE IF NOT EXISTS ThachPhamDevDb;

USE ThachPhamDevDb;

-- Create table Address

DROP TABLE IF EXISTS `Address`;

CREATE TABLE IF NOT EXISTS `Address` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

street NVARCHAR(50) NOT NULL,

city NVARCHAR(50) NOT NULL

);

-- Create table Client

DROP TABLE IF EXISTS `Client`;

CREATE TABLE IF NOT EXISTS `Client` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name NVARCHAR(50) NOT NULL,

address_id SMALLINT UNSIGNED NOT NULL UNIQUE KEY,

FOREIGN KEY (address_id) REFERENCES Address(id)

);

-- INSERT DATA

USE ThachPhamDevDb;

INSERT INTO `Address` (street, city)

VALUE ('30, street1', 'New York'),

('3, street2', 'New York'),

('24, street3', 'New York');

USE ThachPhamDevDb;

INSERT INTO `Client` (name, address_id)

VALUE ('ThachPham', 1),

('Jade Enginner', '2'),

('Huyen', '3');

-- ('Huyen', '3');

Video phan table relation 1 - 1

2. 1-n

SQL Script:

DROP DATABASE IF EXISTS ThachPhamDevDb;

-- Create database

CREATE DATABASE IF NOT EXISTS ThachPhamDevDb;

USE ThachPhamDevDb;

-- Create table Client

DROP TABLE IF EXISTS `Client`;

CREATE TABLE IF NOT EXISTS `Client` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name NVARCHAR(50) NOT NULL

);

-- Create table Address

DROP TABLE IF EXISTS `Address`;

CREATE TABLE IF NOT EXISTS `Address` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

street NVARCHAR(50) NOT NULL,

city NVARCHAR(50) NOT NULL,

client_id SMALLINT UNSIGNED NOT NULL,

FOREIGN KEY (client_id) REFERENCES Client(id)

);

-- INSERT DATA

USE ThachPhamDevDb;

INSERT INTO `Client` (name)

VALUE ('ThachPham'),

('Jade Enginner');

INSERT INTO `Address` (street, city, client_id)

VALUE ('30, street1', 'New York', 1),

('3, street2', 'New York', 1),

('24, street3', 'New York', 2);

Video hướng dẫn:

3. n-n

Cách 1: 

DROP DATABASE IF EXISTS ThachPhamDevDb;

-- Create database

CREATE DATABASE IF NOT EXISTS ThachPhamDevDb;

USE ThachPhamDevDb;

-- Create table Client

DROP TABLE IF EXISTS `Client`;

CREATE TABLE IF NOT EXISTS `Client` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name NVARCHAR(50) NOT NULL

);

-- Create table Address

DROP TABLE IF EXISTS `Address`;

CREATE TABLE IF NOT EXISTS `Address` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

street NVARCHAR(50) NOT NULL,

city NVARCHAR(50) NOT NULL

);

-- Create table ClientAddress

DROP TABLE IF EXISTS ClientAddress;

CREATE TABLE IF NOT EXISTS ClientAddress (

client_id SMALLINT UNSIGNED NOT NULL,

address_id SMALLINT UNSIGNED NOT NULL,

FOREIGN KEY (address_id) REFERENCES Address(id),

FOREIGN KEY (client_id) REFERENCES Client(id),

PRIMARY KEY (client_id, address_id)

);

-- INSERT DATA

USE ThachPhamDevDb;

INSERT INTO `Client` (name)

VALUE ('ThachPham'),

('Jade Enginner'),

('Flash');

INSERT INTO `Address` (street, city)

VALUE ('30, street1', 'New York'),

('3, street2', 'New York'),

('24, street3', 'New York'),

('333, street 100', 'Revenue');

INSERT INTO ClientAddress (client_id, address_id)

VALUE (1, 1),

(1, 2),

(2, 1),

(3, 1),

(3, 3),

(2, 4);

Video hướng dẫn:

Cách 2: Chuyển thành quan hệ 1 - n + n - 1 để tạo thành quan hệ nhiều nhiều

SqlScript:

DROP DATABASE IF EXISTS ThachPhamDevDb;

-- Create database

CREATE DATABASE IF NOT EXISTS ThachPhamDevDb;

USE ThachPhamDevDb;

-- Create table Client

DROP TABLE IF EXISTS `Client`;

CREATE TABLE IF NOT EXISTS `Client` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name NVARCHAR(50) NOT NULL

);

-- Create table Address

DROP TABLE IF EXISTS `Address`;

CREATE TABLE IF NOT EXISTS `Address` (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

street NVARCHAR(50) NOT NULL,

city NVARCHAR(50) NOT NULL

);

-- Create table ClientAddress

DROP TABLE IF EXISTS ClientAddress;

CREATE TABLE IF NOT EXISTS ClientAddress (

client_id SMALLINT UNSIGNED NOT NULL,

address_id SMALLINT UNSIGNED NOT NULL,

created_at DATETIME DEFAULT NOW(), -- <===== Add more here

FOREIGN KEY (address_id) REFERENCES Address(id),

FOREIGN KEY (client_id) REFERENCES Client(id),

PRIMARY KEY (client_id, address_id)

);

-- INSERT DATA

USE ThachPhamDevDb;

INSERT INTO `Client` (name)

VALUE ('ThachPham'),

('Jade Enginner'),

('Flash');

INSERT INTO `Address` (street, city)

VALUE ('30, street1', 'New York'),

('3, street2', 'New York'),

('24, street3', 'New York'),

('333, street 100', 'Revenue');

INSERT INTO ClientAddress (client_id, address_id)

VALUE (1, 1),

(1, 2),

(2, 1),

(3, 1),

(3, 3),

(2, 4);