0
\$\begingroup\$

I have to store some properties in a database properties table which looks like this:

CREATE TABLE `property` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `computer_name` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  `value` varchar(1024) NOT NULL,
  PRIMARY KEY (`id`)
);

Sample data:

+----+---------------+-----------------+-------+
| id | computer_name | name            | value |
+----+---------------+-----------------+-------+
|  9 | PC002         | firewall_status | 3     |
| 10 | PC011         | firewall_status | 0     |
| 11 | PC011         | some_property   | 1     |

To access data in that table I have a getProperty($computerName, $propertyName) method in a repository, which finds the value of specified property for certain computer. And in different parts of my application I call the method like this:

$computer = $repository->findComputer();
$firewallStatus = $repository->getProperty($computer->name, 'firewall_status');

What I don't like here is hard coded name of the property. My solution was to put available property names to a model:

class ComputerModel
{
    const PROPERTY_FIREWALL_STATUS = 'firewall_status';
    const PROPERTY_SOME_PROPERTY   = 'some_property';
}

...

$computer = $repository->findComputer();
$firewallStatus = $repository->getProperty($computer->name, ComputerModel::PROPERTY_FIREWALL_STATUS);

We discussed storing property names as constants and decided that we have some trade-offs:

Good (mostly for code maintanance):

  • keep the value in one place which make it more simple to change it;
  • keep available property dictionary in one place (in the model);
  • make the code completion available in IDE instead of searching for correct property name to copy/paste it;

Not good (mostly for debugging):

  • by storing a literal values we create extra overhead by storing variable names in new variables;
  • worsen debugging by add one more step to find the object containing debug information;
  • we need to support additional dictionary; possible data inconsistency if someone adds new value to database but forget to update it in the model.

Subjective:

  • improves/reduces code readability.

The question is: what is the best way to store such parameter names? Or maybe to use literals.

\$\endgroup\$
2
  • \$\begingroup\$ Too lazy to write a proper response, but I have a feeling that this may be of use: scribd.com/doc/2670985/SQL-Antipatterns In particular the section that starts on page 30. In short, unless your set of attributes truly needs to be dynamic, they should usually be columns instead of rows. \$\endgroup\$ – Corbin Jul 11 '12 at 1:18
  • \$\begingroup\$ @Corbin thank you for the link, it's really helpful. As to the columns - that solution depends on requirements, but in general I agree with you. \$\endgroup\$ – Minras Jul 11 '12 at 8:13
2
\$\begingroup\$

keep the value in one place which make it more simple to change it

You will never change it (why would you?) so this is not a valid reason. And if you did change it then search/replace works fine.

keep available property dictionary in one place (in the model)

This looks like MySQL to me. So I suggest using an enum for this instead of a varchar. It will be much faster, take less storage space, prevent invalid values, and have the benefit of storing the dictionary right in the database.

make the code completion available in IDE instead of searching for correct property name to copy/paste it

This is nice - however is there no other way to do this? Can you not add some kind of configuration file that will define these?

In the "not good" column I would put adds complexity for little gain. You've taken a string, and turned it back into a different string, only uppercase.

Now, if you were going to store these as integers (i.e. do the equivalent of enum yourself) then making constants has value. You are turning a string into a number, so it actually does something.

So, my suggestion:

Either use enums in your database, and just write them as strings, or store integers, and use the constants.

But don't store these properties as strings.

(BTW, suppose you want to convert from one to the other: Search/replace! I've seen too many cases of overengineering things just from fear of search/replace.)

\$\endgroup\$
10
  • \$\begingroup\$ Thank you! Enums is a really good idea, I didn't think about it. As to search/replace, it was one of my arguments - it is easier to find occurrences of a constant than of a certain word (especially when it is not 'firewall_status' but something like 'error'). And overengineering - yes - that's the argument of my opponent :) \$\endgroup\$ – Minras Jul 10 '12 at 23:32
  • \$\begingroup\$ Enums are almost never the correct solution in a RDBMS. They're the same functionality (and insignificantly close performance) as a lookup table with none of the maintainability. \$\endgroup\$ – Corbin Jul 11 '12 at 1:14
  • \$\begingroup\$ @Corbin My rule is if it's hardcoded in the app, it's hardcoded in the database. I hate lookup tables where it's hardcoded in the app, but is editable data in a database table. They also require pre-loading when releasing, and I've found they get out of sync sometimes when adding more entries in new releases. And they allow invalid data. With an enum the database maintains the lookup association over an alter table. If you mean a lookup table in the app, then I don't like those because when working directly in the database you have meaningless numbers, which makes it harder to work with. \$\endgroup\$ – Ariel Jul 11 '12 at 1:45
  • \$\begingroup\$ @Ariel A lookup table that resides in a database should never be hard coded in an application. If the entire table is needed for some reason, then it should just be retrieved. The problem with enums is that they're not data. They can't be manipulated in an easy way from application-space. They also cannot have their options enumerated very easily (counter intuitively). How would your application know the possible values of an enum without either hard coding an array or doing some nasty meta-data fetching? \$\endgroup\$ – Corbin Jul 11 '12 at 1:49
  • 1
    \$\begingroup\$ @Corbin When I say hardcoded I mean the names of the lookup keys. It's not really that hard to enumerate an enum, even though it is meta-data fetching. I made a function specifically for that, rare thought it is that I use it. It's pretty unusual to need to enumerate the enum - it's mainly used when you have a fixed set of values that you use. They could just as easily be integers, but by using an enum it's easier to read the database output. \$\endgroup\$ – Ariel Jul 11 '12 at 2:00

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.