Normalizing a 12,000-Row Vendor Table
Vendor onboarding currently lives in one table with 12,000 vendor records. Contacts are stored as three comma-separated text fields (up to 40 contacts per vendor), addresses as free-text strings, and status as a string with 8 values. Reporting is slow and duplicate vendors keep appearing. The business wants to track certifications (each vendor can hold many, each certification can belong to many vendors) and to assign onboarding tasks to internal staff. A junior developer suggests adding more text fields and a few choice fields, arguing that references will slow the form down. Go-live is in six weeks.
1. Recommend a data model for contacts and addresses, and address the claim that references slow the form.
Read the worked answer
Model it as one-to-many: a Contact table with a reference to Vendor, and an Address table with a reference to Vendor (or a single Location table if addresses are reused across vendors). Show them as related lists, not as 40 fields on the vendor form.
On the performance claim: references do not inherently slow a form. What costs time is how many related lists and dot-walked fields render, and whether client scripts synchronously query. 40 related rows is trivial. Comma-separated text, by contrast, cannot be filtered, joined, reported on, or deduplicated without scripting every report.
Trade-off: more tables mean more forms and ACLs to maintain, and a related list per vendor adds a query per form load. That cost is bounded and measurable; the cost of string parsing grows with every new report. Normalize, and if load time matters, trim the related list to the five columns users actually need.
2. Certifications are many-to-many and carry their own data. How do you model this, and what do you watch for?
Read the worked answer
Use a first-class junction table (e.g., vendor_certification) with references to Vendor and Certification, plus the relationship attributes: date earned, expiry, certificate number, verification status.
Why not a plain many-to-many: a pure m2m table holds only the two references. The moment you need expiry dates and status per pair, the relationship itself is an entity and deserves fields, ACLs, and a form.
Watch for:
- A unique index on (vendor, certification) unless re-certification history is intentional - if it is, keep history and index on (vendor, certification, earned_date) instead.
- Reporting: expiry-driven reminders need an indexed date field and a scheduled job, not a calculated field on the vendor.
- Deletion: decide cascade behavior explicitly; deleting a vendor should not silently strip certification records that auditors need.
3. Status: choice field or reference table? The 8 values change yearly and carry different SLA durations.
Read the worked answer
Reference table wins here because the values carry attributes: SLA duration, active flag, display order, and probably a 'closed' flag. A choice field can hold only the label and value; encoding SLA duration in a client script or a business rule switch statement means every yearly change becomes a code deployment.
What you gain: business users update durations without an update set, reports join cleanly, and inactive statuses can be retired without deleting history.
What you give up: an extra table, extra ACLs, and a lookup on every form. That is small at 8 rows.
Judgement rule: static, label-only values with no behavior → choice. Values that drive logic, reporting, or change independently of the release cycle → reference table. Here the SLA attribute settles it.