Posted - 08/21/2014 : 18:46:48
| Thank you in advance for your consideration.
Imagine a company that makes thousands of products. Those products can be sold across multiple brand relationships. A specific brand can appear multiple times as different "branding categories".
I want to create an association table between the brands:
It is a graph not a tree because a child can have multiple parents...if I understand the Celko book correctly.
A brand can be sold in multiple places in the organization. Think of it
as selling Widgets as your local grocery store as Brand A but you can
also sell them at CostCo or Sams Club as Brand B and Brand C.
I want to store the "organization" of the branding relationship on the invoicing so I can say, "I sold 50 XYZ in Super Brand 1" this month. The problem is that if the BrandId is in multiple association rows, how do you know what "organization" the item was sold in?
Do I store the BrandAssocId on the invoice? That works great except that if the "organization" changes, like moving Normal Brand 2 below Super Brand 3, I lose that history. Or do I store the multiple columns of ParentBrandId, ChildBrandId, and BrandTypeId on the other tables for things like invoices and such.
Again, I appreciate your consideration and time with this.