Designing a MySQL Table from a CSV
Design the destination table from the meaning of the data, not merely its appearance in a spreadsheet.
| CSV field | MySQL type | Rule |
|---|---|---|
| match_id | VARCHAR(20) | unique and required |
| team_name | VARCHAR(100) | required |
| wins | INT | zero or greater |
| losses | INT | zero or greater |
CREATE TABLE team_results (
result_id INT AUTO_INCREMENT PRIMARY KEY,
match_id VARCHAR(20) NOT NULL UNIQUE,
team_name VARCHAR(100) NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL
);
A surrogate primary key supports database operations; the unique source identifier prevents accidental duplicates.
Design questions
- Does one CSV row represent one entity or a relationship?
- Which values repeat independently and belong in related tables?
- Which constraints protect data quality?
- Will repeated imports insert, reject or update existing records?
Document and justify the chosen policy.
Check
- Types and lengths fit expected values.
- Required fields are constrained.
- Duplicate behaviour is defined.
- Relationships reflect the solution’s data needs.