obscure Q on Northwind DB
- Hi all.
I was making up some normalisation exercises using Northwind and came across
something that seems wrong.
there seems to be a surpulfous join table called EmployeeTerritories. Based
on the data I can't see why EmployeeID isn't in the Territories table
Employee 1-m territories NOT Employee m-n territories
Why does it matter?
the b@$$^@#ds will win an argument in saying there are only 3 tables to
normalise to instead of 4 (Employees, EmployeeTerritories, Territories,
SELECT Employees.EmployeeID, Employees.FirstName,
FROM dbo.Territories INNER JOIN
dbo.Territories.RegionID = dbo.Region.RegionID INNER JOIN
dbo.Territories.TerritoryID = dbo.EmployeeTerritories.TerritoryID
dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID
- Just because there is now only 1 employee per territory does not mean
that more than 1 is not possible. Schema is designed for many-to-many
so that is the business model.
--- In SQLQueriesNoCode@yahoogroups.com, "Beattie, Barry"
> Hi all.
> I was making up some normalisation exercises using Northwind and
> something that seems wrong.EmployeeTerritories. Based
> there seems to be a surpulfous join table called
> on the data I can't see why EmployeeID isn't in the Territoriestable
> Employee 1-m territories NOT Employee m-n territories
> Why does it matter?
> the b@$$^@#ds will win an argument in saying there are only 3
> normalise to instead of 4 (Employees, EmployeeTerritories,Territories,
> any thoughts?
> SELECT Employees.EmployeeID, Employees.FirstName,
> Employees.LastName, Territories.TerritoryID,
> Territories.TerritoryDescription, Region.RegionID,
> FROM dbo.Territories INNER JOIN
> dbo.Region ON
> dbo.Territories.RegionID = dbo.Region.RegionID INNER JOIN
> dbo.EmployeeTerritories ON
> dbo.Territories.TerritoryID =
> INNER JOIN
> dbo.Employees ON
> dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID