One table or two? What about duplicate addresses? 2

[kschouest]

Here I am again, working with the same database referenced in my "design strategy" post, with another design dilemma. FYI, I have not created any tables for this database - it is still in the "drawing on paper" stages.

I am creating a mailing list that will be used by a client to manage business and personal contacts, mostly for holiday and "special occasion" (birthdays, anniversaries, etc.) mailings, as well as newsletters.

Each contact will have a single primary record (personID) in the database, but there could be many different names and addresses and category associations for this person. He might have a single address, but he could be designated as both business and personal. Or he might have a separate address for each.

He might have a formal business name for the business address, such as John A. Doe, Esq., but be just John Doe for his personal address, or Mr. and Mrs. John Doe, or all three, depending on what is sent. He might also receive a newsletter.

In addition to his business and/or personal designation, and whether or not he receives a newsletter, he could belong to one or more subcategories - he might be an attorney who is also a family member and also a friend and also belongs to the same church, so if I ask Access to give me labels for all attorneys, family members, friends, and church folks, he would be included in all of those, but I want him to only get one label in that particular mailing.

However, there will also be scenarios where she has two sets of Christmas cards - one suitable for business and one suitable for personal contacts, and she might want some people to receive both cards, even if it's to the same address. So she would do two separate queries - one for "all personal" and one for "all business" and it would be okay - even desired - for that person to show up on both sets of labels.

But at the same time, I want to be able to identify and in some cases eliminate duplicate information. In some cases, I would want to know about duplicate addresses strictly for informational purposes - like all the labels going to different people at the same law firm. But in other cases, I would want to know so I can delete redundant records.

So the bottom line here is I'm trying to pick my poison. In order to manage the personal versus business addresses and all the variations of addressee types and label types that go with it, it makes sense to have separate tables for personal and business addresses, even if that means having duplicate addresses for people who use one address for both.

But if I do that, how can I identify and distinguish between LEGITIMATE duplicate addresses and UNWANTED duplicate addresses?

If I have it all in one place, what is the best way to link all of the different category types to a single address without requiring too much tedious work on the part of the end-user?

Thanks in advance!

Karen

 

[jrbarnett]

Here goes:

Person table
PersonID with their name, title (Mr, Mrs, Ms, Dr, Prof, Sir etc), post nominals (Esq, PhD etc).

Address table
AddressID with line1, line2, line3, line4, line5 (as many as you need)

AddressType table
AddressType with entries for each type of address: personal, business etc.

PersonAddress table
PersonAddress - PersonID, AddressID, AddressType - composite primary key.

This allows each person to have multiple address records for each type. You can build queries on this for different reasons pulling the various names, address lines and filtering on address type for your various needs.

Include a flag to indicate here whether somebody receives a newsletter or not - this way you can dictate which address the newsletter gets sent to.

ContactType table
Friend, Colleague, Relative, Business acquantance, Client

ContactPerson table
ContactTypeID and either PersonAddressID or PersonID

- with a personaddressid rather than personid you can direct business communications to the business address and personal communications to home address. You need to think about this.

This still doesn't meet all of your requirements - for example you would need an additional table of churches and a linking of person ID to a church ID, but it meets most of them. It also doesn't store the id's of occasions (birthday, anniversary plus the dates to store that - they apply to a person, but you really want to apply it to a personaddress so you know which address to send the alert to).
Note that on a personaddress field you could also put an email field for electronic communications to the appropriate individual.

Hope this gives you a starting point.

John

 

[kschouest]

Hi, John - Thanks for the quick response! This is very helpful to ponder, except for the little kink that, if I'm understanding your setup correctly, I think I am going to still end up with two separate entries of an identical address in those scenarios where an individual has one address that is flagged for both business and personal use. I'm wondering which table to do the search on, or if I need to somehow have the query create and populate a new table just for the purpose of searching for duplicates.

But I'm still stumped as to how to find unwanted duplicates. So far, the best I can come up with is a report that shows the duplicate, plus a field to show the primary key# of each record, so the user can see that it's okay for #12 John Doe to have two records of his address at 123 Main Street, for personal and business use, but #45 John Doe with the same address as #12 John Doe is actually a duplicate. I just keep getting stuck when it comes to defining all the tables and linkages, and how to actually set up the query and/or report.

Thanks!

Karen

 

[MazeWorX]

SELEECT DISTINCT" on the Query will give you distinct instances of what you are looking for.

HTH

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[kschouest]

I didn't know about that option, MazeWorx. I will read up on it. Thanks so much! - Karen

 

[MazeWorX]

err SELECT ... got a little carried away with the keyboard

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[kschouest]

I'll look up that one too. [g] - Karen

 

[jrbarnett]

With my design you would two PersonAddress records for JohnDoe but one address record.

The relationships are:

Person 1:M PersonAddress 1:M Address M:1 AddressType

ContactType 1:M ContactPerson 1:M Person (or PersonAddress)

1:M - 1 to many relationship
M:1 - Many to 1 relationship.

If you set the referential integrity for the tables (primary and foreign key constraints), as you put tables and fields into the query builder it will build the join syntax for you.

To search for duplicates you need to use two instances of the personaddress table looking for the same personID and address type but different addressID's.

As MazeWorX says, SELECT DISTINCT is used to remove duplicate entries from a query result set.

John

 

[kschouest]

Thanks, John - I like that setup, and it makes sense to do that. I am having a very faded flashback memory of having an application where I have used SELECT DISTINCT in the past (for some reason, I don't typically have that kind of thing come up), and after you said what it is for, I remember it now. It does make sense that it would definitely be the thing to do if you know you have duplicate entries, but you only want to see one instance of an entry in a query.

But, of course, in my case, I would only need to view records that have duplicates, and *would* need to see each and every one of those records, so that would not be a time to use SELECT DISTINCT. Although, ironically, this discussion about SELECT DISTINCT does fire off a brainstorm about something I need for another report, so I'm happy about that!

Thank you, John!