Advice on system parameters/business logic

Tom Dunne

I've been asked for advice on a web-based system and I reckon I am completely out of my depth.

It's a standard web-based system, three-tier architecture based on LAMP.

The question that was put to me, that I was not sure about, is coding system parameters. These parameters form the basis of the business logic within the app, so potentially, they could change in the future. For example, in one calendar year, there should be X meetings between an employee and his/her manager. Right now, that is (say) three, however in the future, it could change. Another parameter might be that an employee can only have Y days holidays in a year, which, in the future may change.

So the question is, rather than hard-coding global variables, is it best practice to have a "parameters" table in the database. I am thinking the table "Parameter" would have at least two fields - Parameter_name and parameter_value. So any business logic could query this table for the parameter value and then work the logic on it.

Basically, I am looking for a way of changing system parameters without having to change code.

CreepingDeath

Not sure about "best practise"... but here's a few tips.

1. If you're not sure of the maximum length of a parameter value, then just use a CLOB. We've hit 4000 char limitations in the past on various items.

2. If you want to present some admin user with a screen to edit them, them you might want a boolean "HIDDEN" flag for items where you don't want a user editing the system parameter but like the convenience of storing non-editable system parameters in there.

So it's in the table, but not presented for editing.

3. Maybe some import/export functionality to backup/restore those parameters.

4. Auditing. You might want to record an audit log of someone changing a system parameter... could help in debugging issues later.

regards,
CD

oscarBravo

Yeah, I'd have called that table "settings". I'd be tempted to load it into an array or object (depending how you structure the app, haven't done much PHP in a while) in a standard function included at the start of every script, and refer to $SETTINGS or whatever as and when you need to.

Another common approach is to have a configuration file that simply initialises the array: [php]$SETTINGS = array(
'holidays_per_year' => 20,
'meetings_per_year' => 10,
...
);[/php] That way, although technically you have to "change code" to edit them, it's a trivial change in a well-known place. The advantage to this approach is that you can have arbitrary data types, whereas in a settings table you pretty much have to pick one and go with it.

CreepingDeath

Also... you might consider when you read from that table.

If you only read the system parameters when the server starts up, then any change to a system parameter will require restarting the server.

So maybe a scheduled job to read from the table every X minutes would avoid the need to bring down the server for a simple config change.

amen

You should have a parameters table in the database and place the parameters there. Each parameter should have a unique id (numer of meetings per year, number of days holidays etc) that you may want to save with the Meeeting/Employee data.

Storing the data in the database also makes it easy to create a report such as how employees have x meetings a year or Y holidays.

You should also add an effective date from and effective to column on the parameters. This way you could determine that employee A had y days holidays from 01/01/2011 to 31/12/2102 and z days holidays from 01/01/2013 to 12/31/2013.

You might also want to consider having a different table per parameter set.

So maybe a scheduled job to read from the table every X minutes would avoid the need to bring down the server for a simple config change

Personally I've seen this before and its a pain. If you change parameters without a shut down/reset you have some sessions using old values and other sessions using new values. This makes it hard to locate/find the actual change time when later searching through data as you have both old/new session data saved around the same time.

I've yet to have a real valid business reason as to why data needs to change in the middle of the day. There may be a bug which requires a data fix but again this is best handled by stopping and performing a clean start.

4. Auditing

Should always be done. The person who changed the parameter and its prior value should be recorded. Maybe a column recording the reason for the change which should link back to an external tracking system.

If you're not sure of the maximum length of a parameter value, then just use a CLOB. We've hit 4000 char limitations in the past on various items

A 4000 char parameters ? You should really define all parameters based on appropriate data types. So for instance the max number of days an employee can have off in a year is 366 (leap year) so you would to pick an appropriate data type.

stevenmu

My codey senses are tingling , this seems like the type of project where extra requirements could be drip-fed down the line, so I would suggest taking a good flexible approach.

I think one likely addition will be extra event types, i.e. Meetings & Leave & X. The other obvious addition will be that different employees may have different amounts of meetings/leave/etc. For e.g. some employees may have more leave days than others, part time employees may have less meetings and so on.

So to allow for this, I would suggest a few data base tables

- EventTypes: ID and Name fields

- Employees: ID, Name and whatever other fields (depending on your requirements and other systems available you may not actually have an Employees table and just pull these from some other system as needed)

-EmployeeEventAllowance: EmployeeID, EventID and Amount fields. This would have a row for each employee for each event type, and the amount allowed/required. For e.g.
EmployeeID|EventID|Amount
1|1|3
1|2|20
2|1|3
2|2|20
3|1|1
3|2|10


-Event: EmployeeID, EventID and Date fields. This would hold an entry for each meeting or leave day that happens. Simple queries would then let you pull out how many meetings or leave days each employee has used and compare them to the allowance.
e.g.
EmployeeID|EventID|Amount
1|2|01/10/2013
1|2|02/10/2013
1|1|03/10/2013


If you required different allowances per year, and to compare historical data, you could also add a date/year field to the EmployeeEventAllowance table.

It's a little more work than a config file, and you'll have to create CRUD pages of some type for each of the tables, but it should be a lot more flexible if you think they'll start looking for more functionality down the line.