Jump to content



Photo

MySQL table structure ?


  • Please log in to reply
5 replies to this topic

#1 Original Poster

Original Poster

    C++ n00b

  • Tech Issues Solved: 1
  • Joined: 15-July 08
  • Location: my room
  • OS: windows 7, backtrack 5, OSx 10.6

Posted 13 December 2012 - 04:43

I just wanted to get some opinions on how i should lay out a table ... I Have made a chat system which requires loging in ... that all works but now I want to add friend feature... although I have built a table for "friends" before I am not 100% sure that mine is efficient ... the best I have come up with so far is setting one column as the person who sent the friend request and the other as the acceptie .... any improvements on logic ? I was thinking possibly an array of user IDs within each users "friend" column ... but not sure if that would just be to much I think it would be ... opinions ?


#2 arlen2411

arlen2411

    Neowinian

  • Joined: 10-December 12

Posted 13 December 2012 - 16:57

I am not sure if this is the best solution but your table friends could have fields like this:
me_id
friend_id
accepted (bool)

So if you send an invitation to a friend, you know how to set IDs. If some one send you a request, then he will put his id as me_id....And when you are doing a SELECT to get you friends you can do sth like this:
SELECT * FROM friends WHERE me_id = x
SELECT * FROM friends WHERE friend_id = x
where x is your id
Then put return valuse to one table.

#3 OP Original Poster

Original Poster

    C++ n00b

  • Tech Issues Solved: 1
  • Joined: 15-July 08
  • Location: my room
  • OS: windows 7, backtrack 5, OSx 10.6

Posted 14 December 2012 - 04:01

that is somewhat what I was thinking ... but hmm still seems like that table would be more then it needs... but I guess its best option ..

#4 arlen2411

arlen2411

    Neowinian

  • Joined: 10-December 12

Posted 14 December 2012 - 10:01

Probably there is a better way for this, but I am not a DB specialist. This is the way I would do it.

#5 n_K

n_K

    Neowinian Senior

  • Tech Issues Solved: 3
  • Joined: 19-March 06
  • Location: here.
  • OS: FreeDOS
  • Phone: Nokia 3315

Posted 14 December 2012 - 16:14

I can't really work anything out without seeing your full table structure and what it is you're trying to do but remember, you want to normalise your database as much as possible to make it more efficient, so add linking tables and whatnot.

#6 Tuishimi

Tuishimi

    Michinator

  • Joined: 19-November 10
  • OS: Windows 8

Posted 14 December 2012 - 16:28

I'd probably do the same as arlen... Do you want to provide any message with the invite? If you think the table will grow huge, I'd index both ID's... Tho' it probably isn't really THAT necessary since both fields will be integers and any number of internal DB sorting algorithms will probably handled queries easily.



Click here to login or here to register to remove this ad, it's free!