Informal Design Guidelines for Relational Schema Design
Informal guidelines are practical rules used to design high-quality relational schemas that minimize redundancy, avoid anomalies, and ensure data consistency. These guidelines are not strictly based on normal forms, but they help in achieving a good design.
1. Avoid Redundant Information
Problem:
Storing the same data in multiple places leads to data redundancy.
Effects:
Wastes storage space.
Causes update anomalies (need to update in multiple places).
Inconsistency if only some places are updated.
Example:
StudentID
Name
Department
DeptLocation
101
Meera
CS
Block A
102
Arun
CS
Block A
DeptLocation is repeated for each student in the same department.
2. Avoid Insertion, Deletion, and Update Anomalies
Insertion anomaly: Can’t insert data because other data is missing.
Deletion anomaly: Deleting a record causes loss of useful information.
Update anomaly: Changing a value requires multiple updates.
Example (Same as above):
Deletion of Meera deletes all info about CS department.
3. Use Meaningful Primary Keys
Choose a stable, unique attribute (or set of attributes) to act as the primary key.
Avoid using attributes that are likely to change over time (like phone numbers or names).
4. Avoid Null Values as Much as Possible
Problems with Nulls:
Uncertainty in data interpretation.
Complexity in query logic and condition checks.
May indicate poor schema design.
Tips:
Break the relation if many nulls exist in some columns.
Separate optional information into a different table.
5. Ensure Lossless Join and Dependency Preservation
When decomposing relations:
Lossless join ensures that original relation can be reconstructed.
Dependency preservation ensures all constraints are still enforceable after decomposition.
