Table/data set-up design for desired reporting question
I am new to building applications but am building what seems to be a challenging application to start.
I have providers that have different potential lines of businesses (we'll call them accounts). Each account has multiple performance measures that are associated with one of several types of contracts. This sounds like a many to many to many relationship?
Many providers may have multiple accounts with multiple contracts with multiple performance measures.
I want to be able to see performance over time for each provider's account and associated metrics.
I don't know how to set this up.
Current thoughts on table/data structure:
State list table-- a list of available states (markets we operate in)
Account list table--a list of available accounts (standard across states)
Joint state-account table (many to many)--allows State and associated accounts to populate in providers table
Providers table (1 state, may have several contracts with several accounts that have several performance measures)
Providers--accounts join table (so that a particular provider can be associated with their applicable accounts)
Performance measures table (standard list of measures possible to be associated with an account and contract)
Contracts table (types of contracts)
Providers-Accounts join table + Accounts-Perf. Metrics join table + Contracts table??
EX) Provider A has three account lines, each with performance metrics associated with those lines. I'd like to be able to have the information from some relationship from the above (altered, however necessary) to be able to look pull the provider name; associated account lines (many) and each of their many metrics (this sounds like a many to many to many?) and then to be able to enter values to assess their performance over time (year and quarter) and to be able to filter just by the provider, account in question, and specific account performance measures.
I know that QB isn't like Excel, but the picture below will hopefully give you an idea of what I'm after. Each provider will have some or all account lines and some or all performance measures.
I have providers that have different potential lines of businesses (we'll call them accounts). Each account has multiple performance measures that are associated with one of several types of contracts. This sounds like a many to many to many relationship?
Many providers may have multiple accounts with multiple contracts with multiple performance measures.
I want to be able to see performance over time for each provider's account and associated metrics.
I don't know how to set this up.
Current thoughts on table/data structure:
State list table-- a list of available states (markets we operate in)
Account list table--a list of available accounts (standard across states)
Joint state-account table (many to many)--allows State and associated accounts to populate in providers table
Providers table (1 state, may have several contracts with several accounts that have several performance measures)
Providers--accounts join table (so that a particular provider can be associated with their applicable accounts)
Performance measures table (standard list of measures possible to be associated with an account and contract)
Contracts table (types of contracts)
Providers-Accounts join table + Accounts-Perf. Metrics join table + Contracts table??
EX) Provider A has three account lines, each with performance metrics associated with those lines. I'd like to be able to have the information from some relationship from the above (altered, however necessary) to be able to look pull the provider name; associated account lines (many) and each of their many metrics (this sounds like a many to many to many?) and then to be able to enter values to assess their performance over time (year and quarter) and to be able to filter just by the provider, account in question, and specific account performance measures.
I know that QB isn't like Excel, but the picture below will hopefully give you an idea of what I'm after. Each provider will have some or all account lines and some or all performance measures.