mysql - How To Design A Database for a "Check In" Social Service -

i want build "check in" service foursquare or untappd.

how design suitable database schema storing check-ins?

for example, suppose i'm developing "cheesesquare" people keep track of delicious cheeses they've tried.

the table items 1 can check in simple , like

+----+---------+---------+-------------+--------+ | id |  name   | country |    style    | colour | +----+---------+---------+-------------+--------+ |  1 | brie    | france  | soft        | white  | |  2 | cheddar | uk      | traditional | yellow | +----+---------+---------+-------------+--------+ 

i have table users, say

+-----+------+---------------+----------------+ | id  | name | twitter token | facebook token | +-----+------+---------------+----------------+ | 345 | anne | qwerty        | poiuyt         | | 678 | bob  | asdfg         | mnbvc          | +-----+------+---------------+----------------+ 

what's best way of recording user has checked in particular cheese?

for example, want record how many french cheeses anne has checked-in. cheeses bob has checked etc. if cersei has eaten camembert more 5 times etc.

am best putting information in user's table? e.g.

+-----+------+------+--------+------+------+---------+---------+ | id  | name | blue | yellow | soft | brie | cheddar | stilton | +-----+------+------+--------+------+------+---------+---------+ | 345 | anne |    1 |      0 |    2 |    1 |       0 |       5 | | 678 | bob  |    3 |      1 |    1 |    1 |       1 |       2 | +-----+------+------+--------+------+------+---------+---------+ 

that looks rather ungainly , hard maintain. should have separate tables recordings check in?

no, don't put users table. information better stored in join table represents many-to-many relationship between users , cheeses.

the join table (we'll call cheeses_users) must have @ least 2 columns (user_id, cheese_id), third (a timestamp) useful too. if default timestamp column current_timestamp, need insert user_id, cheese_id table log checkin.

cheeses (id) ⇒ (cheese_id) cheeses_users (user_id) ⇐ users (id) 

created as:

create table cheeses_users   cheese_id int not null,   user_id int not null,   -- timestamp defaults current time   checkin_time datetime default current_timestamp,   -- (add other column *specific to* checkin (user+cheese+time))   --the primary key combination of 3   -- becomes impossible same user log same cheese   -- @ same second in time...   primary key (cheese_id, user_id, checkin_time),   -- foreign keys other tables   foreign key (cheese_id) references cheeses (id),   foreign key (user_id) references users (id), ) engine=innodb; -- innodb necessary fk's honored , useful 

to log checkin bob & cheddar, insert with:

insert cheeses_users (cheese_id, user_id) values (2, 678); 

to query them, join through table. example, see number of each cheese type each user, might use:

select   u.name username,   c.name cheesename,   count(*) num_checkins   users u   join cheeses_users cu on u.id = cu.user_id   join cheeses c on cu.cheese_id = c.id group   u.name,   c.name 

to 5 recent checkins given user, like:

select   c.name cheesename,   cu.checkin_time   cheeses_users cu   join cheeses c on cu.cheese_id = c.id    -- limit anne's checkins...   cu.user_id = 345 order checkin_time desc limit 5