I need to create a database that, among other things, lets people choose 1 - N zip codes in the US.

Intuitively it seems best to make users a row and zip codes columns.

The problem I am having is that is like 42k columns. Which I am confident is outside most SQL DBs upper bound on columns.

I could have separate tables for each state. And then would have like 500-5K columns / table?

I mean that is doable, but the whole thing just seems a little ridiculous.

All thoughts, critiques, etc. are appreciated.

Also, any know the best place to get a list of zip codes (maybe broken down by state?)? Googling yielded some dated stuff. And so far I have USPS APIs for live verification. But I just need a static list.

Thanks everyone.

other than the PK every entry would be a boolean

why not a text column that contains the list of zips they chose?
or a combo table that has two columns, user_id and zip where user_id matches the id in the user column. You can have multiple rows for each user. Index the table by user_id and it will be fast as hell even if there's a ****load of entries.

How are you querying this?

Possible better options off the top of my head:

* (user, zip_code) where user can be duplicated and you only have rows for where the zip_code would be true. If its frequently changing, could maybe do (user, zip_code, is_selected) to avoid needing hard deletes.
* (user, list_of_zip_codes)

also there's a bunch of free zip code lists around

http://federalgovernmentzipcodes.us/

So most naturally, the proper data structure is a linked list. I just don't want to have to iterate over the list.

It's for use in a Django build website. So it will be queried by a Python script.

Thanks.

Its not frequently changing.

I am not sure that I follow. So a separate table for each user? Its better for the zip code issue, but seems like I would have a **** ton of tables that way...

just 2 tables

user table
id name
1 bob
2 fred

zip table
user_id zip
1 78703
1 78733
2 78704
2 78746

to get bob's zips:
select zip from zip_table where user_id = 1

or

user table
id name zips
1 bob 78703,78733
2 fred 78704,78746

Yeah, basically what blacklab said.

My point about querying is that if you want to do questions like "is Zip code 12345 selected for user X" you probably want to go the two table route (user table and user->zip mapping table). If you only ever care about querying all the zip codes for a user at one time, then the list of zip codes could be fine.

I need to be able to return a list of users that meet certain criteria for a given zip and it can't take forever.

I posted here, on reddit, and on stackoverflow. The 2-3 tables, users, zips, and a map seems to be the consensus.

Thanks for all the responses.