Skip to main content

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.
Back to top