The concept of a project (charge_number, resource) is not modeled explicitly, It has to be inferred from the collection of allocations for a charge_number(account_id) and resource (resource_id).
The concept of a user account (charge_number, resource, person) is not modeled explicitly. It has to be inferred from the allocation_breakdown records associated with allocations.
Also, the concept of multiple allocations for a "project" has presented problems. Under POPS and now under XRAS this has continually created difficulties deciding if an allocation needs to be created vs finding one to be modified. It would have been a lot cleaner to just have a single allocation that gets renewed, supplemented, extended, etc.
project state is for a charge_number, resource; user account state is for a chaarge_number, resource, person. They don't have FKs into any table since projects and user accounts are not explicitly modeled. Also project state is based on the most recent allocation (ie, the allocation with the latest start date) even if that allocation starts in the future. This has generally worked well, but the latest allocation will "hide" the current allocation (ie, the allocation that has todays date between its start and end dates).
Since POPS did not send an identifier (like the XRAS action_id or request_id) in the AMIE NPC, allocations had to be created/located based on the start date sent by POPS in the NPC. That meant that POPS had to figure out what date to put into the NPC since POPS (like XRAS) did not have a start date for its requests.
The PI role is handled differently than the other roles. The PI is attached (via the principal_investigators table) to the acct.requests table (and there are multiple requests over time because allocations have to have a request that has the same start date). The other roles are not "date based", but they are associated with resources. I think the original intention may have been to allow different roles for different resources, but now the roles are associated with the portal.teragrid resource only.
The AMIE RPCs need to send the PI in the RPC so they have to decide which request to use to find the PI. This meant that if an RPC was sent for an earlier request, the PI on that request might not be the current PI. AMIE was not modified to get the PI from the most recent request, but the PI for the request that was associated with the allocation. When the XDCDB gets a PI change (via POPS NPC or XRAS accounting service) it change the PI on all requests. We lose the history of the PI, but tracking PI history via requests is not a good way to do it since the PI can change for the current request as well.
The people table contains both real people and non-person (eg Community Users). There is no explicit indication of if the entry is a real person or not. This has created problems with DN generation since non-people do not get DNs. The DN generation process looks for the last name 'Community User' to determine if the "person" should get a DN or not.
I think SDSC has oher "non-person" entries in the acct.people table which should not get DNs, but did.
The jobs table is huge and only continues to grow. The impact is not only storage, but query performance.
It is not presently possible to eliminate "old" jobs (for example to only have jobs from the last 3 years), since deletes adjust the allocation the job was charged against.
One solution may be materialized views (as a kind of job data warehouse) for specific sets of queries.
Another may be a archived jobs table, where "old" jobs are moved (inserts adjust the allocation so that no usage is lost).