Struggling to design addresses table for fast querying


I have two tables: addresses and locations.




All my users are required to provide at least a personal address and some need to provide the business address.

My users can create two types of posts: first requires to have a full address and the second requires just the location (state, city, zipcode).

Now, the issue comes because I need to allow searches for posts based on location. The search is always based on city state and zipcode (not street numbers).

Does it make sense to place all the addresses inside the Addresses table and for the posts that need just the state, city and zipcode to leave the other fields blank? Would this slow down posts searches based on addresses?

Would having in my DB 5.000-10.000 users and maybe 200.000 addresses really slow down the process of searching for first type of posts based on the address?

Best Answer

A single address table will be a clean and efficient solution to maintain and execute queries against. From the information you've given the main reason you might not want this is you have two well defined use cases, one of which expects additional fields to be completed while the other accepts a significant number of blank fields.

Handle this in your application logic - users should never be allowed to progress or commit data if all required fields are not validated in the front end.

If you do not already have a design that makes the address/user type clear (e.g. FK to user with a user type) you could always add a bit flag to your address table that defines the address type. If you store both a personal and a business address you may need this anyway.

AddressIsPersonal BIT NOT NULL

This will allow you to ensure a unique PK and to query Address table directly for data checks and searches. For example if AddressIsPersonal = FALSE means we expect to see complete addresses returned and we may only be searching for business posts.