I'm trying to build a home library management client/server application, with the following features:
- One server (back-end+DB) can serve multiple users
- Several different front-ends can connect to the back-end through an API and get JSON information of they have the correct access rights.
- Each user has
- Normal ID information (email, password, and so on)
- A number of 'contacts' to/from which he can lend/borrow books.
- A list of books he owns and a wish-list of books.
I'm concerned about designing the database organization, as this is the first 'big' project I do requiring more than two or three tables in relationship. I have read this, this and this but it's hard to google such an open question, and I am unsure what features I am looking for in the possible documentation sources.
My question is the following: which of the following (or other) database scheme is best, according to: security, speed, and ease of maintenance in that order.
Per user information: Each app user has an equivalent db user and tables containing the book, author, genre, contacts and loans. Even though at small scale it is easy to implement and secure, IMHO this seems a very inefficient way to do it, mainly for scaling reasons: firstly that means I'm going to store duplicates of books if two users own the same book, which is a waste, and also if I have a great number of users than I anticipate a management hell (see this question)
One user to rule them all: The back-end can talk to the DB using one 'myapp' user. That means only having one table for all books, users, authors, etc... But I see the following problem: this would work beautifully if the whole world used the app - but my users will mainly lend books to external people (the aim is to have a mobile app that allows selecting a contact as borrower). I'm wondering how to store per-user information that would normally require a complete table, such as books owned and metadata (score, times read, date(s)...), or a list of contacts to/from which books are currently loaned/borrowed.
- A naive solution is just to add the contacts as 'unregistered' users, so they have a primary user key and we can have one huge 'loans' table. That however causes security concerns: I'm not sure how to forbid users from getting contacts of others...
- Another solution could be to have 'system' tables containing info about registered users and such, 'books' tables containing the books' (meta)data, and then myapp_(user primary key) tables with user-specific information.
- Something else?
I've never really done any serious webservice development and as I'm doing this for fun I have no idea how things are 'usually' done. Any suggestions, ideas, councels, and clarification demands are welcome!
EDIT: Here is an ER-diagram of the data I want to store. Also, having read the MSDN article in the comments (the whole series is interesting actually) I think I'm now looking to choose between:
- Big tables for everyone and using views to restrict access;
- Creating a per-user scheme.
I also think it might be a good idea to be more precise on my aimed public: I want to create a small open-source server that can support a small (< 100) number of people. The clients will be able to configure to what server they want to connect, so anyone can host their own; the ability to do horizontal scaling is an aim if possible, but I don't expect enough users to choke up a single DB instance.
P.S: side question: from an ethical POV, should I delete a contact from the contacts table if he does not have any books left, to keep as little information as possible on other people than my users - or should I privilege usability and keep them? (After all some social networking companies don't give a damn...)
