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: BrandBrandId INTDescription VARCHARExample Data:1:BrandA2:BrandB3:BrandC4:BrandDTable: BrandTypeBrandTypeId INTBrandTypeDescription VARCHAR1:Super Brand2:Normal Brand3:Under-BrandI want to create an association table between the brands:Table: BrandAssocBrandAssocId INTParentBrandId INTChildBrandId INTBrandTypeId INT1:NULL:1:12:1:2:23:1:3:3etc.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 itas 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