Hi everyone,
Thanks to those who replied and to others who might read this.
Let me re-state and clarify the issue we are facing. I know this is a bit long, but I want to be super-clear.
Introduction / Request for a Nonprofit
I am interning with a nonprofit, www.ChangingThePresent.org, which helps generate donations for nonprofits and for classroom resources. We are new to bubble, and we are not tech-savvy, so we (and the nonprofits and children) would be most grateful for a bit of your time and guidance.
What We are Doing
We have a database of 135k schools with six fields for each (school name, city, state, ID#, zip code, and link). We will be adding additional schools several times a week.
We are building a tool to help visitors find any specific school. Most people will search by City and State. A few will search by Zip Code. When they search, they will see the info (school name, city, state, zip, and a link) on schools that match that parameter.
APPROACH #1
Description
The simple approach is obviously to have a single large data table with all of the school data, and people would simply search it. Easy to add more schools. Easy to search.
Problem
The problem with Approach #1 is that we expect a very large number of searches—certainly half a million over the next six months, and likely several million. Bubble has not been able or willing to give us any info on what, for instance, “2 units of reserved server capacity” translates to. However, as an emerging nonprofit, we desperately need to minimize our costs, and so we need to minimize the load on the Bubble server.
The following discusses some approaches and questions for each.
APPROACH #2
Description
Bubble said that we would put less of a load on the server (and spend less money) if we put the schools for each state into separate tables. That way, a person searching (by city and state) for a school in a given state would only cause the server to search the table for that state, which has an average of 2,700 schools, instead of searching the entire table of 135k schools. (We would still need one big table with all of the schools, in order to accommodate people who search by zip code).
Question:
Is that suggestion sound (as it seems to be): that searching a table with 2,700 schools would put less load on the server (and cost us less) than searching a table of 135k schools? Is the load on the server generally proportional to the number of records in the table?
Problem:
The challenge with 50 separate state data tables is importing new schools several times a week. Doing a separate CSV upload for each of 50 state data tables would be a burden. Indeed, even generating 50 separate CSV files would be a pain).
Question:
Is there a way that we can import (several times a week) a single CSV with the new schools from various states, and have each of the schools automatically added to the appropriate state table?
APPROACH #3
Description
We also considered the following setup:
• Table A contains all the data for all the schools (school name, city, state, zip, ID#, and link)
• Table B has two columns. One column contains the states. The other column has, for each state, the list of schools that are in that state.
• If someone searches for Houston + Texas, then the server would look up Texas in Table B; see the list of Texas schools; then retrieve from Table A all of info for all of the schools that are in the Texas list and that are in Houston.
Question:
Is this better or worse (load on server, and search speed) than the 50 separate state data tables that are described in APPROACH #2?
Problem:
The challenge is similar to that of APPROACH #2. In this case, when we import new schools several times a week, we would want to import a single CSV with new schools from various states, and have them automatically added BOTH to Table A and also to the appropriate state lists in Table B.
APPROACH #4
Description
This would be similar to APPROACH #2. However, instead of the system allocating each new school to the appropriate state table upon import, all of the new schools would be appended to a single big data table that holds all the schools. After each import, a batch event would populate (or re-populate) each of the 50 state data tables with the schools that are in that state.
Actually, it seems that a similar approach might be used as an alternative to APPROACH #3: After each import, a batch event would add populate or re-populate the list of schools for each state’s section of in Table A.
Thank You
Thank you very much for sharing your time and expertise. We could not be more appreciative!
