Table Design help

Hello there,

I am using SSIS and SSRS to generate reports from my production database. Now I am trying to impove the ETL process and need some help on the design. I have some products sold in different states in a country by the branch offices in that state. Some branch offices sell to more then one state. Now I am trying to group them by Branch then its State and the data which I show is the available qty in that branch and the sales in each states it does.

Now I have 3 tables called
FDOSale - This contains the product sales in each State,
ProductAvailable - This contains the available stock In each Branch and
FDOProvince - This contains the states code

The product code in each of these 2 tables FDOSale  and ProductAvailable table is the primaryKey that I link them together.

How can I create a relation so that I can achieve this grouping - Branch and its State with the related data. Do I need to create a new column in the FDOProvince with the BranchNames or ??. At present I am not able to group that.

At present I am trying something like this

SELECT
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOSales.ProvinceEng,
    SUM(dbo.FDOSales.SalesQtyinclDis)      AS SalesQtyInclDis,
    SUM(dbo.FDOSales.SalesDiscount)        AS SalesDiscountQty,
    SUM(dbo.ProductAvailable.AvailableQty) AS AvQty
FROM
    dbo.SupplierProductDetail
LEFT OUTER JOIN
    dbo.ProductAvailable
ON
    (
        dbo.SupplierProductDetail.ProductCode = dbo.ProductAvailable.ProductCode)
LEFT OUTER JOIN
    dbo.FDOSales
ON
    (
        dbo.SupplierProductDetail.ProductCode = dbo.FDOSales.ProductCode)
WHERE
    dbo.FDOSales.SaleDate >= '20151001'
AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOSales.ProvinceEng
ORDER BY
    dbo.SupplierProductDetail.companyName ASC,
    dbo.ProductAvailable.BranchNameEng ASC,
    dbo.SupplierProductDetail.description ASC ;

Open in new window


ee.gif
ee1.gif
cheers
Zolf
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It seems to me that there should be a branch/store reference in FDOSales.  Is it there and I'm just not seeing it?
Miguel OzSoftware EngineerCommented:
You are missing a left join to FDOProvince with this condition (dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode) as shown below:
SELECT
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOProvince.ProvinceEng,
    SUM(dbo.FDOSales.SalesQtyinclDis)      AS SalesQtyInclDis,
    SUM(dbo.FDOSales.SalesDiscount)        AS SalesDiscountQty,
    SUM(dbo.ProductAvailable.AvailableQty) AS AvQty
FROM
    dbo.SupplierProductDetail
LEFT OUTER JOIN
    dbo.ProductAvailable
ON
    (dbo.SupplierProductDetail.ProductCode = dbo.ProductAvailable.ProductCode)
LEFT OUTER JOIN
    dbo.FDOSales
ON
    (dbo.ProductAvailable.ProductCode = dbo.FDOSales.ProductCode)
LEFT OUTER JOIN
    dbo.FDOProvince
ON
    (dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode)
WHERE
    dbo.FDOSales.SaleDate >= '20151001'
AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOProvince.ProvinceEng
ORDER BY
    dbo.SupplierProductDetail.companyName ASC,
    dbo.ProductAvailable.BranchNameEng ASC,
    dbo.SupplierProductDetail.description ASC ;

Open in new window

zolfAuthor Commented:
Thanks for all the feedbacks.

It seems to me that there should be a branch/store reference in FDOSales.  Is it there and I'm just not seeing it?

No I dont have a reference in that table but I have that reference in the ProductAvailable table called BranchCode.

Do I add a new column in the FDOProvince called BranchAssociated and associate those States to the Branch. Is this a good design??

Miguel Oz
Your query did not give the correct result. I am more after the table design
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

zolfAuthor Commented:
I added some additional col the the ProvinceCode table to add association between the (Province)State and its Branch Offices. But when I run the query It is getting the branch of the FDOSales not the ProductAvailable.

1
Brian CroweDatabase AdministratorCommented:
Assuming i am interpreting it correctly, FDOSales should represent a transaction in my opinion just like it would if it were a purchase at a grocery store for example.  In that case it would contain a foreign key relationships with a Branch table, a Product table, and a Province table.  ProductAvailable would become BranchInventory and contain foreign key relationships with Branch and Product.  Don't base your design around satisfying a given query, if the logical design correctly describes your entities and their relationships then the query will resolve itself.  If it is pertinent to know which branches are associated with which provinces then I would suggest putting an intermediary many-to-many BranchProvince table in between.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
cheers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.