I'm starting to code time tracking application to replace the monthly excel sheets my company is requiring. Its purpose is standard : to centralize all data about the time spent by different(or same) person(s) on different(or same) projects on a server. According to the following use case, I'm wondering what is the best way to design and create a database for this application ?.

I've prepared some requirements like this use case which shows the detail that are provided by a worker:

Use case overview

In addition, customers are able to provide comments related to any project or any person.

While I'm experimented with coding (Qt/C++ here) I'm a beginner for DB related topics. This question is close to : this one and this one as well but I'm wondering if it's not possible to improve the way to save all periods related to time ranges as they may be fragmented like in the scenario up in the picture. This would imply to save multiple date for working days : 1->3 + 5->6 etc... So as a side question : does it make sense to save this time range as a binary value, so for each working day in a month a 32 bits would be represented as a '1' bit ?

| improve this question | | | | |

The most 'flexible' way is to store the periods in a separate table together with a type field (Working days, Private off days, etc.). This makes it easier to generate overviews of days present/away because there is no need to check the 32 bits for zero's for each type of day. Also no problem with the different length of the months. There is an other type of days? Just allow it to be entered in the database. No need to change the table. If you need to transfer it into a 32 bit field the I am sure that C++ is capable in doing so.

| improve this answer | | | | |

Your question is a very general one, and I'm not going to try to give you a specific answer.

In general terms, the first thing you need to understand is that your existing data is crosstabulated. It's tabulated by day within october across the top, and it's tabulated by kind of day down the side. Cross tabulated data is very clumsy to work with in a relational database.

It's much better to "unpivot" the data before trying to load it into a database. The unpivoted data will look something like this:

create table Shift_Hours
    (Employee_id number,
     Work_shift_type number,
     Work_date Date
     Shift_hours double);

I invented Employee_id because you talked about multiple individuals but you didn't say whether they were to be kept separate. Also the date field allows dates of different months to ekpt in the same table, and you would select only the ones from a given month at retrieval time. Also, I invented Shift_hours because you didn't say what kind of data you wanted in the cells.

And Employee_id and Work_Shift_Type refer to reference data stored in other tables. These tables would contain data like last_name, or Work_SHift_Name.

If I had to solve your problem, I would explore how to unpivot the data in the spreadsheet tool before unloading it, even though I don't know how to do that. The spreadsheet package is probably the right tool for the job.

Once you get the data loaded into a well designed relational table, together with the accompanying reference tables, recreating the outputs is the easy part. Most database packages have a tool for either crosstabulating or pivoting data before presenting it.

| improve this answer | | | | |
  • Very interesting Walter I'm going to update the question according to your remarks. Anyway, if I got your "hot to solve" question well the way to "unpivot" data is to use the hamming weight algorithm which is avaiable directly from Qt – dlewin Nov 10 '15 at 16:10
  • update in regard to Employee_id & multiple individuals : what do you mean by "kept seperate" ? from my pov a dedicated table for all the employees would be justified . for Shift_hours maybe (yes I'm unsure) the cells (working day, off , training, ) are using binary encoded as Hexa values – dlewin Nov 10 '15 at 16:42
  • I'm not sure how much you know about database design. Does the phrase "table decomposition" mean anything to you? How about "data normalization"? – Walter Mitty Nov 10 '15 at 19:13
  • what I can ensure is that I'm a beginner for DB related things, I have no problem to code all the software around. Hence my preference for asking to specialists. – dlewin Nov 12 '15 at 8:56

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.