Struggling to design addresses table for fast querying

addressnormalizationoptimizationperformancequery-performance

I have two tables: addresses and locations.

Locations
zipcode
city
county
state

Addresses
line1
line2
city
county
state
zipcode  

Posts
Id
Address_id
Body

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.

Related Solutions

TheSQL query optimisation — multiple joins or select … where not in (select distinct…)

I would add an index on users_roles (rid, uid). In a many-to-many table with two columns (a,b), you almost always will need both indexes: (a,b) and (b,a) in one query or the other. I think this index would help in this query.
Try various rewritings of the query and the EXPLAIN EXTENDED they produce.
About your suggestions, the first is not correct (it will not show same results). For the second suggestion:

WHERE users.status = 1                                           -- Active users only

Yes, that's better than users.status <> 0. This change may have a better effect if there is an index on users (status) (and even more if there are not many active users). Optimizing queries with boolean columns (or ones that act as boolean) is not easy with B-trees.

  AND users.uid IN
     (SELECT DISTINCT uid FROM users_roles WHERE rid = 5)        -- Must be in rôle A

No. MySQL is known to have issues with column IN (SELECT ...), especially if the external table is big (and yours is 200K columns, so no, not good).

  AND users.uid NOT IN
     (SELECT DISTINCT uid FROM users_roles WHERE rid IN (6,8,9)) -- Not rôles B, C, D

Yes, that is one way to rewrite. The DISTINCT is redundant though.

  AND users.uid <> :users_uid                                    -- Not current user

Yes, removing the users.uid IS NOT NULL may help and does not change the result.

Other things you could try:

Moving the rid = 5 condition to the ON clause:

INNER JOIN users_roles users_roles 
  ON  users.uid = users_roles.uid
  AND users_roles.rid = 5

The (rewrite) to NOT IN can also be written with NOT EXISTS:

  AND NOT EXISTS 
      ( SELECT * 
        FROM users_roles ur 
        WHERE ur.uid = users.uid 
          AND ur.rid IN (6,8,9)
      )

Sql-server – Should I extract / normalize the mailing address despite requirements limiting number / type of addresses

Always bet on requirements changing, or being reinterpreted, at some point in the future.

Does the requirements documentation explicitly state that there are exactly two or just that there should be up to one primary and up to one secondary? If the latter then what could be meant is there could be many addresses of which up to one is the primary and up to one is the secondary and you should allow for more addresses.

Even if not, I would still err on the side of addresses being separate entities if you need to support more than one for any other given entity. The extra work now should be minimal, if you need to emulate the non-normal-form for the application logic you can by various means, and when the requirements change later your job will be a lot easier (and you can either impress the client/devs with your foresight or keep shtum and charge them as if the change request does require significant structural alterations in the DB).

Best Answer

Related Solutions

TheSQL query optimisation — multiple joins or select … where not in (select distinct…)

Sql-server – Should I extract / normalize the mailing address despite requirements limiting number / type of addresses

Related Question