SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Design Question related to Parent/Child
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLJames
Starting Member

30 Posts

Posted - 08/21/2014 :  18:46:48  Show Profile  Reply with Quote
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!



  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000