We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each of these entities. Also we need to store latitude and longitude of Registered Users, Retail Outlets, etc.
Initially we designed the tables as shown below. We created a separate Address tables as the format of the address will be same for Users, Retail Outlets and other entities.
Now we realized that we need to search for Retail Outlets near to User's current location(CurrentLatitude, CurrentLongitude)
So we would need to run a query like
Now we think that above query would run slow because the number of users will be much more than the number of retail outlets. From DB Design/Performance perspective which of the following should be done.
Create index on Latitude, Longitude in Address table and on AddressID in RetailOutlet table. So there would be no change in table structure.
Move Latitude, Longitude from Address table to RetailOutlet and User tables. Also create index on Latitude, Longitude in RetailOutlet table. So the table structure would be as shown below.
Change table structure as shown below and create index on Lat, Lng in UserAddress table and on UserAddressID in User table.
Introduce DB redundancy by copying Lat, Lng from Address table in the RetailOutlet table and create index on Lat, Lng in RetailOutlet table. With this option there will be no change in Address table and we will be able to use it for storing User address without change. Also we may be able to use it for storing Address of other entities.