9 Replies Latest reply: Feb 28, 2015 4:03 PM by Simone Spanio RSS

    I Have doubt in data modeling...

      i have tables like Orders,Products & Categories table as show in below attachment. I want to link  Products & categories tables Orders table. will any one in detailed plz...

       

      Categories and Products Table internally link with the OrderID in orders table.

       

      Message was edited by: Ramaiah Gunti

        • Re: I Have doubt in data modeling...
          Simone Spanio

          Hi,

          in your png is not clear how the three tables are linked.

           

          So, you can structure your data model linking categories to products via categoryID, then products to orderDetails (where you would find the product ID) via productID and orderDetails to orders via OrderID.

           

          But if you'll explain better the actual link in source tables we can be more helpful.

           

          S.

            • Re: I Have doubt in data modeling...

              Hi Simone Spanio,

                                         Will u plz find new attachment

                • Re: I Have doubt in data modeling...
                  Simone Spanio

                  Ramaiah,

                  I'd do something like this:

                   

                  QUALIFY *;

                  UNQUALIFY CategoryID, ProductID, OrderID;

                   

                   

                  Product:

                  SQL SELECT CategoryID,

                      ProductID,

                      ProductName,

                      QuantityPerUnit,

                      SupplierID,

                      UnitCost,

                      UnitPrice,

                      UnitsInStock,

                      UnitsOnOrder

                  FROM Products;

                  LEFT JOIN

                  //Category:

                  SQL SELECT CategoryID,

                      CategoryName,

                      Description

                  FROM Categories;

                   

                  Order:

                  SQL SELECT CustomerID,

                      EmployeeID,

                      Freight,

                      OrderDate,

                      OrderID,

                      ShipperID

                  FROM Orders;

                  LEFT JOIN

                  //OrderDetail:

                  SQL SELECT Discount,

                      OrderID,

                      ProductID,

                      Quantity,

                      UnitPrice

                  FROM `Order Details`;

                   

                  Let me know if it's what do you looking for.

                  S.

                    • Re: I Have doubt in data modeling...

                      I want data modeling like STAR Schema using this data base.

                       

                      Presentation1.png

                      In our data base product table field (ProductID(PID)) internally connect with orderID(OID) and table field Categories table  field categoryid + (ProductID(PID)) internally connect with orderID(OID). using composite key concept.will u know the procedure for this Plz slove it.

                        • Re: I Have doubt in data modeling...
                          Simone Spanio

                          Here it is. Maybe there are other ways to do, but it works:

                           

                          Orders_hr:

                          SQL SELECT CustomerID,

                              EmployeeID,

                              Freight,

                              OrderDate,

                              h.OrderID,

                              ShipperID,

                              ProductID

                          FROM Orders h

                          LEFT JOIN

                          `Order Details`

                          ON h.OrderID = `Order Details`.OrderID;

                           

                          CategoryVsProduct:

                          SQL SELECT CategoryID,

                              ProductID 

                          FROM Products;

                           

                          Orders:

                          LOAD CustomerID,

                              EmployeeID,

                              Freight,

                              OrderDate,

                              OrderID,

                              ShipperID,

                              ProductID,

                              'x' as fakeX

                          Resident Orders_hr;

                          LEFT JOIN

                          LOAD CategoryID as Categories.CategoryID,

                            ProductID

                          Resident CategoryVsProduct;

                           

                          drop tables Orders_hr, CategoryVsProduct;

                          drop field fakeX;

                           

                          QUALIFY *;

                          UNQUALIFY OrderID;

                           

                          OrderDetails:

                          SQL SELECT Discount,

                              OrderID,

                              ProductID,

                              Quantity,

                              UnitPrice

                          FROM `Order Details`;

                           

                          UNQUALIFY *;

                           

                          Customers:

                          SQL SELECT Address,

                              City,

                              CompanyName,

                              ContactName,

                              Country,

                              CustomerID,

                              Fax,

                              Phone,

                              PostalCode,

                              StateProvince

                          FROM Customers;

                           

                          QUALIFY *;

                          UNQUALIFY ShipperID;

                           

                          Shippers:

                          SQL SELECT CompanyName,

                              ShipperID

                          FROM Shippers;

                           

                          UNQUALIFY *;

                           

                          Products:

                          SQL SELECT CategoryID,

                              ProductID,

                              ProductName,

                              QuantityPerUnit,

                              SupplierID,

                              UnitCost,

                              UnitPrice,

                              UnitsInStock,

                              UnitsOnOrder

                          FROM Products;

                           

                          QUALIFY *;

                           

                          Categories:

                          SQL SELECT CategoryID,

                              CategoryName,

                              Description

                          FROM Categories;

                           

                          Let me know if it's what you were looking for.

                          S.

                          • Re: I Have doubt in data modeling...
                            Simone Spanio

                            Ramaiah,

                            if a correct answer is given, please mark it as correct.

                            Thanks.

                            S.

                    • Re: I Have doubt in data modeling...
                      neetha P

                      Hi Ramaiah,

                       

                      Please can you upload sample data.

                       

                      Regards

                      Neetha

                      • Re: I Have doubt in data modeling...

                        Hi Ramaiah,

                         

                        The scenario you shared is a case of Circular Reference. I would suggest you to read about this in Circular References

                        I dont see how shippers table is different from Categories table.

                        But to the data you gave I would suggest you to join Categories and Shippers table based on Order ID.

                        If you want Order Ids in two Different table to hold its uniqueness(to know which order ID is from which table) add one more column to the tables (which identify which Order ID it is). For suppose a column which holds CAT in Categories table. After this concatenate the two tables.

                         

                        Finally load this table formed out of 'Categories and Shippers Table' and Orders table.

                         

                        Regards.