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