I'm working on a PHP project where some object (class member) may contain different data type. For example :

class Property {
    private $_id;     // (PK)
    private $_ref_id; // the object reference id (FK)
    private $_name;   // the name of the property
    private $_type;   // 'string', 'int', 'float(n,m)', 'datetime', etc.
    private $_data;   // ...

    // ..snip.. public getters/setters

Now, I need to perform some persistence on these objects. Some properties may be a text data type, but nothing bigger than what a varchar may hold. Also, later on, I need to be able to perform searches and sorting.

Is it a good practice to use a single database table for this (ie. is there a non negligible performance impact)? If it's "acceptable", then what could be the data type for the data column?

  • It really kind of depends on what you are trying to do. If you just need simple object persistence, this might work out fine. If you need to perform any sort of actual query on the data records, you might want to take a different approach, like using an ORM – Robert Harvey Oct 11 '12 at 23:54
  • @RobertHarvey, there will be some queries performed, to filter objects with some given properties, etc. For example, filter any string properties which contains some text, or filter any int properties between a given range, etc. Obviously, comparing ranges would need to use a natural comparison or casting the column to a numeric value... – Yanick Rochon Oct 12 '12 at 0:08
  • 3
    Sounds like an EAV table. – Robert Harvey Aug 24 '13 at 1:24

Is it a good practice to use a single database table for this

Whether you need 1 table or more is a factor of the dependency of the columns belonging to the table to the PK, as per normalization rule. The sample you show suggests, though not very clear, that this is the case.

You may decompose a single logical table into two physical tables if you have very large number of columns or if your data can be logically partitioned to gain performance, however, this is no longer necessary with new database systems since you can use database features such as Partitions to take care of this.

To gain performance, you need to define indexes and decide which columns should participate in each index as well as the sequence of columns in each. This is the case if you have large data with frequent access. Indexes are slower for large columns than for other types of columns such as int. Also, an index may be ignored if you perform certain LIKE queries.

what could be the data type for the data column?

It is not clear what the data column in your example will hold. However, either a VARCHAR or a TEXT data type may do. For a comparison in performance between the two types, you may want to check Text vs Varchar and dba.stackexchange-Text vs Varchar.

|improve this answer|||||
  • This may sound silly, but even after several college and university DBA classes (part of the CS courses) I'm not sure what you mean by "features such as Partitions". Can you give an example? – Yanick Rochon Oct 12 '12 at 0:33
  • 1
    @YanickRochon: Partitions are a way to divide the data as it is physically stored (both on disk and in memory). This allows you to say "I want all of the rows with $_type == 'int' near each other". This can provide performance improvements since your most commonly used data is closer together, reducing page misses and improving cache performance. – Guvante Oct 12 '12 at 1:11
  • @Guvante, and how do you do that (setup) with MySQL? – Yanick Rochon Oct 12 '12 at 1:16
  • @YanickRochon: I haven't the slightest clue. I will say however that it is not at all a common thing to do. Indexes are usually all you need, partitioning is one of the last tools to use. – Guvante Oct 12 '12 at 1:22
  • 1
    @Guvante, thanks for your comments. As per Yanick Rochon qustion regarding about implementing partitions, please see this: dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html, don't worry about piking my reply as an answer, it happens to me all the time! You are very polite to mention it anyway :) – NoChance Oct 12 '12 at 1:53

No, it's actually the worst possible practice. I am exaggerating.

You should use separate columns for each element in $data in order to maximize the power of the database. Writing search queries, sort queries, etc, against a data column would be a nightmare of complexity, whereas those kinds of queries are easy on a 'normalized' database table.

If your $data property could potentially contain all sorts of key=>value pairs that you cannot predict, then you should look into an EAV table structure for your database table. If the keys (the properties) in your $data variable are fairly constant, then you can use a regular flat database table.

Either way, I strongly advise you not to put $data into a single data column in a database. That would almost completely destroy every advantage of using a database in the first place. You might as well store your data in a text file.

As far as performance... again, you have negated all that is useful about a database, so your performance will be horrendous. And you'll spend hours writing convoluted queries that would take a minute to write if you had a normalized database. I hope you did not proceed with your data column. The whole point of a data base is to make finding and manipulating data easier!

|improve this answer|||||

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.