I need some help with my database design- how to split common things like addresses

[goreyr]

Hello!

I'm pretty new to database management software, but I'm trying to build a database for the foundation I'm working for. It needs to track a lot of different pieces of information, and I feel a little in over my head. I've watched a few strings of tutorials, but I haven't quite found the answers I need yet.

Our foundation facilitates the placements of specially-trained dogs with professional handlers who work for different agencies all over the country.



I was wondering what's the best way to split up common fields of information we'll need among these different groups of people. For example, handlers can have work phones and mobiles and agencies will also have phone numbers. A handler will have a home address and a work address that we'll need to store. An agency will also have their own address. In situations like these, do I try to put all the addresses that belong to handlers AND agencies in a table called tbl1Addresses? Or is it better to make tables called tbl1HandlerAddresses and tbl1AgencyAddresses?

Thanks for the help!

[June7]

I would say need at least:

tblAgencies
AgencyID
AgencyName
Address (separate fields for street address, city, state, zip)
Phone
Email

tblHandlers
FirstName
LastName
HandlerID
HomeAddress (separate fields for street address, city, state, zip)
WorkAddress (separate fields for street address, city, state, zip)
HomePhone
WorkPhone
Email
AgencyID

[ranman256]

I have a table for a person, with PersonID
tClients
----------
[personID] auto
[FirstName]...

a tAddress table
tPhone table
1 to many tables for addresses, and phones

tAddress
----------
[AddrID] auto
[personID] long
[AddrType] (work, home, etc)

so 1 person can have many addresses

tPhones
----------
[PhoneID] auto
[personID] long
[PhoneType] (work, home, cell, etc)

1 person can have many phones

[goreyr]

Thanks for both of your input!

June7- Are you not separating the address information out into its own table? In my research I see that people usually do that, is that not the best way to do it?

ranman256- That mostly makes sense to me! My main question is: since I've got handler's home and work addresses, and agency addresses, do those all go in tAddress? The problem is, a handler will have a work address and a home address. Agencies will also have their own address. It's possible that a handler's work address isn't actually the agency address. So does it make sense to split it into two address tables? One for handler addresses, and one for agency addresses?

[June7]

Depends, if you want to allow many addresses, phone, emails, etc for each individual/agency, then related tables is reasonable. For only two, not sure I would bother.

Can have a common addresses table if the same addresses will be used by many individuals/agencies. If not, doesn't really make sense.

It is a balancing act between normalization and ease of data entry/output. Normalize until it works, denormalize until it hurts (or vice versa).

[goreyr]

Okay, thanks so much! If the handler's work address is going to their primary agency's address about 85% of the time, it would probably make the most sense to have a shared address table then, correct?

[June7]

Not necessarily. In my simplified schema, tblHandler has a field for the AgencyID. Retrieve the Agency address in a query that links the two tables.

[goreyr]

Okay, I think I'm starting to understand. So what about this:

A handler can be related to many different agencies, but only one primary agency. If they do not have a separate work address from their primary agency, their work address should be that of their primary agency.

With this in mind, is it possible to make a query to use the address of the handler's primary agency as the handler's work address unless it is overwritten by something else?

Thanks for all of the help.

[June7]

If you need to relate handlers to many different agencies, this complicates the structure. Probably means a third "junction" table for relating handlers and agencies. This junction table can have a yes/no field that identifies the 'primary' agency. This means no field in tblHandlers for AgencyID.

[goreyr]

Yeah, unfortunately I will need to be able to have a many to many between handlers and agencies, and also one between dogs and handlers. This is what I've developed so far, haven't even tried to stick in addresses yet:

[orange]

Here's a draft model you might consider.
I don't know all of your detail requirements, but put this together based on the thread so far.
Good luck.

[goreyr]

Thanks, orange!

What are the benefits to having the additional triple junction there?

[June7]

It's a compound key because you said handlers and agencies have many-to-many relationship - a handler can associate with more than one agency.

A variation to avoid compound key could be

tblHandlerAgency
HandAgID
AgencyID
HandlerID

tblDogAndHandAg
DogID
HandAgID

[orange]

I really don't know your requirements.
That junction relates
- Dog and Handler and Agency

Your bottom line seemed to deal with this combination - to me.
If this kind of query is not important or needed, you could ignore that junction.

Which Handler handled DogX through AgencyW?

What Agency did Handler R use when handling MyLittleMonster?

If you have a series of queries or reports etc that you need to have answers to, test them against the model. If the junction isn't needed, drop it.

Also, note ( I just saw June's post re compound keys)

In the junction tables I defaulted to compound primary keys. You'll notice, when you make a junction of junctions you add additional fields to the resultant junction (compound) primary key.

An alternative to compound keys in junctions is:

Create a unique primary key (autonumber) for the junction, then
create a unique composite index of the individual primary keys of those tables involved in the many to many. The primary key will handle the requirements of the database. The unique composite index will prevent duplicates.

[goreyr]

Sorry for all the questions, but why do you keep the many to many relationship between dogs and handlers and the many to many between handlers and agencies if I have the triple junction between all three? Am I able to do more by having both the triple junction and the two many to many relationships?

EDIT:

Are the individual many to many relationships the places that let me designate primary handlers between handlers and dogs, and primary agencies for handlers?