(Part 1) PHP & MySQL: How to create a resource system
One of the biggest problems faced with content rich websites is how to store the data than enriches them. Therefore, I’d like to offer my solution – something I call ‘the resource system’.
Specifically, this is designed for PHP & MySQL, but in theory could be adapted to work with other platforms.
The idea is to take the large part of the data (the content) and place it into a set of tables where it can be managed by a resource system.
Suppose we have a table called ‘posts’, which stored a unique ID, title, date and content for each row. Now suppose we used this table to store the daily posts from our members. If we had 1000 members who posted daily, it wouldn’t be long before our table reached a phenomenal size.
When you query that table for a list of the entries, you’ll almost never use the content field. Separating the content from the rest of the information allows for quicker alteration of what we’ll refer to now as the ‘log’.
This calls for a system that can manage content in its bare form – text. We need to be able to take the content, submit it to a function that returns a resource-ID that we can store in the ‘log’. Then later retrieve that data using the resource-ID.
Making the system structured like this will allow for further optimisation later using a utility called MEMCACHED, but we’ll leave that for a later post.
There are many advantages to this system, that we’ll discuss in the next post.