Is it worth using a household field?

[huggybear]

I have a database with clients being the main table. There can be several clients living at the same address. The total number of clients will be less than 500. I already have an option for data entry to add a client at an existing address that fills in the address and phone info to save data entry work.

Is it worth going back and redesigning the tables to include a household table and relating clients to it? One reason I ask is that suppose a notice would be mailed to clients, chances are that only one notice would need to go to each address.

Thanks in advance.

Bear

 

[montrose]

Bear, Are you using a separate address table? Need a little more info about your structure, please. Montrose Learn what you can and share what you know.

 

[huggybear]

Montrose, thanks for the interest!

No, I don't currently have a separate address table. The address stuff is part of clients.

If I went with the change to having a household table, I'd have the address stuff in it (as well as home phone and anything else applicable to a household), then have a householdID field in the client table to connect an address to a client. At least that is what I've been thinking.

I guess what I'm wondering is whether it is worth having an extra table, household, in order to avoid having some repeating data in the existing table. Is it a goal of normalization to avoid that situation in all possible cases?

Bear

 

[montrose]

Bear, One of the fundamentals of normalization is to remove 'repeating data' into another table(s). You stated "I have a database with clients being the main table. There can be several clients living at the same address." The purpose of your database should be based upon the basic 'rules' or how things are done as you design your database to accomplish that purpose.
From a relational viewpoint, ONE address has MANY clients. Is it also true that ONE client can have MORE THAN ONE ADDRESS? Again, goes back to the purpose of your database. Most people today have a home address, a work address, their work address may include a billing address as well as a location address..... Will you only need one phone number per client? Does the phone number BELONG (relate) to the address? Or does the phone number belong to the client? Some poeple today are using a cell phone in place of a home or office phone. Some people may live in the same house and have two or more phone numbers. Or when asked for a phone number will give a home number, a work number, an alternative work number, a cell phone, a beeper/pager number to contact them, only want to receive email......

Not trying to confuse or complicate things for you! But since you were "wondering (if)it is worth having an extra table, household, in order to avoid having some repeating data in the existing table", you have to look at the purpose of your database to answer that question. For me, separate contacts, addresses, phone (& or email/web sites!) tables are the only way meet the complexities of todays communication/life style issues. Takes a little more time to set up initially, but saves hours of query heartache down the road. Not knowing more about your database, I'd suggest the possibility of a separate address table, at least. Good luck!, and post back if you have more questions or would like additional suggestions. Montrose Learn what you can and share what you know.

 

[huggybear]

Montrose,

First of all, I want to thank you for the amount of time and thought you've put into your response to my question.

I didn't realize that I would be getting so much "bang" in the answers to my question so a little clarification would probably be in order.

