Is this a normalisation issue?

[Tony Williams]

I have a database that holds records of people who work for organisations. I
have a table for the business which includes the name and head office
address and a table for the individuals which apart from the usual
firstname, surname etc has fields for a business branch address. Some
businesses have more than one branch and there are more than one individual
at a Branch. I'm trying to create a report that shows the business name, the
branch addresses and the individuals that work at each branch. However when
I run the report the names are duplicated and the branch addresses are
duplicated because there are more than one record with the same branch
address. I was wondering whether this was a problem with the database
structure or is it possible to single out one Branch address which is
repeated over a number of records and then list the individuals attached to
that branch?
Any help, guidance, suggestions would be welcome.
Cheers
Tony

 

[tina]

if you're entering the same branch office address repeatedly in multiple
employee records, that's duplication of data, which breaks normalization
rules. remember that each table should describe one entity (subject). a
branch address does not describe a person, it describes a location of a
business - putting those fields in the employee table again breaks
normalization rules. what you have are businesses that operate from many
locations, with many employees, so

one business may have many addresses, but each address is the "home" of one
business. that's a one-to-many relationship.

one *type* of business office many be at many addresses, but each address
houses only one type of business office. there's another one-to-many
relationship.

one business office may have many people working at it, but each person
works at only one business office. that, too, is a one-to-many relationship.

suggest four tables, as

tblBusinesses
BusinessID (primary key)
BusinessName
<other fields that describe the business, as needed, but not *location*
fields>

tblLocationTypes
TypeID (pk)
TypeName
<you'd have at least two records in this table: "head office", and
"branch">

tblBusinessLocations
LocationID (pk)
BusinessID (foreign key from tblBusinesses)
Street1
Street2
City
State
Zip
TypeName (foreign key from tblLocationTypes)

tblEmployees
EmployeeID (pk)
FirstName
LastName
LocationID (fk from tblBusinessLocations)
<other fields that describe a person>

you can pull these tables together in a query, and then use grouping at the
report level to present the data as you described in your post.

hth

 

[Tony Williams]

Thanks Tina, really useful, I thought I might have a normalisation issue.
Thanks again
Tony

 

[tina]

you're welcome

Thanks Tina, really useful, I thought I might have a normalisation issue.
Thanks again
Tony