各种用户可以访问数据库中的同一个表，并且每个人都有不同的值吗？

Question

Okay, I didn't know how to put this in a sentence. I'm planning to build a web application that lets the users have a track of what books they have read. These books are in a table in MySQL database, along with a boolean column 'is_complete' that is set false by default. When the user clicks 'completed', the value will be set to true in the column.

My question is: Is this possible with a single table of books with the boolean column? Or do I have to create a table for each user with the boolean column and with foreign key(root.books)? What is the best way to get this done? I'm still learning.

P.S. I'm using Apache server, PHP and MySQL

Answer 1

Some quickly put together example sql of how you might structure a database for this purpose - trying to normalise as far as possible ( we could take normalisation a stage further but that would require another table and probably not worth it for the example )

You could run this in your gui so long as you don't already have a database called bookworms just to observe the structure for yourself.

drop database if exists `bookworms`;
create database if not exists `bookworms`;
use `bookworms`;


drop table if exists `publishers`;
create table if not exists `publishers` (
  `pid` smallint(5) unsigned not null auto_increment,
  `publisher` varchar(50) not null,
  primary key (`pid`)
) engine=innodb default charset=utf8;


drop table if exists `books`;
create table if not exists `books` (
  `bid` int(10) unsigned not null auto_increment,
  `pid` smallint(5) unsigned not null default 1,
  `title` varchar(50) not null default '0',
  primary key (`bid`),
  key `pid` (`pid`),
  constraint `fk_pid` foreign key (`pid`) references `publishers` (`pid`) on delete cascade on update cascade
) engine=innodb default charset=utf8;


drop table if exists `users`;
create table if not exists `users` (
  `uid` int(10) unsigned not null auto_increment,
  `username` varchar(50) not null default '0',
  primary key (`uid`)
) engine=innodb default charset=utf8;


drop table if exists `library`;
create table if not exists `library` (
  `id` int(10) unsigned not null auto_increment,
  `uid` int(10) unsigned not null default '0',
  `bid` int(10) unsigned not null default '0',
  `status` tinyint(3) unsigned not null default '0' comment 'indicates if the book has been read',
  primary key (`id`),
  key `uid` (`uid`),
  key `bid` (`bid`),
  constraint `fk_bid` foreign key (`bid`) references `books` (`bid`) on delete cascade on update cascade,
  constraint `fk_uid` foreign key (`uid`) references `users` (`uid`) on delete cascade on update cascade
) engine=innodb default charset=utf8;




insert into `publishers` (`pid`, `publisher`) values
    (1, 'unknown'),
    (2, 'penguin'),
    (3, 'faber cassell'),
    (4, 'domino'),
    (5, 'unknown');

insert into `books` (`bid`, `pid`, `title`) values
    (1, 1, 'miss piggy got caught shoplifting'),
    (2, 2, 'my life on crack by kermit the frog');

insert into `users` (`uid`, `username`) values
    (1, 'joe bloggs'),
    (2, 'fred smith'),
    (3, 'john doe');

insert into `library` (`id`, `uid`, `bid`, `status`) values
    (1, 1, 1, 1),
    (2, 2, 2, 1);

Then, when you need to query for a particular book, user or publisher a derivation of the following:

$sql="select * from library l
    left outer join users u on u.uid=l.uid
    left outer join books b on b.bid=l.bid
    left outer join publishers p on p.pid=b.pid
    where u.username='joe bloggs';";