Modelling Personnel, Companies and Addresses in Access.

[TheArgyll]

Good Morning All,
I was wondering if anyone could assist me with modelling some data. I am sure its not too complex but it falls outside the field I normally work in.

I have been asked to create a database that will model the Personnel, Companies and Addresses of everyone working on site. I am not having a lot of luck with the basic structure of this database and have included the details below.

I have three tables.

1. Personnel
First Name
Family Name
Badge Number
...

2. Company
Name
Contact Person
Contact Email
Contact Number
...

3. Address
Address 1
Address 2
Address 3
Town
Country
Postcode
...

Each member of Personnel should have 1 or 2 Addresses (Home Address and Work Address (for expats)).
Each member of Company should have 1 Address only (Business Address).

The specific trouble I am having is defining Primary Keys and Relationships between this data.
Any assistance anyone can lend would be greatly appreciated!

Thanks,
Rob

 

[mole999]

I'm sure thats what the NorthWinds Database was designed to provide, the methodologies to link users and suppliers

 

[TheArgyll]

Hi Mole999,
Thanks for getting back to me with this. Is this database packaged with Access or do I need to download it?

Thanks again,
Rob

 

[mole999]

it always used to be part of the Access install, haven't used it in years, have a search, but you may try this from MS https://www.microsoft.com/en-us/download/details.aspx?id=23654

 

[alansidman]

I'm using Access 2013 and if I search Northwind in the Access search bar, the DB comes up. Also, you may wish to look at this whitepaper on db design

http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf

 

[TheArgyll]

Thanks everyone. I did a quick Google and found out how to load the Northwinds template. Now I just need to reverse engineer it to see how it all hangs together. I will have a look at the whitepaper too alansidman.

Thanks again for your help.
Rob

 

[HiTechCoach]

Thanks everyone. I did a quick Google and found out how to load the Northwinds template. Now I just need to reverse engineer it to see how it all hangs together. I will have a look at the whitepaper too alansidman.

Thanks again for your help.
Rob

Rob,

I would not recommend modeling and table design off Northwind (or most of the Access templates). While it does show some cool stuff you can do with Accces, the table design is poor. They use spaces in table and field names which is a HUG NO NO. There have repeating fields which means it is not properly normalized.


See: The Ten Commandments of Access
*** Most Microsoft created Access templates violate commandments #1 and #3.

I highly recommend having one table for people/organizations. A single table for all contacts.

Tables:
Contacts - Names
Contact_Address -A child table to hold all the record for each address type.
Contact_Relations - this will be used to relate contact record. For example employees to companies.

If you want an example that does this really well then check out: Contacts -- Names, Addresses, Phones, Email, Websites

 

[TheArgyll]

Thanks very much Boyd.
I am having a look at this just now, it looks like its pretty much what I need plus some.

Thanks again,
Rob

 

[HiTechCoach]

Thanks very much Boyd.
I am having a look at this just now, it looks like its pretty much what I need plus some.

Thanks again,
Rob

Rob,

You're welcome.

My friend Crystal did a great job with that application. It is a great fully functioning Access application that shows lots of great examples of database normalization and programming techniques.. You can use this database as a starting point.

 

[TheArgyll]

Good Morning All,
I have modeled my data but I have one remaining relationship to complete and its giving me a headache. Literally.

Each Person in my table has a Supervisor. Each Supervisor has one or more Persons working for them. But also each Supervisor is a Person.

How can I model this even if it means changing table structures?

Thanks,
Rob