Hướng dẫn Thiết Kế Cơ Sở Dữ Liệu Database MYSQL

Nguyên tắc thiết kế CSDL

Trước khi bước vào việc thiết kế Database bạn cần nắm một số nguyên tắc quan trọng sau:

• Chính xác

• Tránh trùng lặp

• Dễ hiểu

• Chọn đúng thuộc tính và kiểu thuộc tính

• Chọn đúng mối quan hệ

Các bước thiết kế Database chuẩn

Thiết kế database, hay cũng như thiết kế 1 phần mềm, một trang web, đều bắt đầu có quy trình gồm những bước cơ bản:

• Phân tích yêu cầu.

• Phác thảo ý tưởng ra giấy, làm bản mẫu.

• Thực hiện.

Phân tích yêu cầu

Đây chắc hẳn là bước mà một lập trình viên chưa có nhiều kinh nghiệm (như mình chẳng hạn) ít chú tâm nhất. Không cần phải nói thêm về tầm quan trọng của giai đoạn này, khi đi làm được một thời gian bạn sẽ thấy code thực sự không khó, maintain và thay đổi theo những nhu cầu của khách hàng còn khó hơn gấp vạn lần. Bạn sẽ phải dần dần thay đổi tư duy. Lập trình viên không phải là đâm đầu vào code mà không cần biết chuyện gì đang xảy ra, hãy suy nghĩ như những product manager, hãy suy nghĩ như mình là người tạo ra sản phẩm đó.

Rồi, mở đầu với database, ở đây là MySQL, rất phổ biến, hầu như ai cũng xài, hầu như ai cũng nghĩ nó dễ, mà hầu như ai cũng không nắm hết về nó. Ở đây, mình không quan tâm bạn dùng giao diện hay command line, vì đằng nào cũng ra kết quả như nhau.

Xác định yêu cầu một cách đầy đủ, cụ thể sẽ giúp cho việc thiết kế CSDL trở lên dễ dàng hơn. Luôn đặt ra câu hỏi:

• CSDL sẽ được sử dụng như thế nào?

• Những thông tin gì cần được lưu vào CSDL?

Chúng ta có thể tham khảo các hệ thống dữ liệu sẵn có. Có thể là trong hoá đơn bán hàng, tập lưu trữ hồ sơ khách hàng.. vv.v.v

Phác thảo ý tưởng

Có thể bạn ung dung khi hiện tại có rất nhiều tool thiết kế database offline lẫn online, tuy nhiên, không gì thoải mái hơn công cụ truyền thống là bút chì và giấy. Hãy cầm lên và vẽ ra, bạn cần bao nhiêu bảng, liên kết với nhau như thế nào, liệu bảng nào nên tách ra riêng, bảng nào cần nhiều cột, bảng nào cần ít cột. Đây là giai đoạn tốn thời gian nhất. Hãy nghĩ ra tất cả các trường hợp có thể.

Thực hiện

• Việc đầu tiên là tạo database, công việc có vẻ đơn giản, tuy nhiên sẽ có vài thứ cần chú ý. Điều đầu tiên đó chính là bạn phải xác định rõ ứng dụng của mình, nhu cầu đọc nhiều hơn hay là thêm sửa xóa nhiều hơn. Nếu đọc nhiều hơn, nên thiết kế ít bảng và có nhiều thông tin, nhiều cột hơn, nếu thêm/sửa/xóa là chủ yếu, hãy thiết kế nhiều bảng và trong mỗi bảng, dữ liệu được chuẩn hóa. Cái này phụ thuộc vào tư duy của người thiết kế.

• Tiếp theo đó là charset và collation, chúng luôn đi với nhau. Charset hiểu nôm na là tập hợp các ký tự (giống như bảng chữ cái) và dạng mã hóa của nó. Collation là tập hợp các quy tắc để so sánh các ký tự trong charset, thực chất là so sánh dạng đã mã hóa của nó. Tài liệu chính thống của MySQL đã nói rất rõ ở đây. Charset và collation cần được để tâm khi bạn làm database của những hệ thống đa ngôn ngữ, hoặc lưu những ký tự đặc biệt ví dụ như tiếng Việt. utf8 charset với utf8_general_ci và utf8_unicode_ci collation đều làm tốt hai việc này và so sánh giữa hai loại collation này

