Two Plus Two Publishing LLC Two Plus Two Publishing LLC
 

Go Back   Two Plus Two Poker Forums > >

Notices

Programming Discussions about computer programming

Reply
 
Thread Tools Display Modes
Old 06-20-2017, 01:13 PM   #1
rand
veteran
 
rand's Avatar
 
Join Date: Feb 2007
Location: 314-oc
Posts: 3,422
Google 'Big Table' like needs... in SQL? How to design DB?

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.
rand is offline   Reply With Quote
Old 06-20-2017, 01:27 PM   #2
rand
veteran
 
rand's Avatar
 
Join Date: Feb 2007
Location: 314-oc
Posts: 3,422
Re: Google 'Big Table' like needs... in SQL? How to design DB?

other than the PK every entry would be a boolean
rand is offline   Reply With Quote
Old 06-20-2017, 01:34 PM   #3
blacklab
adept
 
blacklab's Avatar
 
Join Date: Jan 2005
Location: Austin, Tx
Posts: 767
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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.
blacklab is offline   Reply With Quote
Old 06-20-2017, 01:35 PM   #4
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 21,564
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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)
jjshabado is offline   Reply With Quote
Old 06-20-2017, 01:38 PM   #5
blacklab
adept
 
blacklab's Avatar
 
Join Date: Jan 2005
Location: Austin, Tx
Posts: 767
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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

http://federalgovernmentzipcodes.us/
blacklab is offline   Reply With Quote
Old 06-20-2017, 01:50 PM   #6
rand
veteran
 
rand's Avatar
 
Join Date: Feb 2007
Location: 314-oc
Posts: 3,422
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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.
rand is offline   Reply With Quote
Old 06-20-2017, 01:55 PM   #7
rand
veteran
 
rand's Avatar
 
Join Date: Feb 2007
Location: 314-oc
Posts: 3,422
Re: Google 'Big Table' like needs... in SQL? How to design DB?

Quote:
Originally Posted by blacklab View Post
also there's a bunch of free zip code lists around

http://federalgovernmentzipcodes.us/
Thanks.
rand is offline   Reply With Quote
Old 06-20-2017, 01:57 PM   #8
rand
veteran
 
rand's Avatar
 
Join Date: Feb 2007
Location: 314-oc
Posts: 3,422
Re: Google 'Big Table' like needs... in SQL? How to design DB?

Quote:
Originally Posted by jjshabado View Post
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)
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...
rand is offline   Reply With Quote
Old 06-20-2017, 02:24 PM   #9
blacklab
adept
 
blacklab's Avatar
 
Join Date: Jan 2005
Location: Austin, Tx
Posts: 767
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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
blacklab is offline   Reply With Quote
Old 06-20-2017, 02:36 PM   #10
jjshabado
Carpal Tunnel
 
jjshabado's Avatar
 
Join Date: Jul 2006
Posts: 21,564
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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.
jjshabado is offline   Reply With Quote
Old 06-22-2017, 04:15 PM   #11
rand
veteran
 
rand's Avatar
 
Join Date: Feb 2007
Location: 314-oc
Posts: 3,422
Re: Google 'Big Table' like needs... in SQL? How to design DB?

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.
rand is offline   Reply With Quote

Reply
      

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Forum Jump


All times are GMT -4. The time now is 04:45 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ę 2008-2010, Two Plus Two Interactive
 
 
Poker Players - Streaming Live Online