[SQL First Step] 34. Designing Database

Designing Database

Designing database means defining database objects such as table, index, view in the schema of database.

The major part of database design is to decide the name of table and columns and its datatype. You should also consider the relationship between multiple tables.

The following is an example of table design. It is similar to the result of DESC command.

Let’s look at components one by one.

1. Logical name, Physical name
   When naming tables, you should follow rules of naming. This can make it hard to express the real function of the table. To solve the problem, you can call the table in ‘logical name’ which reflects the real function of the table and not used in command. It is different from ‘physical name’, which is used in CREATE TABLE command.

2. Datatype
   You should set a datatype for each column. You can also limit the range of data (e.g. among 1, 2, 3) by setting CHECK limitation.

3. Fixed length, Variable length
   Choose the length of character type between fixed and variable. You should consider the data before choosing the length. For example, if the length is already fixed such as product number, fixed length would be more appropriate.
   If you need very long length, use LOB type.

4. Primary key
   Be careful when you setting primary key. If it is hard to choose, use AUTO_INCREMENT function to make auto incremental column.

ER Diagram

ER Diagram (Entity Relationship Diagram) is a diagram that expresses the relationship between multiple entities. Here, an entity is a table or a view.

If entities are related, connect the columns used for the relationship with lines.

All images, except those with separate source indications, are excerpted from lecture materials.