1

I'm creating a website and new to php and mysql. I just read as much as I could this morning and I've got the registration and login all working, along with a form a user can use to submit content.

My question specifically is how I should store the user submitted content. The content will basically be articles. As it stands now, I have a database for all of the users that stores their name, id, and password, but whenever someone makes an account I generate a new database with their to store any articles they submit. Are there any problems with this? I don't need the articles to be searchable at this point, but I would like to save the text of the articles, a category the article falls into, and the time it was published.

Is making a new database for every user that signs up a bad idea? Also, I was wondering about memory concerns. Are there any practical considerations I should take with regards to all of this content filling tons of server hard drive space?

Improve this question

3 Answers 3

Reset to default
2

When using databases the primary concern is SQL Injection. Use parametrised queries or escape all your data. Also in the users table don't store the plain text password. Use a hash and salt.

On the schema rather than creating a new database for each user I would use the same database with tables users (containing an id field) and an articles table containing the user id. This should be sufficent to know who the author of each article is.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I almost missed your answer up here. Short and too the point. Thanks a lot.
2

I was a CMS-exclusive developer for 5 years, so I've jumped through these same hoops.

Your solution is fine if you don't mind having to fully qualify the Database names or dealing with multiple connection switching. My company does it for a very complex application, but I do have to mention that it's frustrating and annoying when the connection fails and we do get connection error issues quite often.

In my personal work, I prefer using just one database and adding a user or company id field to each entry. That way, if for some reason I want to aggregate from one account to the next, it's a piece of cake. Trying to search across DB's would definitely not be a fun task at all. The main content field (body, content, etc) is nearly always a MySQL Text field to avoid running out of field room. Ensure that when inserting you do some sort of data cleansing to avoid sql injection. Mysql_real_escape_string usually does the trick.

Space-wise, unless you grow a VERY popular site you're not going to have issues. I've run several hundred medium-sized CMS's on one small server with no issue.

Improve this answer

1 Comment

Thanks for this answer. Qualifying the database names and connection switching problems are exactly the kinds of things I want to prevent. The bit about sql injection is also helpful, I was worried about that.
1

Creating a new database for every user is definitely the wrong approach in possibly any conceivable scenario since it defies the purpose of relational databases.

What you should (probably) be doing is to create an articles table that contains articles (title, content etc.), and a user table that contains user data. Depending on your data model, you can either have the author data stored in the db row for every article (foreign key), or in case a single article can have multiple authors, you can store the connections (relationships) in a separate table.

Improve this answer

2 Comments

Thanks a lot. This is probably how I'll end up doing it. At this point I don't necessarily need to search through the data, but this will allow me to do so if the need arises. This raises another question though - at what point will the database get too big to efficiently search through the articles for specific phrases?
That question is really hard to answer. In these days of cloud computing, cheap hardware, readily available know-how and tools for virtually limitless scaling, the main and major constraint is money. In any case, you most probably should not be worrying about hitting such limits unless you are trying to build something huge that will attract huge audiences yet your budget is very restricted and will remain such.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.