dtx9763 2016-08-11 21:26 采纳率: 100%
浏览 161
已采纳

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

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

  • 写回答

4条回答 默认 最新

  • dqm4977 2016-08-11 23:17
    关注

    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';";
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题