0

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?

Improve this question
7
  • How are you linking post to address? You need to perform data design as a whole. Start with what you think is a clean data design (e.g. 3NF) and then only deviate if you have performance issues. At only 200,000 you are not even close to pushing the limits of a database.
    – paparazzo
    Dec 15, 2015 at 16:20
  • Edited question to reflect relation between address and post. Its just a simple one to many (more likely one to one)
    – Cristian
    Dec 15, 2015 at 16:24
  • Uh, still does not show how you are linking. So Posts has an addressID.
    – paparazzo
    Dec 15, 2015 at 16:25
  • Yes. Posts have an address_id while users themselves use many to many with type field in the pivot table.
    – Cristian
    Dec 15, 2015 at 16:26
  • 1
    If you were going to normalize this at all, it would make most sense to add LocationID to the Addresses table and remove the common fields from Addresses. City, state, zip is a rather finite set and hence will be repeated often in the Addresses table.
    – Solomon Rutzky
    Dec 15, 2015 at 16:34

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.