Search code examples
mysqldatabase-normalization

How would you "Normalize" this SQL?

So, I will have a table called "REPS". In that table I will have this info.

ID

NAME

ZIP_CODES

I need to figure out how I should do the ZIP_CODES section. Should it be JSON format of zip codes? Like this

zip_codes('{"32343", "324234", "34234"}')

Then if I want to grab the name where ZIP matches any of them in ZIP_CODES? Or should ZIP_CODES have it's own table?

Solution

  • I would recommend having a table of zip codes and a table of reps. Then create a map table of sorts that has its own ID, the rep ID, and then the zip code ID.

    Table zipcode

    id INT PRIMARY KEY AUTOINCREMENT
name VARCHAR(64)
zipcode VARCHAR(15)  // account for postal codes from Canada etc

    Table rep

    id INT PRIMARY KEY AUTOINCREMENT
name VARCHAR(64)

    Table rep_zipcode_map

    id INT PRIMARY KEY AUTOINCREMENT
zipcode_id INT
rep_id INT

    Then if you wanted the list of zip codes serviced by a rep, you could service this request with a simple JOIN.

    An alternative approach would be to use a graph database of sorts. Sounds like it may be the right approach for this problem.