FluxAccounting.db: need a table to describe bank/account hierarchy about flux-accounting HOT 6 CLOSED

@cmoussa1:

I think you may want to consider actual SQL queries in mind in designing the schema further. If you want to create a full bank/accounting hierarchy like we discussed with @ryanday36, what would be typical SQL queries you would need?

Would it be mainly a Join operation? Like join the association_table with bank_table? How would you join those two? What would be a foreign key?

If you need to dump the entire bank table info into the internal tree of bank hierarchy in memory, what SQL queries and how many of those will be needed?

from flux-accounting.

The three fields, account_name, parent_acct_name, shares, are the ones I can think of that could give some baseline functionality to creating a bank hierarchy. Can you think of any other fields that might be needed?

Those three fields should be enough to build a bank hierarchy. One other thing that I'm curious about, do you see this as being one database per cluster, or a centralized database for multiple clusters? There's arguments both ways, but if it's the latter will there be an association table for each cluster (_association_table) or a field for cluster name?

from flux-accounting.

That's what I was thinking - a JOIN between association_table and bank_table. To create the hierarchy, we could join based on the account name, since that would be present in both tables.

I think a requirement expressed from @ryanday36 is that a user can belong to multiple banks. How do you plan to support this?

from flux-accounting.

The primary key for association_table is a combination of both username and account. This should allow multiple entries for associations with the same username that can belong to multiple banks.

from flux-accounting.

I think one database per cluster is reasonable, and probably the best way to do things, especially at first. In some senses it's more to maintain than a centralized database, but it sounds like the database is going to be pretty integral to fairshare job scheduling, so having a database per cluster removes a central point of failure that can take out all of the clusters.

from flux-accounting.

Would it be mainly a Join operation? Like join the association_table with bank_table? How would you join those two? What would be a foreign key?

That's what I was thinking - a JOIN between association_table and bank_table. To create the hierarchy, we could join based on the account name, since that would be present in both tables.

One other thing that I'm curious about, do you see this as being one database per cluster, or a centralized database for multiple clusters? There's arguments both ways, but if it's the latter will there be an association table for each cluster (_association_table) or a field for cluster name?

Good question. I was thinking this being one database per cluster - the association_table would list the users registered on the cluster, and the bank_table could list the breakdown of the accounts registered on that same cluster. Does this seem reasonable?

from flux-accounting.