C&E DIS-2 rep

The poorly designed database is a nightmare for the database users if database architect designs database ignoring the fact that database it should be useful for the store once read many times. Data is stored to be consumed later, and the goal is always to store it and retrieve it in the most efficient manner. To achieve this, the database designer must know in advance what the data is going to represent, how is it going to be acquired and at what rate, what its operational volume will be and, finally, how it is going to be used.

Designing a database is not a deterministic task; two database designers may follow all the rules and normalization principles for a given problem, and in most cases, they will generate different data layouts. This is inherent to the creative nature of software engineering. However, there are some analysis techniques that make sense in every instance, and to follow them is the best way to get to a database that performs at its best.

Redundant fields and tables are a nightmare for developers since they require business logic to keep many versions of the same information up to date. This is an overhead that can be avoided if normalization rules are followed thoroughly. Although sometimes redundancy may seem necessary, it must be used only in very specific cases and be clearly documented to be taken into consideration in future developments.

Sometimes, you will have a table that you need to query in many columns. As the table grows, you will notice that the SELECTs on these columns slow down. If the table is big enough, you will think, logically, to create an index on each column that you use to access this table only to find almost immediately that the performance of SELECTs improves but INSERTs, UPDATEs, and DELETEs drop. This, of course, is since indexes must be kept synchronized with the table, which means massive overhead for the DBE. This is a typical case of over-indexing that you can solve in many ways; for instance, having only one index on all the columns different from the primary key that you use to query the table, ordering these columns from the most used to the least may offer better performance in all CRUD operations than one index per column.

References:

Abadi, D., Boncz, P., Harizopoulos, S., Idreos, S., & Madden, S. (2013). The design and implementation of modern column-oriented database systems. Foundations and Trends® in Databases, 5(3), 197-280.

Robbins, R. J. (1994). Database Fundamentals. Johns Hopkins University, rrobbins@ gdb. org, 5-8.

Meyyappan, N., Foo, S., & Chowdhury, G. G. (2004). Design and evaluation of a task-based digital library for the academic community. Journal of Documentation, 60(4), 449-475.