Mysql – How to structure a database that organizes post by location like craigslist

You need three tables - User, Interest and an associative table for the many-to-many relation between them (a User can have many Interests and an Interest will have many Users), call it UserInterestLink or something. User and Interest will have autoincrement int fields as a surrogate primary key, UserInterestLink will have FKs to both User and Interest, like so:

|=================================================|
| TABLE: User                                     |
|=================================================|
| Name       | Type                               |
|=================================================|
| UserID     | int, autoincrement, PK             |
| << Other User fields here >>                    |
|=================================================|


|=================================================|
| TABLE: Interest                                 |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, autoincrement, PK             |
| << Other Interest fields here >>                |
|=================================================|


|=================================================|
| TABLE: UserInterestLink                         |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, FK to Interest.InterestID     |
| UserID     | int, FK to User.UserID             |
|=================================================|

I didn't mention the PK on UserInterestLink, for efficiency it would be a compound PK on (UserInterestLink.InterestID, UserInterestLink.UserID), but there's also an argument to be made for a seperate surrogate primary key (which I personally think is a waste of space, unless you need to allow for multiple UserInterestLinks between one User and one Interest). For a purely associative table in a many-to-many relationship, though, the compound primary key should work fine.

Edit: The complicated part of this isn't in the relational design, it'll be in the application code, because you'll need best-match/partial matching in order to show users the groups that most closely match their interests (rather than having accidental splinter groups all over the place). And if you want to out-Facebook Facebook, you'll need a smoother and better user experience than they offer.

You can change your post_order column to a float and update the value with a calculation using the post_order values from the posts you want to end up between.

Example:

PostID, PostOrder
1       1
2       2
3       3

If you want to move PostID = 3 to be sorted between 1 and 2 it would be

PostID, PostOrder
1       1
3       1.5
2       2

Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID of the post to be moved and PostID of the post you want to end up behind.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

create table Post
(
  PostID int primary key,
  PostOrder float unsigned not null unique,
  check (PostOrder > 0)
);

insert into Post(PostID,  PostOrder) values (1, 1);
insert into Post(PostID,  PostOrder) values (2, 2);
insert into Post(PostID,  PostOrder) values (3, 3);
insert into Post(PostID,  PostOrder) values (4, 4);
insert into Post(PostID,  PostOrder) values (5, 5);
insert into Post(PostID,  PostOrder) values (6, 6);
insert into Post(PostID,  PostOrder) values (7, 7);
insert into Post(PostID,  PostOrder) values (8, 8);
insert into Post(PostID,  PostOrder) values (9, 9);
insert into Post(PostID,  PostOrder) values (10, 10);

//

create procedure MovePost(MovePostID int, AfterPostID int)
begin

  declare AfterPostOrder float;
  declare NextPostOrder float; 


  set AfterPostOrder = (select PostOrder 
                        from Post
                        where PostID = AfterPostID);

  if AfterPostOrder is null then
    -- Move first
    set AfterPostOrder = 0;
  end if;

  set NextPostOrder = (select min(PostOrder) 
                       from Post 
                       where PostOrder > AfterPostOrder);

  if NextPostOrder is null then
    -- Move last
    set NextPostOrder = (select max(PostOrder) + 2
                         from Post);
  end if;


  update Post
  set PostOrder = (AfterPostOrder + NextPostOrder) / 2
  where PostID = MovePostID;


end

Query 1:

call MovePost (7, 3);    -- Move 7 after 3
call MovePost (8, 3);    -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10);   -- Move 2 after 10

select * 
from Post
order by PostOrder

Results:

| POSTID | POSTORDER |
----------------------
|      9 |       0.5 |
|      1 |         1 |
|      3 |         3 |
|      8 |      3.25 |
|      7 |       3.5 |
|      4 |         4 |
|      5 |         5 |
|      6 |         6 |
|     10 |        10 |
|      2 |        11 |