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

问题描述：

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

好的，我不知道怎么把它放在一个句子里。我打算构建一个Web应用程序，让用户可以跟踪他们阅读的书籍。这些书在MySQL数据库的表中，以及默认设置为false的布尔列'is_complete'。当用户点击“已完成”时，该值将在列中设置为true。 p>

我的问题是：这是否可以使用布尔列的单个书籍表？或者我是否必须为每个用户创建一个包含boolean列和外键（root.books）的表？完成这项工作的最佳方法是什么？我还在学习。 p>

P.S。我正在使用Apache服务器，PHP和MySQL p> div>

答

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';";

答

you need books table and users table and table books_users with id's of book and id of user who clicked is_completed (and with that you don't need boolean is_completed)

答

Impossible. The database looks the same for any user.
You don't have to create a table for each user either! That would be a really stupid solution.

Just create a table with two columns: user ID and book ID with proper links/joins/etc. If the user have already read the book, add a row inside this table with the corresponding user and book IDs. To check if the user have already read the book, just look for a row in this new table with the corresponding user and book IDs. If such a row is in the table - the user have already completed the book. If not - he had not.
As the row can't create itself, the default value is that he have not read the book yet. By removing the row you may also "change the user's history" - that will mark the book as incompleted by that user.

答

Yes this is possible. Set it up so when each user registers each book is set to false as you were stating. Here is an example:

As each user is added, the book_id column is set to false for that user.

As others stated, of course you will need the Users table, but if you wanted, you could just transfer the user_id and setup a table like pictured above with each book (book_id into multiple columns) set to false. It would be easier to manager and understand.

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

相关推荐