January 6, 2012 at 1:23 pm
I am working on a project, and I've hit a small-ish bump in the road. I have to link a client to multiple product types. I need this to allow for various numbers of products, and several of the same products are sold by different clients. Here is a better overview of the data:
Create Table dbo.Clients
(Client_ID Int Identity(1,1) Primary Key
,ClientName Varchar(100))
Go
--------------------
Create Table dbo.Products
(Product_ID Int Identity(1,1) Primary Key
,ProductName Varchar(100))
Go
--------------------
Insert Into dbo.Clients
(ClientName)
Select 'Client 1'
Union All
Select 'Client 2'
Go
--------------------
Insert Into dbo.Products
(ProductName)
Select 'Product 1'
Union All
Select 'Product 2'
Union All
Select 'Product 3'
Go
Now, my thought is to use an asoc table...something along the lines of:
Create Table Client_Products_Asoc
(Client_Products_Asoc_ID Int Identity(1,1) Primary Key
,Client_ID Int References dbo.Clients(Client_ID)
,Product_ID Int References dbo.Products(Product_ID))
Go
--------------------
Insert Into dbo.Client_Products_Asoc
(Client_ID
,Product_ID)
Select 1,1
Union All
Select 1,3
Union All
Select 2,1
Union All
Select 2,2
Go
Am I over thinking this? I know I can create a cross-tab query using this logic, but is there an easier way to go about this?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 6, 2012 at 1:33 pm
jarid.lawson (1/6/2012)
Am I over thinking this?
Nope, you are not.
As far as I understand the scenario there is a many-to-many relationship between Clients and Products - the bridge table breaks that n-n relationship which is IMHO the sensible thing to do.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2012 at 1:40 pm
Thanks for the reassurance. I'm designing a new financial tracking system as the company's new DBA...and I'm trying to do this as perfect as I can from the start. It has been a long time since I've done something this big, so I am a little nervous.
I appreciate you taking the time to answer. 😀
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 6, 2012 at 1:43 pm
jarid.lawson (1/6/2012)
Thanks for the reassurance. I'm designing a new financial tracking system as the company's new DBA...and I'm trying to do this as perfect as I can from the start. It has been a long time since I've done something this big, so I am a little nervous.I appreciate you taking the time to answer. 😀
No worries. Just trust your instincts, I've seen your posts - you are good 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 11, 2012 at 1:35 pm
jarid.lawson (1/6/2012)
I am working on a project, and I've hit a small-ish bump in the road. I have to link a client to multiple product types. I need this to allow for various numbers of products, and several of the same products are sold by different clients. Here is a better overview of the data:
I see some potential problems. Is there a reason why you are allowing products with duplicate names and clients with duplicate names? Why permit null product names and client names? How will the users be able to identify the clients and products accurately in those tables?
Create Table Client_Products_Asoc
(Client_Products_Asoc_ID Int Identity(1,1) Primary Key
,Client_ID Int References dbo.Clients(Client_ID)
,Product_ID Int References dbo.Products(Product_ID))
Go
The Client_Products_Asoc table means that one client can be associated with the same product multiple times. That seems unlikely to be useful or correct. Also both client and product IDs are nullable, which means you could get clients without associated products and products without associated clients, in which case what would be the purpose of having them in this table?
Hope this helps.
January 11, 2012 at 3:49 pm
These are great questions. Let me see if I can explain my logic.
Client 1 Offers
Product 1
Product 2
Product 4
Client 2 Offers
Product 2
Product 4
Product 5
I want to be able to run reports that list sales by client by product. To do this I would
Select C.Name As ClientName
,P.Name As ProductName
,Sum(P.Sales) As TotalSales
From dbo.Client C Inner Join dbo.ClientProduct_Asoc CPA
On C.Client_ID = CPA.Client_ID
Inner Join dbo.Product P
On P.Product_ID = CPA.Product_ID
Where CPA.Inactive <> 'Y'
Granted this is a quick version of my logic, but does that explain it a little better? Or is there a better way to do this?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 11, 2012 at 3:52 pm
Sorry, I just saw that I forgot to include a vital part of my logic to why I am doing things this way. I have a transactions table which shows each of the products. Each transaction will include Client_ID and Product_ID on each line, so they can link this way.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 11, 2012 at 6:18 pm
Create Table Client_Products_Asoc(Client_Products_Asoc_ID Int Identity(1,1) Primary Key ,Client_ID Int References dbo.Clients(Client_ID) ,Product_ID Int References dbo.Products(Product_ID))Go
Logically, the ClientID + ProductID should be unique so you can use them together as a composite key. This should help avoid nulls and duplicates in the associating table.
Something like:
--this table identifies the clients
Create Table Clients(
Client_ID Int Identity(1,1) Primary Key ,
ClientName Varchar(100))
Go
Insert Into Clients(ClientName)
Select 'Client 1'
Union All
Select 'Client 2'
Go
--------------------
--this table identifies the products, i'm assuming there will be more columns
Create Table Products(
Product_ID Int Identity(1,1) Primary Key ,
ProductName Varchar(100),
ProductCost Decimal(28,4))
Go
Insert Into Products(ProductName, ProductCost)
Select 'Product 1' , 49.95
Union All
Select 'Product 2' , 15
Union All
Select 'Product 3' , 777.77
Go
--------------------
--this table associates the clients and products and provides a place for custom name
Create Table Client_Products(
Client_ID Int References Clients(Client_ID) not null,
Product_ID Int References Products(Product_ID) not null,
ClientProductName varchar(100) null,
PRIMARY KEY (Client_ID, Product_ID)
)
Go
Insert Into Client_Products (Client_ID , Product_ID , ClientProductName )
select 1,1,null
union all
select 1,2, 'Beer Popsicle'
union all
select 2,2, null
union all
select 2,3, 'Our Thingy'
union all
select 1,3, null
Go
--------------------
--if you want to see which products a client is associated with
select
c.client_ID
,c.ClientName
,p.Product_ID
,coalesce(cp.ClientProductName, p.ProductName)
from
Client_Products cp
inner join Clients c on c.Client_ID = cp.Client_ID
inner join Products p on p.Product_ID = cp.Product_ID
;
--------------------
--you said you have a transaction table, this is a dummy version without your ddl
--just to show how to pull out data
Create Table ClientTransactions(Client_ID Int, Product_ID Int, Quantity Int)
go
Insert Into ClientTransactions
select 1,1,40 union all
select 1,2,200 union all
select 1,3,10 union all
select 1,1,16 union all
select 2,1,1 union all
select 2,2,50 union all
select 2,3,40 union all
select 1,2,6 union all
select 2,3,4
go
select
c.client_ID
,c.ClientName
,p.Product_ID
,coalesce(cp.ClientProductName, p.ProductName)
,TotalQuantity=SUM(t.Quantity)
,TotalPrice=SUM(t.Quantity * p.ProductCost)
from
ClientTransactions t
inner join Clients c on c.Client_ID = t.Client_ID
inner join Products p on p.Product_ID = t.Product_ID
inner join Client_Products cp on cp.Client_ID = t.Client_ID and cp.Product_ID = t.Product_ID
group by
c.client_ID
,c.ClientName
,p.Product_ID
,coalesce(cp.ClientProductName, p.ProductName)
;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply