Avoiding Growing Pains of a Database Driven Website

How Can I Ensure Fewer Headaches If My Site Makes It Big?

[itledi]

I'm building my first truly database driven website, and I'm using PHP and MySQL behind the scenes. I've used databases before, but more to record information, not to use its content to build the pages.

My structure will involve one main table, which stores user created content. This content is then accessed to create my site's pages. Beyond storing and retrieving content I'll also be updating rows to reflect content views. I will rarely delete created content.

What's the best, most scalable way to go about this project? It just seems that this one table is my site's Achille's heal. Can a table grow unlimited in size without any issues? Won't it make it more and more difficult to back it up? Would it run sluggish?

I can't logically divide the table into smaller tables. I guess I could divide the content by user entries. The 0 - X inserted content is in the first table, X+1 - Y in the second, as so forth. But this would seem like a major headache when retriving the content.

How would I go about planning the database architecture of my site, and allow it to scale without growing pains?

Thanks

[lammert]

Can a table grow unlimited in size without any issues?

Yes, at least if you use proper indexes. You might run into table lock problems when there are many concurrent reads and writes to the same table, but this can be solved by using the right database engine or separating writes and reads on different servers (master-slave configuration). But once you will begin to start about master-slave configurations your database has already grown real big.

Although one table is simple to maintain, the single table approach is also where your long-term maintenance problem is. One table is not sufficient. You need extra tables that define the relation between the pages, categories, menu structures etc. The relation between pages should be stored outside of each page to make taxonomy totally independent of content. If you create your pages with many embedded <a href=...> links to other pages, it may be a real pain if you just want one page removed or renamed. You have to find all other pages where that one page is linked from. Seperating interlinks and content will help you to maintain your growing site easily.

Separating content and links doesn't mean that you can't have links directly from your content to other content. Look at wikipedia for a smart solution. They use the [[linktext]] directive to define interlinks between pages. A post processor transforms these boxes to real links, just before the page is sent to the browser.

[physics]

One word: normalization.

[databasejournal.com...]

A nice normalized table structure in the beginning should save you countless headaches down the line.