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?
3
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.
6
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!