DynamoDB: How to model data that’s shared and queried by all users

If you set up an index over the binary-tree related fields, leaving the fields in the table should have more or less the same performance as if you had them into their own table with a full covering index (as PostgreSQL supports index-only scans as of v9.2). It probably isn't a bad idea to set up some tables with filler data and do some test cases, though.

In regards to 2), there is a slightly different way you can represent this kind of data, and it really depends on the way you expect to be querying it. This might not be useful, but might give you some food for thought:

For my organization I had to come up with a way to represent organization structure in such a way that it facilitated very fast queries of the kind "give me every person who reports up to X but has direct reports", or "give me the list of persons who are within Z reporting levels to this person". The solution is a slightly modified adjacency table of the form:

 h_ID, emp_ID, m_ID, lvlsAbv  

where h_ID is an autogenerated key, emp_ID is the employeeID, m_ID is the managerID, and lvlsAbove is the # of reporting lvls difference between the 2 people. This means that each employee has multiple rows (1 for each manager above them).

Example:

h_ID    emp_ID    m_ID   lvlsAbv 
42530   211432  254192  1
42531   211432  197829  2
42532   211432  256373  3
42533   211432  255628  4
42534   211432  256978  5
42535   211432  3735    6

The result is a slightly larger table, but is still small enough (size wise) to easily justify a covering index over the whole thing.

The advantage of this kind of structure is the ability to write very simple queries against relational properties of the tree (ex: "select everybody that is downtree of person X"). The downside is that it requires more work to construct and maintain (a lot more).

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?