Chopping database records

I have been working on a project recently for a client which required the user to insert a record into an existing set of records and to modify those existing records to accomodate the new record.

The above diagrams shows what needed to be accomplished. If you imagine from left to right is time on the diagram, each record has a start date and and end date and the new record is needing to be inserted between those start dates and end dates.

The start and end dates can overlap the start and end dates of the new record and this is where the existing need to be chopped and modified to accomodate the new record.

So how did I go about this?

Firstly I decided to bucket the existing records into categories as follows …

And like …

This gives us four actual categories and a fifth “Ignore” category. The four actual categories are LeftRight, Middle, Left, Right. Some more examples are shown below …

The bucketing of the existing records into categories from a coding perspective is accomplished as follows …

Firstly I selected the existing records, pseudo code shown below …

dateStart = new record start date
dateEnd = new record end date
dbDateStart = existing record start date
dbDateEnd = existing record end date

Other columns could be added to this selection code if needed, in some cases a “user_id” or other foreign key could be potentially needed.

Using the above selection code will select all the existing records that exist within the range of the new record start and end dates. In other words the records that fall into the “Ignore” category will not be selected.

This is why the “Ignore” category is not a real category as the records are never selected and thus do not need to be bucketed.

Now I have selected my records from the database I put the records into a loop. Whilst in this loop I bucket the records into a set of arrays using the following conditions …

LeftRight …

Middle …

Left …

Right …

Now that the records have been bucketed into the arrays I loop through each group and process the groups seperately as follows …

new record = the new record you are wanting to insert into the DB via displacing the current records
original records = the record in the LeftRight bucket that has not been cloned

LeftRight …

I take a clone of the record and I modify the original (none cloned) records date end to be the new record date start -1 day this is then saved. I then take the clone and set the date start to be the newly inserted records date end +1 day this is then saved.

Middle …

Any middle records are deleted.

Left …

I modify the records date end to be the newly inserted records date start -1 day this is then saved.

Right …

I modify the records date start to be the newly inserted records date end +1 day this is then saved.

After all of the bucketed records have been processed there should now be room to insert the new record so I insert / save the new record.

So that concludes how I created a system that would allow a new record to be inserted whilst chopping the existing records to accomodate the new records.

Retaining great employees

There is talk on the web about retaining great employees and the way some of the articles are written about the subject you would think a lot of companies have a real struggle to get and keep great employees.

Not sure what makes the employees “great” employees other than the fact that if they are the ones leaving the company they obviously have options elsewhere.

So what would I do if I had a company with employees and I wanted to retain them without going over the top and paying them huge salaries obviously …

Allow home working

Allow the employees to work from home as and when they choose to.

Less hours in work day

Expect 5 hours “in the zone” work from each employee each day but pay them for 7.5 / 8 hours.

Allow the employee to revise skills, have breaks / lunch, collaborate with coworkers, work on personal projects, exercise or even depending on your limits allow the employee to occasionally take the rest of the day off in the 2.5 / 3 hours spare.

Alternatively if the employee really wants to, the employee can carry on with his / her workload. It is up to the employee what he / she spends the 3 hours doing each day.

Some afternoons could be spent doing group activities whether work related or fun related.

Also note that the above idea is not an excuse to compress more or the same amount of work into less time. If that happens then the above advantage has obviously been nullified.

More holidays

20 to 21 days holiday entitlement a year? Increase it. I think for every 8 weeks worked an employee should get around 5 days off work.

Lunch around a big table together

Have the whole company eat lunch together every working day around a big table or multiple tables if needed.

Once a month meal

Once a month take the employees out for a meal, paid for by the company.

Every 3 months have an employee activity day

Every 3 months arrange for there to be a fun activity day such as paint balling or some other group fun activity.

Have an open culture

Set the tone of the workplace as a place where people can ask work related questions of their teammates. Work related mistakes should not be jumped on but merely highlighted in a nice way.

These are the points I would implement in my business if I owned a multi employee business. Not all the points would have to be implemented but the more points the better. I would only apply these rules if the staff where regular employees, the rules would not be applied to contractors. Obviously if the workplace is toxic then none of the above points apply and employees will leave anyway but that is a different topic of discussion.