Contacts DB - Single Organisation, Multiple Addresses

[benjames_au]

Hi.
Im a beginner-intermediate access user (I guess) and I am working on a stakeholder management database, which essentially enables me to separate organisations, individuals, the affiliation between the two, and addresses of either individual or organisation. (pic of relationships included).

Originally, I stored address values on each respective table (ie. org address was on the org table, individual address on the individual table). However then it became apparent that one organisation (in my line of business) can have many address sites, therefore the need to nominate multiple addresses.

So I figured that the best method would be to have an address repository, and orgs or individuals are assigned to that address via a MultiAddress table. The preferred postal address would be nominated by an address preferred field in another junction table.

Seems to work ok through the form (see form pic attached), but Im concerned that when I try to run queries/reports that I'll get duplications or other anomalies.

Can anyone provide me with some tips or advice???

Cheers

 

[Len Boorman]

Basically if an Organisation has multiple address's then you will get multiple records returned when search by Organisation.

Looking at your ER diagram there are a few questions/pointers

Which of these statements is true

An Organisation may have 1 or more address's
An Organisation may have 1 or more Contacts
A Contact may have only 1 address.

If all are true then your ER diagram should reflect not so much an Organisation Address but a contact address and thus where there are multiple contacts you will have multiple address's.

Active or inactive Contacts/Organisations. Do not have separate tables. They are after all the same entities. Simple add a field (Yes/No) to the contacts table. By the link to Address you will then have Active/ In Active address's

It does pay to actually write out statements that describe the relationships between entities in order to work out the actual relationships and exactly what thos relationships are.

My suggestion above is only my interpretation of what you have said. You maybe need to ask more questions and write down the answers.

For instance. Do you have multiple contacts for a given organisation at the same address. If you do then this needs to be reflected in the ER diagram

Hope this has helped at least a little bit

Len

 

[Pat Hartman]

There are a number of other things wrong with the structure.
1. You cannot establish RI between the org and address tables and between the individual and address tables because you are using separate relation tables to manage the relationship. To solve this problem, you need to establish a "parent" for org and individual. This is frequently called tblEntity. The table contains any common fields shared and assigns the autonumber PK. It also contains a field that indicates whether the entity is an org or an individual. The existing org and individual tables are stripped to the non-common fields and their pk is changed to long integer. These tables should be defined to have a 1-1 relationship with tblEntity. This structure allows you to declare RI. You would then get rid of the org-address and individual-address relation tables and replace them with the single entity-address relation.
2. The new entity-address relation should include a third column that identifies the role of this address. It is this field that would indicate which was the preferred mailing address so the use...address fields should get removed from the org table.
3. When you create relation tables that are not the parent of any other table, you should use the two foreign keys as the primary key rather than adding yet another autonumber. If you add the autonumber anyway and make it the pk, you Must add a unique index built from the two foreign keys to enforce the business rule that a relationship only occurs once between the same set of foreign keys.
4. You have used the word "Name" as a column name. This is poor practice since "Name" is the name of a property associated with every object. You will run into problems if you ever need to use this in VBA.
5. You have several instances of repeating groups where you have prefixed or suffixed a column name with a number. Whenever you have more than one of something, you have many and "many" requires a separate table to implement properly. Just because there are only two doesn't alter the rule.
6. The relationship between address and postcode should be on postcode, not town and state.
7. Not all of your relationships have RI enforced.
8. What is that group of 4 tables with postcode as their pk?