• Xong, tiếp tới là tạo bảng. Cũng như việc tạo database, hầu hết chúng ta đều tạo với những thiết lập mặc định mà hệ thống làm cho, mà chẳng quan tâm gì những thiết lập đó có nghĩa là gì. Mình sẽ kể ra những thứ nên được chú tâm để thiết kế một database chuẩn, và dễ dàng tối ưu hết sức có thể. Đã đến lúc bạn để tâm tới cái gọi là storage engine của bảng. Trong kipalog cũng có 1 bài bàn về vấn đề này, bạn có thể xem ở đây. Mình chỉ bổ sung một chút cho dễ hiểu.

• Với những mục đích thông thường, ta nên dùng InnoDB. Khi một dòng được thêm/sửa, thì MySQL sẽ chỉ khóa không cho những process khác thao tác với dòng đó, được gọi là row level lock, mọi thao tác với những dòng khác vẫn diễn ra bình thường, bởi vậy InnoDB có đặc tính gọi là Concurrency, vì các process khác không phải chờ khi dòng đó được hoàn tất. MyISAM thì chỉ hỗ trợ table level lock, bạn sẽ khắc hiểu là MySQL sẽ khóa toàn bộ bảng khi thao tác với 1 dòng, và sẽ không hợp lý khi ứng dụng của bạn cần hỗ trợ nhiều thao tác đồng thời. Memory engine hiện tại không được sử dụng nhiều, thay vào đó là Redis hay Memcached. Archive engine thích hợp để lưu log, tuy nhiên cách lưu log dùng bảng hay dùng file, và lưu thế nào sẽ là một bài toán khác.

Tiếp theo là tạo những cột cần thiết cho bảng, và điều cực kỳ quan trọng là kiểu dữ liệu, hãy sử dụng kiểu số nguyên khi có thể, kiểu ngày tháng thì nên dùng DATETIME ngoại trừ một vài field sử dụng TIMESTAMP để lưu lại thời điểm.

Cụ thể:

• Luôn luôn lưu một cột là người tạo để biết là ai tạo dòng này, một cột là ngày tạo, và ngày chỉnh sửa dòng này. Ngày tạo và ngày chỉnh sửa nên chọn kiểu dữ liệu là TIMESTAMP.
  Đối với dữ liệu ngày tháng, nên lưu là DATETIME để phục vụ cho những nhu cầu sau này như đánh index, virtual column.

• Tên cột càng ngắn càng tốt, dĩ nhiên phải rõ nghĩa và dễ hiểu.
  Những cột ví dụ như “type”, “status”… nên chọn kiểu là số nguyên, và hãy comment cho cột đó để biết những giá trị cột đó có thể mang, ví dụ 1:enabled, 2:disabled. Bạn đã bao giờ khóc thét khi không biết được 1 cột như vậy mang giá trị 3 mà không thể tìm được ở đâu cho biết rằng 3 có nghĩa là gì chưa?
  Default value cho cột vẫn là một cuộc tranh luận, và bạn đừng nên tin ai cả, hãy đọc hết sức có thể và chỉ nên tin vào yêu cầu của chính mình.

• Hạn chế sử dụng kiểu dữ liệu là TEXT hết sức có thể.
  Index rất quan trọng, hãy sử dụng khi có thể. Lời khuyên của mình: luôn luôn sử dụng index, đặc biệt những cột có kiểu dữ liệu là số nguyên.

• Cái cơ bản nhất, ai cũng biết đó là đánh index vào những cột nào xuất hiện trong điều kiện WHERE của câu truy vấn. Tuy nhiên, còn nhiều loại index khác cao cấp hơn, như covering index, merge index, hash index, index cho virtual column… Đánh index là cả một vấn đề, và nó là một cách để cải thiện tốc độ truy vấn hiệu quả nhất. Hãy thử nghĩ ra tất cả các trường hợp có thể, hãy nghĩ xem ví dụ một lúc nào đó bạn được yêu cầu tìm kiếm 1 user dựa vào email của user đó, bạn sẽ đánh index vào trường email với kiểu dữ liệu VARCHAR(50)?

• Hãy học cách kiểm tra câu truy vấn của mình nhanh hay chậm, tiêu biểu là dùng lệnh EXPLAIN. Còn rất nhiều best practice về tạo database

Hướng dẫn thực thi cơ bản với MySQL – Thiết kế Database

Notes MySQL

Video Hướng dẫn

[MySQL] Create Database, table [MySQL] Add PRIMARY KEY, FOREIGN KEY Constraint [MySQL] Create Database ER diagram

Create Database

https://www.w3schools.com/sql/sql_create_db.asp

CREATE DATABASE databasename;

CREATE DATABASE mapping;

Khi đặt tên nên:

• Không dấu

• Không khoảng trắng

• Viết thường

• Không ký tự đặc biệt