These are clients at a hair salon. The only address information that is necessary is the home address. Since some adult clients also bring their children there can be a one address ("household&quot to many clients relationship but not the other way around.

Each "household" has a phone number. Each client may also have a work number. I know that nowadays there are often times more phone numbers than that associated with people, but that is all that was specified so that's what there is.

I find it interesting that you break out phones and web sites etc. in separate tables. I hadn't thought of taking things that far but I am intrigued by that thought. It seems that in certain cases you would be repeating data in one table or the other if a phone number belonged to more than one person. Say for instance that 5 people shared a phone number. If you put the number in each of their records, you'd be repeating the phone number. However, if you put the phone number in a table of phone numbers, you'd be repeating the person id in that table. That is the sort of dilemma that I find myself in when I design a database. As you can probably tell, I'm kind of new at this.

Again, thanks for your help and interest!

Bear

 

[JimHorton]

huggy,
I think what you need here is an 'associative' table.

tblClients<--->>tblAddrLink<<--->tblAddresses

Here, the tblAddrLink contains at least two fields--the Client key and the Addresses Key--which together are the PK of this table. Therefore a client can have many addresses, and the same address can be used by many clients.

Now, add more fields to tblAddrLink, such as:
**AddressUse--How does *this* client use *this* address (home, work, etc)?
**Active Date--When did this address become valid for this client? (or use 2 fields for a 'valid date range')

From this logic, you maight now add a field to the Clients table, which contains the 'PrimaryAddr' addressID--so mailings go to only one addr. Alternatively, you might add a 'PrimaryAddr' Yes/No field to the link table. The logic gets murky here because, without db-level triggers, you need to manually verify that there is only one primary address in the link table per client...and making the 'PrimaryAddr' or the 'AddressUse' field part of the key to the link table is not necessarily a good idea--a Yes/No field would then be stuck with only one 'yes' and one 'no'; and for AddressUse--maybe the client has 2 addresses under 'vacation use', for example.

There are ways to do this effectively, but, as Montrose suggested, you need to be an analyst for a moment and decide how the db will be used.

Anyway, it's somthing to mull over.
--Jim

 

[montrose]

Bear, Repeating the personID in an &quot;associative&quot; (or junction table or resolver table) as Jim pointed out is the way to tell MS Access to handle a many-to-many relationship. That 'repeating' piece of data can be handled by Access and you'll never have to worry about keying it in. Beats entering the same phone number/address over and over when more than one family member comes to your salon. Using a many-to-many relationahip means that when a customer(s) moves and gets a new address or phone number, you only have to enter the new info once, instead of 2, 3 etc. times. Since you said you were pretty new at this, humble suggestion to pick up a book or two on Access that covers some of these basics to help you figure out what's best for you. Good luck, post back if you've got more questions. Montrose Learn what you can and share what you know.

 

[huggybear]

Thanks to you guys.

Bear

 

[montrose]

Bear, You're welcome. Montrose Learn what you can and share what you know.

 

[huggybear]

Okay, I can see what you are driving at with the associative entity here. Now I have a question about the best way to create a user interface to implement it.

A person has a first and last name. A person also has an address, phone etc. Since the first name is the only unique bit of information for a &quot;household&quot;, how would you ensure that a person gets put into an existing household rather than duplicating household information.

Would you use a combo box with all the last names in the table so a user can either select an existing last name or add one if necessary? Or would you just scan the tables to check for duplicates and proceed from there? Or, would you do something entirely different?

I'm just looking for ideas here, not any specific code for duplicate checking, if that's how you would do it.

Thanks again, Bear

 

[JimHorton]

huggy,
The unique field in the Link table should *not* be Firstname, it will be the pair of ID's--Client and Address. Now, within one address, you may have several different Client ID's, and these *can't* be unique, since the key of the link table is this pair of ID's.

However, for your lookup field, I'm not sure how your system works...if you had a client up on the client form, and you wanted to add an address for him to 'belong' to, you'd most likely want to use a separate form (in continuous view), that lists all available addresses. I don't like combo or list-box approach because a form is simply more powerful and more flexible. Anyway, you then either search for a street or address that is similar (using Like*), and if not found, then add a new one to the table, and then your code just adds a new entry to the link table.

This is the downside to this approach--you have the advantage of using an address just once, but you have the burden of having to find it when you want to use it again, else you risk 'quasi-duplicates', ie, you're sure that you've entered 123 Any St., but a search doesn't find it, even though 123 n. Any St., or 123 Any Street' exists. So if you widen the search, you have more luck finding the one you want.
--Jim

 

[huggybear]

Jim,

Sorry to give you the impression that I meant first name to be &quot;unique&quot; as if it constituted a unique identifier field. I only meant that of all the people in a &quot;household&quot; each would probably have a different first name (unless there's a junior of course, or George Foreman is your dad).

You hit on what I see as the dilemma. It seems in order to make your database more normalized you tend to make data entry more bothersome. So that is why I was wondering how some of you experienced database gurus hit on a balance between the two objectives.

Thanks for your input!

Bear

 

[montrose]

Bear, What Jim is saying actually makes data entry easier. You can use form/subforms if you want-I'm a 'vba challenged' individual myself and try to let the relationships in Access work for me whenever possible. To the user or salon receptionist entering info into your main 'client' form, the continuous view of an address subform would work well. Or you could have a main client form for their last names, a continuous subform showing related family members, and a continuous subform for phone numbers (the number of subforms on your main form is dependent upon which version of Access you're using). Your actual table structure that fits your needs will help you to determine the best user interface, based on what the user really needs as they work with their customers and the data. Hope I haven't confused you more and have given you some additional ideas to think about. Montrose Learn what you can and share what you know.