A table stores customer details and repeated order information. How would you decide whether normalization is required?
Interview preparation resource from Gate Smashers.
I would examine the table for repeated customer information, repeating groups, mixed facts, functional dependencies, and insertion/update/deletion anomalies. If those problems exist, normalize by separating stable entities (e.g., Customer, Order, OrderItem) so each attribute depends on the correct key. Denormalization can be applied later only as a measured optimization for read performance, not as the default for transactional design.
Decision criteria — what to check
I would check whether the table contains repeated customer information, repeating groups, mixed facts, functional dependencies, or insertion/update/deletion anomalies. If it does, normalization is usually needed.
- Repeated values: The same customer name or phone appears many times when a customer places several orders.
- Repeating groups: Order or item information is stored in repeated columns or rows instead of separate rows/entities.
- Mixed facts: Attributes with different dependencies (customer-level vs order-level vs order-item-level) are stored in one table.
- Functional dependencies: Attributes should be tested for what key they actually depend on (customer, order, or order-item).
- Anomalies: Insertion, update, or deletion anomalies indicate the need for normalization.
Analyze attribute dependencies
Ask what each attribute actually depends on. Use the dependencies to determine proper grouping of attributes.
- Customer-level: Customer details depend on the customer (e.g., name, phone).
- Order-level: Order details depend on the order (e.g., order_date, order_id linked to customer).
- Order-item-level: Item quantities depend on a particular order-item combination (order_id + item_id).
Example normalized design
A cleaner design would usually separate the stable entities so each table represents a single level of dependency.
- Customer: Customer(customer_id, name, phone, ...)
- Order: Order(order_id, customer_id, order_date, ...)
- OrderItem: OrderItem(order_id, item_id, quantity, ...)
When to consider denormalization
I would strongly consider normalization when the design causes repeated values, frequent update problems, or mixed facts that have different dependencies. Denormalization can sometimes be justified later for measured read-performance needs, but it should be a deliberate optimization rather than the starting point for a transactional design.