Is my design logic correct?
Dynamic Measurement Group is a small agency that provides research and training for educators who use our products. Our products consist of training workshops delivered in-person (onsite workshops), online (pre-recorded workshops), live online (webinars), at regional locations (training events) and Institutes (one annually – delivered the same as training events but product authors provide the training); training books that can only be purchased by those who attend specific workshops and have acquired Mentor status; merchandise (stopwatches, clip boards, t-shirts, etc.) that can be purchased by anyone; and a data service (DIBELSnet) for entering and storing results of assessments conducted using our products.
I have been tasked with creating a database to answer the question: Who have we touched?
The possibilities include anything from a school district that purchases a number of workshops or onsite training events to an individual who signs up for our newsletter. We use CVent to capture registration details for training events we host but not for events contracted for by a specific school or district. We use QuickBooks as our accounting system and therefore have customer records in both of these programs, some of which overlap. We also have a database that contains the names and email addresses (at the least) of those educators who have been trained to be Mentors.
I have the following entities or categories defined:
- Type (workshop or merchandise or data service user)
Based on these categories I think they are related as follows:
- Customers have a many to many relationship with type (one customer could purchase training, merchandise and/or be a DIBELSnet customer) or one type (merchandise) could be purchased by many customers
- Type has a many to many relationship with customers
- Customers have a one to many relationship with Products
- Products have a many to many relationship with Invoices
- Invoices have a many to many relationship with Products
My questions are:
Is my logic as to the relationships between the categories of our database design correct?
Assuming they are correct, we would need to define two join tables: one to join the data in the Customers Table with the data in the Type Table and the second to join the Products and Invoices tables, correct?
What am I missing? What have I not considered?