Create Table

https://www.w3schools.com/sql/sql_create_table.asp

Cú pháp

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

column3 datatype,

....

);

Chúng ta có thể khai báo luôn khóa chính lúc tạo bảng luôn, như thế này

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int

);

Lỗi

15:09:03 CREATE TABLE admin( admin_id int, ward varchar(255), district varchar(255), city varchar(255) ) Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar. 0.000 sec

Lỗi này do cửa sổ query không biết nên truy vấn từ Database nào, trong mysql workbend thì ta nhấp đúp chuột vào Database muốn truy vấn để active nó lên.

Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar

Thực hành

admin

CREATE TABLE admin(

admin_id int,

ward varchar(255),

district varchar(255),

city varchar(255)

);

street

CREATE TABLE street(

street_id int,

street_type_id int,

admin_id int,

street_name varchar(255)

);

Mình nên để mấy cái cột liên kết (khoái ngoại, khóa chính) lên đầu bảng cho dễ quản lý

poi

CREATE TABLE poi(

poi_id int,

category_id int,

address_id int,

admin_id int,

name varchar(255)

);

street_type

CREATE TABLE street_type(

street_type_id int,

street_type varchar(255)

);

categories

CREATE TABLE categories(

category_id int,

category varchar(255)

);

address

CREATE TABLE address(

addr_id int,

street_id int,

admin_id int,

addr_no varchar(255)

);

Tạo Khóa chính

https://www.w3schools.com/sql/sql_primarykey.asp

Cú pháp

Chúng ta có thể khai báo luôn khóa chính lúc tạo bảng luôn, như thế này

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int

);

Chúng ta nên tạo khi create table luôn

Nếu đã tạo rồi thì giờ ta update lại thôi

ALTER TABLE Persons

ADD PRIMARY KEY (ID);

Thực hành

ALTER TABLE admin

ADD PRIMARY KEY (admin_id);

ALTER TABLE street

ADD PRIMARY KEY (street_id);

ALTER TABLE poi

ADD PRIMARY KEY (poi_id);

ALTER TABLE street_type

ADD PRIMARY KEY (street_type_id);

ALTER TABLE categories

ADD PRIMARY KEY (category_id);

ALTER TABLE address

ADD PRIMARY KEY (addr_id);

Tạo Khóa ngoại, liên kết bảng

https://www.w3schools.com/sql/sql_foreignkey.asp

Cú pháp

Chúng ta có thể khai báo luôn khóa chính lúc tạo bảng luôn, như thế này

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

Đối với khóa ngoại thì ta nên tạo xong hết bảng rồi mới tạo khóa ngoại, vì nếu chưa tạo bảng mà tạo khóa ngoại liên kết tới nó thì chương trình sẽ báo lỗi không tồn tại

Nếu đã tạo rồi thì giờ ta update lại thôi

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Thực hành

-- admin: Không có khóa ngoại

-- street

-- Cú pháp là ADD FOREIGN KEY ({Tên cột khóa ngoại}) REFERENCES {Tên bảng liên kết}({Khóa chính của bảng liên kết});

-- Bảng này có 2 khóa ngoại

ALTER TABLE street

ADD FOREIGN KEY (street_type_id) REFERENCES street_type(street_type_id);

ALTER TABLE street

ADD FOREIGN KEY (admin_id) REFERENCES admin(admin_id);

-- poi

-- Có 3 khóa ngoại

ALTER TABLE poi

ADD FOREIGN KEY (category_id) REFERENCES categories(category_id);

ALTER TABLE poi

ADD FOREIGN KEY (address_id) REFERENCES address(addr_id);

ALTER TABLE poi

ADD FOREIGN KEY (admin_id) REFERENCES admin(admin_id);

-- street_type

-- Không có khóa ngoại, nó là bảng danh mục :')

-- categories

-- Không có khóa ngoại, nó là bảng danh mục :')

-- address

-- Có 2 khóa ngoại

ALTER TABLE address

ADD FOREIGN KEY (street_id) REFERENCES street_type(street_type_id);

ALTER TABLE address

ADD FOREIGN KEY (admin_id) REFERENCES admin(admin_id);

Show Diagram

https://dataedo.com/kb/tools/mysql-workbench/create-database-diagram

Một số lưu ý khác

Cột khóa chính thường không được phép null NOT NULL và được cho tự động tăng AUTO_INCREMENT

ALTER TABLE

clients

ALTER COLUMN

phone

Cú pháp chuẩn lúc tạo bảng

CREATE TABLE Persons (

Personid int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (Personid)

);