MySQL Database utf-8 encoding

Database design

Multimedia websites, small programs, mobile development are inseparable from the background system, must use the database.

The database design consists of four main steps:

1. Demand Analysis

The main results are: Requirements Analysis documentation

Entities (objects), contact

There are three kinds of contact: 1-1, 1-*, *-*

2. Conceptual design (E-r map)

3, logical design (table): At least 3NF compliance

(1) Each entity corresponds to a single table

User table: number , name, type

Book list: number , ISBN, name, price, publisher

(2) More-long, the relationship also corresponds to a table

User-book list: User number, book number

4. Physical Design

Take MySQL for example:

DROP DATABASE IF EXISTS Books;

CREATE DATABASE Books;

Use books;

SET Collation_database=utf8_general_ci;

SET Collation_connection=utf8_general_ci;

SET Collation_server=utf8_general_ci;

ALTER DATABASE Books CHARACTER SET UTF8;

DROP TABLE IF EXISTS tuserbook;

DROP TABLE IF EXISTS TUser;

DROP TABLE IF EXISTS tbook;

CREATE TABLE TUser (

Code VARCHAR (16),

Username VARCHAR (64),

Password VARCHAR (64),

Utype VARCHAR (1),

PRIMARY KEY (Code)

);

CREATE TABLE Tbook (

Code VARCHAR (16),

Name VARCHAR (64),

ISBN VARCHAR (32),

Price float,

Publish VARCHAR (32),

PRIMARY KEY (Code)

);

CREATE TABLE Tuserbook (

ID INT (one) PRIMARY KEY auto_increment,

Usercode VARCHAR (16),

Bookcode VARCHAR (16),

FOREIGN KEY (Usercode) REFERENCES TUser (code)

On DELETE CASCADE

On UPDATE CASCADE,

FOREIGN KEY (Bookcode) REFERENCES Tbook (code)

On DELETE CASCADE

On UPDATE CASCADE,

);

Database modeling Tools:

PowerDesigner

MySQL Database utf-8 encoding