ERD for a Departmental Licence Management System

Gretings,

SQL Guru needed! This one seems rather tricky to me and could be implemented in several ways I guess.

I am learning SQL, and Dot Net Core 8 with Visual Studio. We have a requirement to record and report on software licences used by our departments. I have drafted this ERD that seeks to (but probably with errors given my level of expertise).
The help I seek is to understand how this would translate into Dimension Tables, Fact Tables, Primary Keys, Foreign Keys,Surrogate Keys – as applicable please.

Summary of business process:

• Departments have a set number of users.
• Each department defines one or more Bundles.
• Each Bundle comprises of one or more Licences.
• All Licences have two cost models. One is for a set number of users, e.g. £50k for 10 users. One is for Perpetual (lifetime) e.g. £100k.
• Each Licence is associated with a Vendor. Vendors can thus provide many Licences. Vendors provide the costs for set users, AND, a perpetual cost FOR ALL licences they provide.
• Each Department allocates one or more licences to it, and defines how many of its users will use each of the respective licences – in specific months of specific years. If a particular licence is not used in a given month and//or year then no record is created, the assumption is “no use” for that Year+Month combinations. Days are never used.
• We want to run reports of various combinations of insight, based on Year, Quarters, Months – from 2010 to 2040. These reports can provide insight based on department’s allocation of users to licences over the months and years, such as:

1. Total invested by Year, Quarter and Month
2. Total invested by Vendor
3. Departmental Costs
4. Bundle Costs
5. Licensing Cost
6. User based costs
7. Perpetual based costs
8. combinations of the above.

Key attributes

DEPARTMENTS

• Name
• Total cost for All Bundles
• Total cost for User Based licensing
• Total cost for all Perpetual licensing.

BUNDLE

• Name
• Total Costs
  — Total for all licences in the Bundle based on User Based licensing
  — Total for all licences in the Bundle based on Perpetual licensing

LICENCE [in this example, this licence costs £5k per 10 users]

• Name
• Cost per user count (e.g. £5k)
• User count (10)
• Perpetual Cose

VENDOR

• Name
• Total we have invested in this vendor
  — for all User Based licensing
  — for all Perpetual licensing
• An email contact

ALLOCATION

• Licence
• Year
• Month
• User Ratio (% of users likely to use the licence)

DATES DIMENSION [we never use days, just Year, Quarter and Month]

• Year
• Month number
• Month name
• MMYYY
• Quarter number
  I have managed to create this table using T-SQL and all is fine with this, just don’t know how it should be used.

Next steps

1. Define the tables and type (Dimension, Fact) <— THIS IS WHERE I NEED HELP TO THIS POST
2. Define the Primary, Foreign and Surrogate keys
3. Determine how to calculate derived attributes
4. Add additional useful attributes
5. Populate and test

Thank you
and please correct any misunderstanding and poor questioning 🙂

I dont know where to start