Almost all online services are using database. However, not-optimized database scheme causes disaster. The service would be slow down and sometimes get down with data requests overflown. Here I give a brief guideline on how to optimize database scheme.
Most database management system enacted on RDBMS(Relational Database Management System) base. The keyword is “Relational.” The way how to make relations mostly determines the whole performance.
There are 3 types of relation – 1:1, 1:n and n:m. Let’s take a look for each.
One-to-One(1:1) relation
Although it is rarely used as all 1:1 relations can be expressed as one composite dataset, understanding the concept would help to see the connections more clear. A student and his student ID match is an example. One student has one ID and one ID is possessed by one student.
One-to-Many(1:n) relation
It is the most common relation that most hierarchical structure can be represented with it. For example, suppose that 16 full-time employees are working in a company. Then the company has 16 employees, and each employee belong to one company.
Many-to-Many(n:m) relation
This relation is the most complicated structure among relations so it requires the performance overhead. It traverses N*M times to search for data while One-to-Many relation does it N times. One of good examples is the relation between class and student. Students take many classes and each class has many students.
The key is that, avoid Many-to-Many relation to maximize performance. If it is inevitable, count the number of data records and control it with discussion. The performance is not to be ignored in web service.
