0

I am preparing to write a database system using PHP and MYSQL which will store every piece of info sent to it as an individual row. Each row will store several piece of meta data (time stamp, who created it, version number, attribute number) and one piece of 'true' data (file name, phone number, username, etc.) Rows my also be used to store serialized data. The goal is to create a system that is easy to incrementally back up, search on single rows, and review based on its state at a particular date. Is this an effective design or should I alter my approach?

The database will be used to drive a custom CMS system which is intended to store a interconnected system of data. It will involve customer data, location data and job data. We want to be able to track individual database changes based on time so that we can maintain an on-site backup (the system will be cloud based) which we can use in the event that we lose internet connectivity. The system will be strongly permission based, only allowing people to see/change information that they are authorized to see. Most of the data will be viewed in a timeline format, based on the time that the info is entered into the system.

Improve this question
4
  • 1
    Far too little information here. It may be effective, it may not be. It depends on what you are trying to accomplish.
    – GrandmasterB
    Mar 24, 2014 at 17:29
  • This database design is called a vertical database. It will only have a few tables, but have a huge number of rows for the tables.
    – BPugh
    Mar 24, 2014 at 18:50
  • I built another site using a vertical database and there was one table that grew huge. This time, I want to use several table for general data storage instead of just 1. I plan to break up the data based on attribute type and the type of object (person, place, job, etc.) that the data belongs to
    – Hoytman
    Mar 24, 2014 at 18:59
  • Entity-Attribute-Value Model
    – Robert Harvey
    Mar 24, 2014 at 20:10

2 Answers 2

Reset to default
1

Based on the information provided:

The database will be used to drive a custom CMS system which is intended to store a interconnected system of data.

'Interconnected' implies relational, which is why you should use a traditional relational database design.

It will involve customer data, location data and job data.

Already there's three tables at least -- customer table, location table with a foreign key to customer, and job table with a foreign key to another related table -- user? employee?

We want to be able to track individual database changes based on time so that we can maintain an on-site backup (the system will be cloud based) which we can use in the event that we lose internet connectivity.

Backups for purposes of disaster recovery should probably be handled at a different "layer" (infrastructure) than database design. Nonetheless, I still don't think a vertical database is the best solution for this -- you could use a separate logging system via extra tables or writing to a file if you want to use an approach like that. But I think including the created_by, timestamp, start_date, end_date, etc. as metadata in your data tables is the best approach for maintaining history.

The system will be strongly permission based, only allowing people to see/change information that they are authorized to see.

I'd recommend building or using a pre-existing role-based authentication system for this. You could get down to table- or row-level security by storing the role(s) of the individuals that should be able to read/modify the data alongside your data or in separate user/role tables. How you'd set that up is probably a much deeper discussion than we could have here.

Most of the data will be viewed in a timeline format, based on the time that the info is entered into the system.

ORDER BY timestamp :)

I'd strongly recommend looking into existing CMS systems before you get too far into planning on your own custom one. Many of them have a lot of the capabilities you've mentioned.

Improve this answer
0

This approach is sometimes used in unusual situations, such as having tables that would have a huge number of columns each sparsely populated, or having user defined data where the columns are not known until the application is running.

The reason it isn't used more is that it's not how relation databases like MySQL are designed to work, and therefore doing so is problematic. The way indexes work is based on columns, and if your column is really holding a bit of everything then you will have to index everything or nothing, and the query optimizer will have a hard time determining when it will be useful. You are depriving the database of the accurate meta-data it would otherwise have, and this will cost you in performance.

Furthermore, the SQL you write will be more difficult. If you want to get a bunch of related data, say everything from one web page, you will have to group by... something that you put in the database to allow to group. Most likely you will end up having a table table, a row table, a column table, and a value table, and group by the row table. This is a pain.

As far as your stated goals of being easy to incrementally back up, search on single rows, and review based on it's state on a particular date, it doesn't help back-up, it doesn't make it easier to search on a single value (and makes it harder to search multiple values in a logical row), and at best does nothing to help you review it's state on a particular date (the timestamp is the key to that).

So, based on the information you provided I don't think you are a good candidate for using this design. If so, you should consider yourself lucky - it's a pain.

Improve this answer

Your Answer

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

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