I’ve got a lot of data (~75k records) that won’t fit into a single table, so I need to separate it out into multiple tables. It’s basic contact info (name, address, city, state, zip, phone, etc). We’re doing a zip-code search on the front-end (via the API), so with a limited data set (smaller than the final batch of data), it’s easy enough to query the zip field for the passed value. I was figuring I could break it out into states as that’s an easy & logical breakdown.
However, now that I need to break this out into multiple tables, I don’t want to search ALL of the tables for that single value. Is there a way to create some kind of lookup table that would accomplish this? I imagine something where each record is a pointer to the other tables, along with say, min & max values from that table (eg. min zip & max zip)? This way I can search the lookup table for the row whose min is less than the desired zip AND whose max is greater than the desired zip. Then, I can take the resulting table and search that one for my actual data.
Or, if there’s a way to create a formula that references another table, so I could manually set up the lookup table to be State Name, min Zip => min(state.zip), max Zip => max(state.zip) or something.