Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I need to design a database table that holds the data of programmers work hours.

The problem is that I have a compound type - two pieces of data belong to a category.

Specifically, the work hours are classified into categories: design, program, test, troubleshoot, etc... and each category is divided into two types: hours spent at work and hours spent at home.

How can I design a compound type in the database side?

enter image description here

I thought about storing the two types in one field like "6,2" but that creates more problems to resolve. I would now have to convert and split data along with worrying about localization issues, missing data, or if another location was added to the categories.


Clarification:
Asking my question a different way, it's relatively easy to design a table without compound types like this:

enter image description here

But how to deal with it when each category is split into two types?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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
  • Employees
  • Activities
  • Locations

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 activities:

1  'Design'
2  'Program'
3  'Test'
3  'Troubleshoot'

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 (
    id          NUMERIC,
    employee    NUMERIC,  -- Foreign key into employees(id)
    activity    NUMERIC,  -- Foreign key into activities(id)
    location    NUMERIC,  -- Foreign key into locations(id)
    time_spent  NUMERIC
)

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
FROM
  hours_worked
  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
SELECT SUM(time_spent)
FROM
  hours_worked JOIN location ON hours_worked.location = location.id
WHERE
  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.

share|improve this answer
Table 
-> Relationship

Employee -> WorkHours (Hours)
WorkHours -> WorkCategory (Work, Home)
WorkHours -> HoursCategory (Design, Test, Program, Troubleshoot)

An employee can have one or more workhours, a workhour has an hours value and relationships to lookup tables for category of work and category of kinds of hours. This should capture how many hours of what kind of work an employee did and where they did that work.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.