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]

maybe add a role (set from a table to limit options CEO, head Of, Team leader, grunt) to each member and then have a relationship for roles, so you could query for, give me all the supervisors, then by each supervisor list x staff. coded right you could in theory go from CEO to post room. I know some people give CEO a positional number like 1 for the top, 2 head of etc

 

[Kyle123]

Typically you have a foregin key on the person table that is named something like manager, the primary key is the primary key on the Person table - so you have a relationship to itself.

@Boyd, did I misunderstand your point or are you advocating a single table for both people and companies?

 

[HiTechCoach]

@Boyd, did I misunderstand your point or are you advocating a single table for both people and companies?

Yes, I am advocating a single table for both people and companies.

 

[Kyle123]

Why? - They have completely different properties and so many null fields would slow the database down as there's more to index. I'm not picking an argument, I'm genuinely interested in your rationale

 

[HiTechCoach]

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

Rob,

In the contacts example in the link I posted it handles it very well. I have been using the same method for many years in Contact Management/Sales/CRM applications. Look at the Relationships tab on the contact form named f_People. Be sure to pay attention to the list box in the sub form's footer. This is the trick to making this work with only one relationship record. The relationships are stored in the table t_PpLink.

 

[HiTechCoach]

Why? - They have completely different properties and so many null fields would slow the database down as there's more to index.

The argument that they all have completely different properties could also be said for every person and company. So how do you model them all. By following the rules of data normalization.

All the other properties field you are referring to probably need to be in another table to normalizae the data properly. Whenever I see "so many null fields" it a red flag to a normalization issue. I have very few fields unused in all the records with people and companies in the same table.

I'm not picking an argument, I'm genuinely interested in your rationale

Great. Hopefully I can help you learn how to normalized data in a way you might not have considered.

Why put people (humans) and companies in the same table. Because the only different is that companies don't really have a first and last name. Every thing else is identical. I have been developing accounting systems for 30+ years. Accounting system hold the most data in the world about people and companies. I have had the good fortune to work with many very good ones and also some very poorly design ones.

Something to ponder:
What is a company? it is really a unique type of person created by governments for collecting taxes. Companies are modeled after people.

I would urge you to look at the Contacts example. If is one of the best examples I have seen for properly normalizing data

 

[Kyle123]

I'll have a look when I install access, it's not on my computer right now.

I sort of see what you're saying, but it's dependant on the situation, I'm a SQL server developer and there is rarely a "right" solution it's just a balance of factors as I'm sure you'll agree. I've seen various approaches to this, from table inheritance right through to fairly unnormalized designs, none are wrong if done for the right reasons.

Deliberate denormalization can have significant performance increases in the right scenario on large datasets - joins ain't free.

Normalisation isn't the be all and end all if it's a deliberate design decision - you'd never get me normalising first names in a contact table even though it breaks principles "the rules".

I don't agree as a general rule that there is so much cross over, typically contacts can have dates of birth, a sex, passwords etc that you wouldn't associate with companies - I suppose you could argue that a company's dob is its incorporation date but it's a stretch and somewhat cryptic. Again it's horses for courses and there's rarely a "correct" solution.

It's always quite interesting hearing how others design solutions to problems though, thanks for your insight

 

[HiTechCoach]

Deliberate denormalization can have significant performance increases in the right scenario on large datasets - joins ain't free.

Yes denormalization can improve perform. It is great fro data warehousng were the data will never change. If the data is chaning, like for people and companies, denormalization usually ends up having a high cost in actual money due to bad data. Insuring all the duplicate data is updated increase user cost.

Joins are NOT free is exactly why I use a single table.

FWIW: Stating with Access 2007 and the new ACE (.accdb) format I now only use an SQL Server back end for all new development or when upgrading an .mdb to the new format. I find this single table method works even better in with MS SQL Server.

Question:
Have you ever seen a database that has data in every field in ever record in all table?

 

[Kyle123]

I've seen all sorts, yes, I've seen databases with no nulls, I've seen databases where everything is a varchar and the developer enters NULL as a string - but I suspect that's not what you mean

No, normally databases will have Nulls, though I'm aware of a school of though that despises them, especially in the oracle world where Null fields aren't indexed by default. I'm somewhat more pragmatic and use Nulls as appropriate, however there's a world of difference in designing a database table where > 50% of the records will have null values - it's just asking for performance headaches as the table gets large.

All the other properties field you are referring to probably need to be in another table to normalizae the data properly. Whenever I see "so many null fields" it a red flag to a normalization issue. I have very few fields unused in all the records with people and companies in the same table.

The sample database you've posted is an example of what I was talking about, whilst normalized, you end up with an undesirable amount of null fields, in the Person table, companies have the following null fields:

Gender
Title
FirstName
MiddleName
Suffix
DOB

The party model would improve things here and I'd be interested to know why it wasn't considered as a solution.

There are various solutions I've seen to storing companies and people, any of them could be right, but it's important to take into account the context of the application. There are scenarios where it makes a lot of sense to have people and companies in a single table. In applications where people and companies can be used interchangeably, I'd argue that the optional fields should be abstracted al la the party model (which is essentially simulating inheritance if you haven't come across it before). This should allow faster read times and make the sql more efficient whilst keeping things normalized.

To be explicit I wasn't arguing before for denormalization in this case, just that it has its place, it's not usually the first place you'd start though and should probably be done when experiencing bottle necks.

If the data is chaning, like for people and companies, denormalization usually ends up having a high cost in actual money due to bad data. Insuring all the duplicate data is updated increase user cost.

Again, it's application specific, high reads, low writes would benefit from denormalization, and again I agree that it's a cost/benefit situation - though often in reality it's not so arduous keeping data correct, one can use triggers and if not time critical scheduled jobs to clean things up - but I digress, I agree that you wouldn't start a database with denormalization as the norm (if using a relational db) and this only comes with scale and performance issues.

 

[HiTechCoach]

The example I posted has a very good design for normalizing the tables. Better than most. Don't be confused by the Forms. The forms as based on the original designer's needs.

The example I provided is a free and open source template. It is NOT what I use.

The sample database you've posted is an example of what I was talking about, whilst normalized, you end up with an undesirable amount of null fields, in the Person table, companies have the following null fields:

Gender
Title
FirstName
MiddleName
Suffix
DOB

I my design I use the same types if fields for companies.

How I use and/or re purpose the fields in the example for a company are:

Gender = Company Type
DOB = Date if Incorporation or business started

The following are used for the corp officer info for companies:
Title
FirstName
MiddleName
Suffix


I have been able to use the same normalized design for people/companies and other table normalization found in the example for every data I have created for over 10 years. It is true the UI/UX looks completely different and the label are different but the tables are still the same.

I do re purpose field to allow a person and a company to use most, if not all, of the field in a record. Similar to the was the category tables hold all the lookup lists in the example database.