In the context of your original question I think you've pretty well answered
your own supplemaentary question. By introducing a StoreID foreign key
column in tbAddressAssign this table models a 3-way many-to-many relationship
type between stores, addresses and employees. I'd add a Position column to
the table as well. You can then record as few or as many employees per store
and their position, e.g. Manager. You can then remove the manager column
from tbStore. Also add an AddressType column to the table ( so it now models
a 4-way relationship type as the tbAddressType table is also referenced. You
can then assign different addresses to each employee and several addresses of
different type for one employee if necessary.
For an employee not based in a store you could leave the StoreID column
Null. Similarly to assign addresses of a particular type to a store rather
than via an employee at the store the EmployeeID column could be left Null.
You can then find a store's physical address for instance rather than one of
its employees' addresses by a query 'WHERE tbAddressAssign.EmployeeID IS
NULL'. This does create a problem in defining the primary key of the
tbAddressAssign table, though, as the key would normally be a composite one
of StoreID, EmployeeID, AddressID and AddressType. No part of a primary key
can be Null, however. The solution would be to have a N/A store and an N/A
employee in the stores and employees tables, each with their own StoreID and
EmployeeID values. Instead of leaving EmployeeID or StoreD Null, therefore,
you'd select the N/A employee or store as appropriate.
BTW your tbAddress table is not fully normalized. You only need to know the
city to know the state and country as city implies state which in turn
implies country, so you don't need state and country columns. Having all the
columns introduces redundancy, e.g. you might be told San Francisco is in
California several times if there is more than one store in San Francisco.
Similarly you'd be told that California is in the USA several times. This
opens up the possibility of inconsistent data being entered as its perfectly
possible to have San Francisco in Alabama or Texas in one or more rows. I'm
aware that the sample Northwind database stores data in this way, but the
fact is that it is not properly normalized, deliberately so for the sake of
simplicity I think, but nevertheless incorrectly. As it happens I posted a
demo file of how to handle this sort of data via correlated combo boxes
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
It uses the local administrative areas of parish, district and county in my
neck of the woods, but the principle is the same. One thing to note about
cities is that the names can be duplicated, so a unique numerical CityID
should be used. You can then distinguish Paris France from Paris Texas!
Normalizing the design like this does take us back to an earlier point,
though, as some countries don't have regional equivalents of a state, or as
in the UK some cities will be in a County (the nearest regional designation
here) and some won't; Stafford, where I live, is in the county of
Staffordshire for instance, but Liverpool where I was born is not in any
county, being a 'unitary' authority. I don't imagine Luxembourg has any
regions at all! The solution is, as with employees and stores, to again have
a N/A region for those countries which don't have regions or where a city
might not be in a region, each with its unique numeric primary key and a
foreign key referencing the countries table.
Finally I'd question the inclusion of previous years' sales in tbStore.
Sales data is best kept in a separate table which references tbStore via a
StoreID foreign key. By including dates in this table its easy to get sales
data for any year or possibly a shorter period of time, depending to what
level of detail the sales data is stored.
Ken Sheridan
Stafford, England
