Seeking advise on data model design

Wed, 04 Feb 2015 04:03:55 GMT

Hi All,

I have created a data model for Northwind database.

Here is the script and enclosed the data model.

Please help to correct me if anything was wrong.

Thanks in advance.

Customers:
 SQL SELECT CustomerID
 ,CompanyName as Cust_CompanyName
 ,ContactName
 ,ContactTitle
 ,Address as Cust_Address
 ,City as Cust_City
 ,Region as Cust_Region
 ,PostalCode as Cust_PostalCode
 ,Country as Cust_Country
 ,Phone as Customers_Phone
 ,Fax
 FROM Northwind.dbo.Customers;
 
 left join (Customers)
 CustomerCustomerDemo:
 SQL SELECT CustomerID,
 CustomerTypeID
 FROM Northwind.dbo.CustomerCustomerDemo;
 
 left join(Customers)
 CustomerDemographics:
 SQL SELECT CustomerDesc,
 CustomerTypeID
 FROM Northwind.dbo.CustomerDemographics;
 
 Employees:
 SQL SELECT EmployeeID
 ,LastName
 ,FirstName
 ,Title
 ,TitleOfCourtesy
 ,BirthDate
 ,HireDate
 ,Address
 ,City
 ,Region
 ,PostalCode
 ,Country
 ,HomePhone
 ,Extension
 ,Photo
 ,Notes
 ,ReportsTo
 ,PhotoPath
 FROM Northwind.dbo.Employees;
 
 left join (Employees)
 EmployeeTerritories:
 SQL SELECT EmployeeID,
 TerritoryID
 FROM Northwind.dbo.EmployeeTerritories;
 
 left join (Employees)
 
 Territories:
 SQL SELECT RegionID,
 TerritoryDescription,
 TerritoryID
 FROM Northwind.dbo.Territories;
 
 left join (Employees)
 
 SQL SELECT RegionDescription,
 RegionID
 FROM Northwind.dbo.Region;
 
 Orders:
 SQL SELECT CustomerID,
 EmployeeID,
 Freight,
 OrderDate,
 OrderID,
 RequiredDate,
 ShipAddress,
 ShipCity,
 ShipCountry,
 ShipName,
 ShippedDate,
 ShipPostalCode,
 ShipRegion,
 ShipVia as ShipperID
 FROM Northwind.dbo.Orders;
 
 left join(Orders)
 Shippers:
 SQL SELECT CompanyName,
 Phone,
 ShipperID
 FROM Northwind.dbo.Shippers;
 
 left join (Orders)
 
 [Order Details]:
 SQL SELECT Discount,
 OrderID,
 ProductID,
 Quantity,
 UnitPrice
 FROM Northwind.dbo."Order Details";
 
 Products:
 SQL SELECT CategoryID,
 Discontinued,
 ProductID,
 ProductName,
 QuantityPerUnit,
 ReorderLevel,
 SupplierID,
 UnitPrice as Products_UnitPrice,
 UnitsInStock,
 UnitsOnOrder
 FROM Northwind.dbo.Products;
 
 left join (Products)
 
 Categories:
 SQL SELECT CategoryID,
 CategoryName,
 Description,
 Picture
 FROM Northwind.dbo.Categories;
 left join (Products)
 Suppliers:
 SQL SELECT Address as Suppliers_Address,
 City as Suppliers_City,
 CompanyName as Suppliers_CompanyName,
 ContactName as Suppliers_ContactName,
 ContactTitle as Suppliers_ContactTitle,
 Country as Suppliers_Country ,
 Fax as Suppliers_Fax,
 HomePage,
 Phone as Suppliers_Phone,
 PostalCode as Suppliers_PostalCode ,
 Region as Suppliers_Region,
 SupplierID
 FROM Northwind.dbo.Suppliers;

Re: Seeking advise on data model design

arulsettu — Wed, 04 Feb 2015 04:11:12 GMT

Find the Fact table and connect other dimensions table to fact table to make star schema

for more check this

Star Schema or Snow Flaxe schema?

Re: Seeking advise on data model design

robert_mika — Wed, 04 Feb 2015 05:25:14 GMT

Maybe this well help a bit

Fact Table with Mixed Granularity

topic Seeking advise on data model design in QlikView

Seeking advise on data model design

Re: Seeking advise on data model design

Re: Seeking advise on data model design