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

 

[Kyle123]

Sorry, I'm completely confused, admittedly it may be because I looked at the database with open office. I didn't look at a form, I looked at the Persons tables that does have these null fields?

Horses for courses, if it works for you then all's good. I'd still go for a party model though

Good hearing another point of view

 

[HiTechCoach]

Sorry, I'm completely confused, admittedly it may be because I looked at the database with open office. I didn't look at a form, I looked at the Persons tables that does have these null fields?

That was just some sample data. I do not judge a database design based on a few sample records of fictitious data.. I like to stress test with at least 5-10 years worth of data and lots of worst case examples.

Horses for courses, if it works for you then all's good. I'd still go for a party model though

I did a google/bing search and sis not really find anything good at defining on party model. May not be just the correct search terms.

Good hearing another point of view

I totally agree. I learn there is something called party model.

I get a lot of inspiration helping new people learn Access and database design.

From my math background I have the attitude that anything as it is possible until proven it not.

 

[Kyle123]

I'm struggling to find anything good to recommend online also - you might get more luck with searching for table inheritance. There's a more concrete example of the sort of thing I'm talking about in the first answer here normalization - Same data from different entities in Database - Best Practice - Phone numbers example - Stack Overflow

Essentially it's the sample database taken to the next logical level. One has a base type of party (a table) with one to one ralelationships to other tables for the additional properties - the link above will probably clear up my rather unclear description

 

[HiTechCoach]

Thanks for the link.

hat what I have read it is the same old theory from new names. The first time I was exposed to this theory was back in the late 70's. I have been doing this long enough that I rarely see anything I feel is truly new. Only new names with new lipstick.

 

[Kyle123]

Haha very true, all tech ideas seem to come in cycles when the young ones rediscover older techniques. I can't imagine there's much new when it comes to relational databases - I reckon it's all been done