What is the proper way to store a list of zip code in MYSQL?-CodePudding

I own a property preservation company. I'm rolling out an update to our current system. This update will allow our contractors to enter zip codes of areas they cover.. What would be the proper way to store this data in MYSQL?

Each user can cover multiple zip codes. So, I need to collect these zip codes and store it in the database for this user. Also, what would be the best way of collecting this list of zip code (HTML wise)? an input text, textarea or what ?

I appreciate all the help!

CodePudding user response：

Use a table like this:

CREATE TABLE contractor_coverage (
    contractor_id INT,
    zip_id INT,
    FOREIGN KEY contractor_id REFERENCES contractor (id),
    FOREIGN KEY zip_id REFERENCES zip_codes (id),
    PRIMARY KEY (contractor_id, zip_id)
);

Of course, adjust the table and column names in the REFERENCES clause to match your actual tables.

There will be a row for each zip code that each contractor covers.

Designing the UI is too broad for this Q/A site. You could use cascading menus that allow the contractor to narrow down to state and city, then use a multi-select with all the zip codes in that city. This would make errors less likely than using text fields. You could ask for advice on User Experience.

CodePudding user response：

I would just use a textarea to collect the zip codes. If you expect a high number of zip codes for each user you could consider allowing the user to upload a .csv file.