Explain the factors influencing physical database design

Physical database designing is used for good performance while the process of structuring data in the storage.

Factors influencing physical database design

Let us see the important factors that influence physical database design −

Transactions and queries

It is important to understand what kind of transactions and queries will be run and what use will the database serve.

Following information about the queries should be needed −

• Files that will query access.

• Selection conditions to be specified for the query.

• Type of selection conditions to be used in query whether equality, range, or inequality.

• Join conditions to be used in the query.

• Need for linking tables for query optimization.

Following information about the transactions should be needed −

• Files to update.

• Operation type to be performed on files.

• Attributes on which conditions will be applied.

• Attributes whose data will be modified.

Frequency Analysis

After identifying the traits and features of the kind of query and transaction to be run, it is important to find the frequency of invocation of each query/transaction. This information helps in compiling a list of the expected frequency of each query/transaction.

Time constraints

Many queries/transactions offer performance constraints. It may take certain seconds to terminate. These constraints place priorities on candidate attributes of the index. The attributes used in a query that has time constraints are given the highest priority.

Update frequency

If a file is regularly updated, there has to be some minimum number of paths for access. This is because the whole activity slows down the operation.

Uniqueness constraints

Every candidate key attribute should have an index or an index. The index makes the search easier when the uniqueness constraint is checked. This is because all the attribute values exist in the index's leaf nodes.