This one is a little technical, but here it goes.
I am building an IT financial management database for the IT department of a hospital. In that database, I want to allocate costs to various allocation categories based on the amount of IT service they consume. For most of the allocations, it's a fairly simple affair (Allocation relationships.pdf). There's an OrderProduct table (from the Procurement cycle) for which the user inputs the products associated with an order/purchase/contract, and the Allocation table is a many to many table combining the OrderProduct with an AllocationType and a percentage to allocate of that OrderProduct to the chose AllocationType. The AllocationType table is a hierarchical table, so you can nest categories of allocations (Service Catalog, IT Functional Spend, IT Asset Spend, etc.).
Here's the challenge: the hospital has departments and service lines. A department is self-explanatory; I have a hierarchical Organization table that has all the departments in it. A service line is a service the hospital offers (say, Medical/Surgical Inpatient Services) that may or may not be comprised of contributions by multiple departments. For instance, Medical/Surgical Inpatient Services would include both the medical/surgical floor staff, as well as dietary, environmental and cardio/respiratory services. To build the service lines is easy enough; just replicate the allocation methodology to create percentages of departments contributing to a service line.
However, the department/service line tables are necessarily separate and distinct from the AllocationType table. How can I include the departments and service lines with the allocation types in one list for the user on the front end? I could use a query, I guess, but I often have problems with the query being updateable. What are your thoughts?
Thanks,
Phil