Unanswered: database design for an app lik friend finder. Need Help!
im currently doing the database design for an android app like friend finder. i'l be using google maps to display the location. location is tracked using gps. the functionality would be:
-locate u n all ur friends on the map
-give alerts when a friend is somewhere close like within 5km
-hide users location from certain people.
this is what ive come up with so far..
and rest of the details
users_location [visible, nt visible]
type [friend, rejected]
i've looked up in a couple of forums regarding the friendlist and this was the most repeated answer:
if the user accepts a friend request then 2 rows r created in the friendlist table
one with userid=1 and friendid=2
second with userid=2 and friendid=1
To insert a friend this would be the query: INSERT INTO friend VALUES ( 1, 2), (2, 1)
To get the list of friends of user 1, this would work:
SELECT friend_id FROM friend WHERE user_id = 1
In this design, it's easy and fast to get the list of friends for any user. However, one can say that there is duplicated data. This design highlights the nature of friendship, where if user 1 is user's 2 friend, the reverse is also true. is there a way i can prevent duplication? or should i go with this?
the attribute "type" in the friendlist wud be set to friend for both the rows in the friendlist table.
users_location is set to visible if the user wants that friend to see his location.
my questions are:
-do i need to a map entity if yes then what would be the relation between the map entity and the location entity?
-is the friendlist table correct?
thank you and i also wanted to add a feature wherein people can send messages to each other. For that will this be okay?
instant message(to the friends visible on the map):
Because when i showed the same thing to my project guide, she said i am missing many things here. I can't think of anything else that this table would need.