Skip to main content

What are 20 best practices for database design ?

Photo by Christina Morillo from Pexels


  1. Use a well-defined table or column names and keep them consistent (for example, School, StudentCourse, CourseID).
  2. Use the singular form of the table name (ie, use StudentCourse instead of StudentCourses). The table represents a collection of entities and does not need to be plural.
  3. Do not use spaces in the table name. Otherwise, you will have to use characters such as "{" and "[" when defining the table (that is, in order to access the table Student Course, you have to write "Student Course". Using StudentCourse is sufficient).
  4. Do not add unnecessary prefixes or suffixes to the table name (that is, just name School, not TblSchool, SchoolTable, etc.).
  5. Encrypt the password to ensure security. You can decrypt them in the app when needed.
  6. Use integer ID fields in all tables. Even if the ID is not used right now, it will be useful in the future (for example, in the associated table, index, etc.).
  7. Use integer (or related) data columns to create indexes. Varchar column indexes can cause performance problems.
  8. Use bit fields for boolean values. It is unnecessary to use Integer or Varchar type storage. At the same time, add the description of Is to these column names.
  9. Verify database access. No user should be given the admin role.
  10. If not necessary, avoid using "select *" query statements. For better performance, please use "select [required_columns_list]".
  11. If the program code is large, you can use the ORM (Object Relational Mapping) framework (such as Hibernate, iBatis) tools. Regarding its performance problems, it can be dealt with through detailed configuration parameters.
  12. Separate large tables (table parts) that are not used or not commonly used into different physical storage spaces to provide better query performance.
  13. For important database systems, use disaster recovery solutions and security services, such as failover clustering, automatic backup, and replication.
  14. In order to ensure data integrity, please use constraints (such as foreign keys, Check, Not null constraints, etc.). Don't give complete control over the application code.
  15. The lack of database documentation is very bad (evil). Use ER diagram to describe the database design model. Also remember to write code for scripts such as triggers and stored procedures.
  16. Use indexes for frequent queries. The Analyser tool can be used to determine where the index is defined. For queries to fetch a large number of columns, a clustered index is usually better. For point queries, non-clustered indexes can be used.
  17. Place the database server and the Web server on different computers. This can provide better security performance (the attacker cannot directly access the data), and because the number of access requests and the number of processes can be limited, the server can also obtain better CPU and memory performance.
  18. Image and Blob columns must not be defined in frequently queried tables, this is also for performance considerations. You can put these data in a separate table, and create a pointer to them in the lookup table.
  19. Normalization (Normalization) is necessary to further optimize application performance. Otherwise, you may face too many data copies. Of course, over-normalization will result in a large number of connections across too many tables. Both of these will affect performance.
  20. It is also necessary to spend some effort on database modeling and design. If you save time for this, you are likely to face 10 times or even 100/1000 times the maintenance/redesign cost.

Comments

Popular posts from this blog

Defination of the essential properties of operating systems

Define the essential properties of the following types of operating sys-tems:  Batch  Interactive  Time sharing  Real time  Network  Parallel  Distributed  Clustered  Handheld ANSWERS: a. Batch processing:-   Jobs with similar needs are batched together and run through the computer as a group by an operator or automatic job sequencer. Performance is increased by attempting to keep CPU and I/O devices busy at all times through buffering, off-line operation, spooling, and multi-programming. Batch is good for executing large jobs that need little interaction; it can be submitted and picked up later. b. Interactive System:-   This system is composed of many short transactions where the results of the next transaction may be unpredictable. Response time needs to be short (seconds) since the user submits and waits for the result. c. Time sharing:-   This systems uses CPU scheduling and multipro-gramming to provide economical interactive use of a system. The CPU switches rapidl

What is a Fair lock in multithreading?

  Photo by  João Jesus  from  Pexels In Java, there is a class ReentrantLock that is used for implementing Fair lock. This class accepts optional parameter fairness.  When fairness is set to true, the RenentrantLock will give access to the longest waiting thread.  The most popular use of Fair lock is in avoiding thread starvation.  Since longest waiting threads are always given priority in case of contention, no thread can starve.  The downside of Fair lock is the low throughput of the program.  Since low priority or slow threads are getting locks multiple times, it leads to slower execution of a program. The only exception to a Fair lock is tryLock() method of ReentrantLock.  This method does not honor the value of the fairness parameter.

How do clustered systems differ from multiprocessor systems? What is required for two machines belonging to a cluster to cooperate to provide a highly available service?

 How do clustered systems differ from multiprocessor systems? What is required for two machines belonging to a cluster to cooperate to provide a highly available service? Answer: Clustered systems are typically constructed by combining multiple computers into a single system to perform a computational task distributed across the cluster. Multiprocessor systems on the other hand could be a single physical entity comprising of multiple CPUs. A clustered system is less tightly coupled than a multiprocessor system. Clustered systems communicate using messages, while processors in a multiprocessor system could communicate using shared memory. In order for two machines to provide a highly available service, the state on the two machines should be replicated and should be consistently updated. When one of the machines fails, the other could then take‐over the functionality of the failed machine. Some computer systems do not provide a privileged mode of operation in hardware. Is it possible t