1

This question was voted inappropriate for stackoverflow, so I'll ask here. I'm considering designing a CMS where content is centered around what the users submit. Built on polyglot, php, sqlite3 for user content, and xml for CMS settings.

My question is would using a slite3 database for each user within one web application be viable? Or would it be too hard on server resources? In some ways it seems it would be easier especially on large sites, but then what about comments?

2

My question is would using a slite3 database for each user within one web application be viable?

It is "technically" possible but highly unorthodox. Using sqlite this way would be well outside the assumed "solution space" for which it was designed.

So, viable ? No.

Or would it be too hard on server resources?

It is such an unexpected use-case I doubt it's ever been benchmarked.

In some ways it seems it would be easier especially on large sites, but then what about comments?

The standard practice would be to design the database to hold all the users data in one database. Each table that referenced any data specific to one user would have a column with a value that uniquely identified that user.

In this way you can safely store all the users data mixed in together in one database.

Here is a very basic example that might help illustrate the principle.

All the following tables are in 1 single database

TABLE = "users"
FIELDS
 "user ID"
 "user login nickname"
 "user password_hash"
 "user email"
 "user name"


TABLE = "forum threads"
FIELDS
 "thread id"
 "thread title"

TABLE = "forum thread comments"
FIELDS
 "thread id"
 "comment id"
 "comment user id"
 "comment datetime"
 "commnet text"
  • 1
    Good answer! This is a rather simple schema issue. Perhaps the OP should study database schemas and queries a bit more before getting started. Your answer provides a perfect example. I am not sure if there is a CAD for this database, but using MySQL and MySQL Workbench for a model may help to build a schema and code for the target database if it uses standard SQL. – closetnoc Jun 10 '15 at 2:00
  • @closetnoc Thanks. OP definitely needs to hit the books. Perhaps we should provide help with finding resources? A few sqlite tools are discussed here stackoverflow.com/questions/3332250/… I've used SQLiteStudio myself extensively with no complaint. Love portable apps. I don't think any of them have visual entity design tools however. – John McNamara Jun 11 '15 at 11:20
  • You are right. I was too tired to do much thinking, but I did see SQLiteStudio at sqlitestudio.pl and wanted to check resources first. If you like it, that is good enough for me! I did do a bit of reading on the database too. It seems like a good database, but I think I would still use MySQL. I do kinda bigish stuff not very well. ;-) I need all the help I can get!! This site may help the OP too! docstore.mik.ua/orelly/index.html The books are not a direct match, but can help with theory anyway. – closetnoc Jun 11 '15 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.