Database design, 1-to-many or many-to-many

database-designrelational-theory

Just a little background information pertaining to the question at hand:
I'm developing an application for users to be able to log the work they have been doing hourly, to be compiled into a weekly log. They should log 8 consecutive hours a day.
Only these users can log their work, as they are in a specific table of their own.

My question is how to handle these weekly logs, as a 1-to-many relation or a many-to-many relation? That is, since there will be many users and thus many logs, create an intermediate relation linking both of these tables, or look at it from a more general point of view and think one user can have many reports, so as to label it a one-to-many relation.

In short, would it be a good idea to see this as follows: there is a relation stating the hour and description of the work done or task performed, another relation which would be the actual report (which has user field and an hour field, both foreign keys so this would be an intermediate relation), and then the user relation with all of its pertinent information.

I guess my doubt is more conceptual than anything, but I'm interested in learning this so I can make an optimal design.

Best Answer

Don't try to over-design your solution. Relational theory, like many other academic subjects can often be taken to extremes, to the point where they are difficult to grasp for a third party that will (eventually) inherit your work.

I think the most common data model you'll find, with regards to time reporting, looks something like this:

CREATE TABLE rep.Users (
    UserID     int PRIMARY KEY,
    ManagerID  int NULL,
    Name       varchar(100),
    FOREIGN KEY (ManagerID) REFERENCES rep.Users (UserID)
);

CREATE TABLE rep.Projects (
    ProjectID  int PRIMARY KEY,
    Name       varchar(100),
);

CREATE TABLE rep.TimeReport (
    UserID     int,
    ProjectID  int,
    StartTime  datetime,
    EndTime    datetime,
    Approved   bit DEFAULT (0),
    PRIMARY KEY (UserID, StartTime), /* possibly also ProjectID */
    FOREIGN KEY (UserID) REFERENCES rep.Users (UserID),
    FOREIGN KEY (ProjectID) REFERENCES rep.Projects (ProjectID)
);

In this design, the notion of a "report" is implicitly what you decide it to be: a day, a week, a month, etc. You can add various elements depending on your needs, for instance:

  • Is there a start or end validity date to each project and/or user?
  • Do you want EndTime or NumberOfHours? You can always calculate one from the other, so you never need both.
  • Do you need approval history or is it enough to know if the report is approved (i.e. closed) or not?
Related Question