SQL Schema recommendations

Question

I'm fairly new to SQL, especially in using it with PHP, and I'm currently working on an app that allows users to track their reading. The first version will have a simple calendar that allows you to input what day you start and when you should be finished by.

My question is on my user login and management system. Right now, I have my SQL database setup with three tables - Users, Books, and Events. Users is pretty self explanatory, Books is just a collection of Books and a variety of info, and then Events has this setup.

|  UserID  |  BookID  |  StartDate  |  EndDate  |
|    1     |    2     |   6-10-15   |  6-12-15  |
|    1     |    3     |   6-13-15   |  6-16-15  |

Right now I'm planning on my app being used by small group of people, but I'm worried that if I want to expand it that my SQL database might limit it. My question is: If I end up with thousands of users and millions of 'Event's, will my current schema be able to handle the load?

I'm just worried that when I hit a large number of events, I'm going to have trouble getting the data back quickly. I considered creating a new database for each user, and having a shared common database with books and such, but I don't know how I would handle sharing info/messages/books between 'friends'.

Off the bat, the single database with Users/Books/Events will get the job done. I'm just curious as to other peoples opinions on further separation of data.

Answer 1

SQL Server, with a properly configured database schema, can handle tens or hundreds of millions of rows with fairly good performance. Of course, it depends on what you're doing... if you're just looking up events by date/user/book, then this schema will be fine. You'll need to properly index the right columns, but millions of rows are no problem.

I suspect, though, that you're trying to link users to events, and that there might be more than one user per event (and more than one event per user). In that case, you'd want a junction table to point users at distinct events.

Users
  UserID int

Events
  EventID int
  <dates, or whatever>

EventUsers
  UserID int (Foreign key to Users.UserID)
  EventID int (Foreign key to Events.EventID)