0

I'm new to web developing and I'm trying to learn web developing by doing projects .

I decided to build web site that you can search name of a book and result will be list of who users read this book .

I know this is not useful and just want to learn

My problem is that i don`t know how to store list of books that users read in database .

I just find this two ways by myself to make database that can do this for me but i'm sure that this are not fastest way .

1-I can store them all in one table with readers's ID and very soon they will become too many rows and makes my site slow

2-I can make a table for each user and then search all of tables for one specific book and then i can return name of owners of the table which contain that book and i think searching whole tables of my DB is very slow too.

I will really appreciate any one who can answer my question . I'm really stuck at this point of my site ;)

mr programmers dasasdasd
  • 65
  • 6
  • 4
    'too many rows' for a database is a lot, lot higher than you would think. Tables for each user is [not a good idea](http://stackoverflow.com/q/7544544/505722). – Jim Jun 12 '14 at 14:45
  • I know my website is just for learning and there are not many users but think of 1 million users with 100 books it will become too much to search it wont ? – mr programmers dasasdasd Jun 12 '14 at 14:47
  • 2
    Sure. With 1 million users you will start hitting performance issues *everywhere* but by that point you'd have more resources to solve these problems. In your example there would be 1,000,000+ tables which would have **much** worse performance. – Jim Jun 12 '14 at 14:51
  • 1
    Sounds like a situation for a many to many relationship. If you don't know what that is or why I said it, I've heard good things about the book, Database Design for Mere Mortals. – Dan Bracuk Jun 12 '14 at 15:25

1 Answers1

2

How about

users table
-----------
id
name
other_stuff


books table
-----------
id
title
author
isbn_number
release_date
other_stuff


lendings table
--------------
book_id
user_id
lended_from
lended_to

Using this design you can see who has a specific book at the moment with

select u.name
from users u
join lendings l on l.user_id = u.id
join books b on l.book_id = l.book_id
where b.title = 'moby dick'
and now() >= lended_from 
and lended_to is null
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • My first idea is just like your answer but i'm not sure if this is best way to store books read by millions of users if each one reads 100 books it will become too many books it won't? – mr programmers dasasdasd Jun 12 '14 at 14:49
  • 2
    No, a database can hold easily billons of records. With proper indexing this is super fast. – juergen d Jun 12 '14 at 14:51
  • 2
    +1 @hamidreza eyvanaki: This is the normative pattern for a many-to-many relationship. You may have a lots of rows, but relational databases are designed to handle lots of rows. For performance, relational databases make effective use of appropriately defined indexes. That is, the database doesn't necessarily need to examine every row in the database to find the ones its looking for. Using an index, the database can efficiently narrow in on the particular rows it needs to satisfy a query. – spencer7593 Jun 12 '14 at 15:52