pjNiMsys

1ALTER TABLE seats
2ADD CONSTRAINT FK_seats  
3FOREIGN KEY (user_id) REFERENCES members(user_id)  
4ON UPDATE CASCADE  
5ON DELETE CASCADE;
6
7ALTER TABLE seats
8ADD CONSTRAINT FK_seats  
9FOREIGN KEY (seat_ID) REFERENCES members(seat_ID)  
10ON UPDATE CASCADE  
11ON DELETE CASCADE;
12	
13CREATE TABLE `members-table` (
14   user_id int(8) not null auto_increment,
15   user_name varchar(30) not null,
16   user_pass varchar(255) not null,
17   seat -- your type choice, should be nullable if not seated
18);
19	
20UPDATE `members-table` SET `seat` = NULL;
21	
22CREATE TABLE `seats` (
23   id int(4) unsigned not null auto_increment primary key,
24   row int(2) unsigned not null,
25   col int(2) unsigned not null,
26   UNIQUE(row, col)
27) ENGINE InnoDB;
28
29CREATE TABLE `members` (
30   user_id int(8) not null auto_increment primary key,
31   user_name varchar(30) not null,
32   user_pass varchar(255) not null,
33   seat int(4) unsigned null,
34   FOREIGN KEY(seat) references seats(id) on delete set null on update restrict,
35   UNIQUE(seat)
36) ENGINE InnoDB;
37	
38SELECT COUNT(*) AS occupied FROM members WHERE seat = (SELECT id FROM seats WHERE row = :ROW AND col = :COL);
39	
40SELECT MIN(id) FROM seats WHERE NOT EXISTS( SELECT seat FROM members WHERE seat = seats.id);
41	
42UPDATE members SET seat = NULL;