Database tables are, by nature, two-dimensional creatures. The simplified version in your clarification, which is hours by name and activity, works just fine because there are only to dimensions to the data (name and activity). Introducing location adds a third dimension, and the fact that you had to start thinking about ways to store the data in something other than a native type means you've outgrown the model.
Fortunately, there's normalization, which is the process of making sure you avoid duplication in your tables by thinking about your data in terms of what all the individual pieces are and how they relate to each other. (That last bit is why SQL databases are called relational.)
Looking at the table in your question, I see four things:
- Hours worked
One thing you may notice is that you have a lot of hours worked, and each of those figures can be tied to an an employee, and activity and a location. Those ties are the relations in your data. Each relation is a dimension and an opportunity to split things that might be duplicated into a separate table:
CREATE TABLE employees (id NUMERIC, name VARCHAR(100))
CREATE TABLE activities (id NUMERIC, label VARCHAR(100))
CREATE TABLE locations (id NUMERIC, place VARCHAR(100))
Each table gets populated with the possible values for those attributes, such as these one for
Your table of hours worked can then have as many dimensions as you need by referring to the others using foreign keys:
CREATE TABLE hours_worked (
employee NUMERIC, -- Foreign key into employees(id)
activity NUMERIC, -- Foreign key into activities(id)
location NUMERIC, -- Foreign key into locations(id)
Once you've done this, you can use JOINs to do a wide variety of queries, some of which would have required writing code to query and assembly had everything been left denormalized:
-- Your table, which uses all three dimensions
SELECT employee.name, activity.label, locations.place, time_spent
JOIN employees ON hours_worked.employee = employees.id
JOIN activity ON hours_worked.activity = activities.id
JOIN location on hours_worked.location = locations.id
ORDER BY xxx -- Whatever's appropriate here
-- Number of hours worked at home, which uses only one dimension
hours_worked JOIN location ON hours_worked.location = location.id
location.place = 'Home'
Normalization also imparts considerable flexibility.
If your bosses decide that they also want to keep track of hours by project, your three-dimensional relationship becomes four-dimensional, and if you thought stuffing 3-D data into a 2-D model was going to be hairy, 4-D is worse. Fortunately, a normalized database just requires the addition of another table...
CREATE TABLE projects (id NUMERIC, name VARCHAR(100))
...and another column to hours_worked:
project NUMERIC, -- Foreign key into projects(id)
POOF! With a very small amount of work, you've just added an additional dimension.
Are people working at coffee shops in addition to the office and at home? Insert a row into
locations and you're good to go.
Have you decided that "programming" should have been called "development?" Change that single row in
activities and any queries you do afterward will have the right label attached.