Loading ...
Sorry, an error occurred while loading the content.

obscure Q on Northwind DB

Expand Messages
  • Beattie, Barry
    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
    Message 1 of 2 , Jul 21, 2003
    • 0 Attachment
      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,
      Region)

      any thoughts?
      thanx
      barry.b

      SELECT Employees.EmployeeID, Employees.FirstName,
      Employees.LastName, Territories.TerritoryID,
      Territories.TerritoryDescription, Region.RegionID,
      Region.RegionDescription
      FROM dbo.Territories INNER JOIN
      dbo.Region ON
      dbo.Territories.RegionID = dbo.Region.RegionID INNER JOIN
      dbo.EmployeeTerritories ON
      dbo.Territories.TerritoryID = dbo.EmployeeTerritories.TerritoryID
      INNER JOIN
      dbo.Employees ON
      dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID
    • hansve2000
      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
      Message 2 of 2 , Jul 22, 2003
      • 0 Attachment
        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.

        Hans.

        --- In SQLQueriesNoCode@yahoogroups.com, "Beattie, Barry"
        <bbeattie@q...> wrote:
        >
        > 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,
        > Region)
        >
        > any thoughts?
        > thanx
        > barry.b
        >
        > SELECT Employees.EmployeeID, Employees.FirstName,
        > Employees.LastName, Territories.TerritoryID,
        > Territories.TerritoryDescription, Region.RegionID,
        > Region.RegionDescription
        > FROM dbo.Territories INNER JOIN
        > dbo.Region ON
        > dbo.Territories.RegionID = dbo.Region.RegionID INNER JOIN
        > dbo.EmployeeTerritories ON
        > dbo.Territories.TerritoryID =
        dbo.EmployeeTerritories.TerritoryID
        > INNER JOIN
        > dbo.Employees ON
        > dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID
      Your message has been successfully submitted and would be delivered to recipients shortly.