Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLJames
Starting Member
35 Posts |
 Posted - 2014-08-21 : 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".
Table: Brand
BrandId INT
Description VARCHAR
Example Data:
1:BrandA
2:BrandB
3:BrandC
4:BrandD
Table: BrandType
BrandTypeId INT
BrandTypeDescription VARCHAR
1:Super Brand
2:Normal Brand
3:Under-Brand
I want to create an association table between the brands:
Table: BrandAssoc
BrandAssocId INT
ParentBrandId INT
ChildBrandId INT
BrandTypeId INT
1:NULL:1:1
2:1:2:2
3:1:3:3
etc.
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.
Thanks!
|
|
|
|
|
|
Design Question related to Parent/Child