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

[orange]

why do you keep the many to many relationship between dogs and handlers and the many to many between handlers and agencies

You don't keep them. The tables and relationships represent your business rules/facts.
You indicate a Handler can handle several Dogs and a Dog can be handled by many Handlers.
Similarly, a Handler can be associated with many Agencies, and an Agency can represent several Handlers.


In database diagramming you break the many to Many relationships into multiple 1 to Many relationships.
If the tables and relationships in the draft model don't match or meet your business requirements/facts, then adjust as necessary. There is nothing sacred/permanent with the draft model. It was intended as a starting point for consideration based on my understanding of your post.

[goreyr]

I think it might be helpful if I do a better job explaining what we need to be able to do with the database/what it needs to store.

1. A dog can have many handlers, and a handler can have many dogs.
2. A handler can work at many agencies, and an agency can use many different handler's services.

3. A dog can only have one primary handler, and a handler can have only one primary dog. So if Dog Bark is Handler Steve's primary dog, then Handler Steve must be Dog Bark's primary handler.
4. Now this one's a little different: A handler can have only one primary agency, but an agency can have more than one primary handler. So Handler Linda's only primary agency can be Center1, and Handler Bob's only primary agency can also be Center1, or some other agency.

5. All handlers must have a primary dog, and all dogs must have a primary handler.
6. No agency MUST have a primary handler- some only have secondary handlers. All handlers must have a primary agency.

We would like to be able to do any search like the following:

If I select dog Bark, I'd like to know: Who is Bark's primary handler? Does Bark have any secondary handlers, and if so, who? What agencies do those handlers work for, and which are the primary agencies?
If I select Handler Steve, I'd like to know: Does Steve have any primary dogs? Does Steve handle any secondary dogs? Where is Steve's primary agency? If he has them, what are his secondary agencies?
If I select Center1, I'd like to know: Who are Center1's primary handlers/secondary handlers? What dogs all those handlers primarily/secondarily handle?

Unfortunately, to make it even more complicated, we'd like to be able to store the time frames of each placement too. I want to be able to add a condition of a timeframe for all those conditions. Like, in May 2007, who was the primary handler for Dog Bark? How about currently? How many dogs were actively working in 2009? How about now?

In terms of information I'd need to be storing- I'll need the handlers' home address and work address, and all the agencies' addresses as well.. 80% of the time, that handler's work address will be the same address as their primary agency, but 20% of the time, it won't be. I also need stuff like handlers' and agencies' contact information, and some other random assorted data.

I hope that helps more than it confuses everything. Thanks for all of your attention and time, I really really appreciate it.

[orange]

For clarification:
This is not a homework assignment- is that correct?

From your post, you have Primary Handlers, and others -- but you mention secondary??
Is there a separate group called Secondary Handlers or are they really others.
Where/what info do you record for Dog worked? What do Dogs work at? Are these special services of some sort? More details please.

[goreyr]

You are correct- this is not a homework assignment. This is a project I'm trying to do for the nonprofit organization I'm working for. None of us have much experience at all with database management software, so I volunteered to tackle the project and try to figure out what's going on.

Thanks for catching some bad terminology on my part- what I mean by a secondary handler is indeed just an "other" handler. They are just handlers for the dogs who are not its primary handler.

The dogs I'm referring to are courthouse facility dogs- they are graduates from an Assistance Dogs International accredited organization, such as Canine Companions for Independence or Assistance Dogs of the West. They help comfort victims of violent crimes/abuse. These dogs are all placed with a handler. The handlers bring the dogs to a number of agencies in their area to offer their services to victims of crime/abuse (they are usually employed at one main agency, and work at an on-call basis to nearby related agencies).

We help facilitate their placement and develop best practices for their use. Since this is relatively new, their numbers are small (under 100) but it's catching on. We need a database to store all the information we'd need to know about them, because up until now, we've been keeping track of it in our heads (obviously not sustainable).

The data we're interested in tracking will eventually be used to look up information about any particular dog, handler (who is handling a courthouse facility dog), or agency (who might employ/use one of these dogs which always goes through their handler). We also want to be able to use it to see things like how many dogs were working in any given year, or for example, how many child advocacy centers (a type of agency) have access to a courthouse dog? This kind of data helps us keep track of our operations, keep in touch with handlers and agencies using these dogs, and get data about the dogs for things like writing grants/answering general questions about their geographic spread and numbers.

We DON'T need to record stuff like each dog's daily assignments. That's up to the handler, and it's way more detailed than we'll need to know. Does that help?

[June7]

Do you really need to keep track of what 'other' handlers work with a dog?

[orange]

OK. It's an interesting task.
Is a placement
-an assignment by an Agency to a Customer of a Dog and Handler for a period of time?

Do you record the placement Customer along with the placement start and EndDate?

[orange]

I am mocking up a database from the model --manually. It may be helpful to ensure all the facts are captured and tested before the "real" database gets built. This is more of a "proof of concept" to see that the model and physical database can support the "business" and samples you posted.

I think there are more facts required related to

who is handling a courthouse facility dog

and

how many child advocacy centers (a type of agency) have access to a courthouse dog

There must be a list of "services" or "facilities" descriptions associated with Dogs or placements. And some attribute(s) related to Agency (agency type??) that are not yet defined.

I'd like to see more details or samples of what these things are, and how they relate.

There are also facts surrounding:
primary Handler
-- is this for a period of time?
-- Dogs die or removed from Service, so need some reassignment process?
-- Handler moves/is removed and must assign another?

Similarly with Agency
--is primary Agency for Handler for a time period?
--Agency changes name/location/leaves the business?

and there will be more....

[goreyr]

June7 - Unfortunately, yes, we need to know which other handlers can handle the dogs, even if they aren't its primary handler. That's how we intend to track how many agencies have access to dog, even if they don't have their own handler for one working at their agency. If an agency has a handler that has been trained to borrow a particular dog on an on-call basis, we need to accurately reflect that in our records- if we claimed that they actually had their own dog, we'd be inflating the numbers, but if we only said that they had no dog at all, they'd be misleadingly low.

orange - The way it usually works is this:

Say you have the Red County District Attorney's Victims Services Unit (this is an example of an agency). The Victims Services Unit decides they would like a courthouse facility dog, so they wait until they have an already existing staff member who wants to be the handler for the dog. They will continue their profession as a victim's advocate, but agree to use the dog in their work. Let's say Victim Advocate Bob works for Red County DA's VSU, and decides he wants the dog. So Bob applies together with the Red County DA's VSU, and Bob becomes the handler for BowWow.

The placement is the assignment of Dog BowWow to Handler Bob at Red County DA's VSU. Several years later, say Bob decides he no longer wants to work for Red County DA's VSU. He wants to work for Blue County DA's VSU instead. One of four things can happen:

1. BowWow might retire. We would want a way to designate BowWow's status as retired.
2. BowWow could move with Bob to Blue County DA's VSU, at which point this would be a NEW placement- BowWow handled by Bob at Blue County DA's VSU.
3. BowWow could be placed with a different staff member working at Red County DA's VSU. Say Linda works there and wants the dog. So now BowWow is handled by Linda at Red County DA's VSU. This is a new placement.
4. BowWow might be placed with an entirely different handler at an entirely different agency. This would be a new placement.

In summary, if any of the three pieces change (dog, handler, or agency), then it becomes a new placement.

I'm not sure what the best way to store information on other handlers for the dog would be, but it is important data. What if there were Primary placements and other placements? So if a dog's primary handler changes, or his handler's primary agency changes, then it is a new Primary placement. And then, if another handler is trained to occasionally borrow the dog for some small task, it is entered as an "other" placement or something?

In terms of the people actually using these dogs' services (the Customers) - We don't need to track that. That data is managed by the handler and their agency- we only need to keep track of what dogs are paired with whom at what agencies.

It WOULD be good to have a start date and end date for each placement though, as they do change.

By attributes for agencies, did you mean what types are there? There are 8 types - Child Advocacy Center, Prosecutor's Office, Law Enforcement Agency, Nonprofit, Sexual Assault Center, Family Justice Center, CASA/GAL, Court, and Other.

[orange]

Ah, now were getting to the meat of it all. You need to determine/project as best as you can what this really is/does/represents

And then, if another handler is trained to occasionally borrow the dog for some small task, it is entered as an "other" placement or something?

Going back to one of your earlier questions what does the DogHandlerAgency junction table represent--- it seems more and more like a placement.

As a worthwhile exercise, take each of your major entities and write a 2-3 line description of each. It will not only help you clarify your thinking, but it will be useful documentation for anyone using or maintaining the system/database and for training purposes. See if your placement definition/description accommodates "borrows".

Also, take Retire Dog as a process/event:
Let's suppose OldYeller is being retired. He is currently HandledBy Steve who is the PrimaryHandler.

OldYeller has worked/been placed through Center1.
What things should be done to Retire OldYeller?

[orange]

I have added some comments/questions within the text of your previous post. My comments are in capital letters and green text (I hope). They are meant for consideration to clarify thoughts and facts for your database. I recommend you start an official/authoritative list of facts that you keep current. ie Each DOG has a Primary Handler. These will be important as you proceed.

Say you have the Red County District Attorney's Victims Services Unit (this is an example of an agency). The Victims Services Unit decides they would like a courthouse facility dog, so they wait until they have an already existing staff member who wants to be the handler for the dog. They will continue their profession as a victim's advocate, but agree to use the dog in their work. Let's say Victim Advocate Bob works for Red County DA's VSU, and decides he wants the dog. So Bob applies together with the Red County DA's VSU, and Bob becomes the handler for BowWow.

WHERE/HOW DID BOWWOW ENTER THE SCENE?? NEW DOG!! WHAT IS THE INTERACTION WITH THE DATABASE(IF ANY) BEFORE BOB BECOMES THE HANDLER. DOES BOB AUTOMATICALLY BECOME PRIMARY HANDLER OF BOWWOW. WHAT IS BOBS STATUS WITH THE AGENCY (PRIMARY/OTHER?)

The placement is the assignment of Dog BowWow to Handler Bob at Red County DA's VSU. Several years later, say Bob decides he no longer wants to work for Red County DA's VSU. He wants to work for Blue County DA's VSU instead. One of four things can happen:
HANDLER CHANGES AGENCY
1. BowWow might retire. We would want a way to designate BowWow's status as retired.
BOWWOW WOULD BE FLAGGED AS RETIRED BUT EXISTING PLACEMENTS, HANDLER INFO ETC WOULD BE RETAINED FOR HISTORY/QUERIES.
2. BowWow could move with Bob to Blue County DA's VSU, at which point this would be a NEW placement- BowWow handled by Bob at Blue County DA's VSU.
WHAT IS BOB'S HANDLER STATUS (PRIMARY/OTHER)? NOCHANGE WITH STATUS WITH BOWWOW, BUT WHAT ABOUT BOB WITH AGENCY
3. BowWow could be placed with a different staff member working at Red County DA's VSU. Say Linda works there and wants the dog. So now BowWow is handled by Linda at Red County DA's VSU. This is a new placement.IS LINDA NOW BOWWOWS PRIMARY HANDLER? WHAT HAPPENS TO BOB STATUS WITH BOWWOW AND AGENCY?

4. BowWow might be placed with an entirely different handler at an entirely different agency. This would be a new placement.WILL YOU RECORD THE DATE OF HANDLER CHANGE? NEED TO UPDATE DOGHANDLER. HOW DO YOU KNOW THE STATUS OF NEW HANDLER WITH AGENCY? HOW DO YOU SELECT THE NEW HANDLER AND STATUS RE BOWWOW(PRIMARY/OTHER)?

In summary, if any of the three pieces change (dog, handler, or agency), then it becomes a new placement.
SO A PLACEMENT INVOLVES DOG + HANDLER + AGENCY

I'm not sure what the best way to store information on other handlers for the dog would be, but it is important data. What if there were Primary placements and other placements? So if a dog's primary handler changes, or his handler's primary agency changes, then it is a new Primary placement. And then, if another handler is trained to occasionally borrow the dog for some small task, it is entered as an "other" placement or something?
SEEMS LIKE WHEN DOG IS WITH HIS PRIMARY HANDLER ITS A PRIMARY PLACEMENT. ANY OTHER HANDLER MAKES IT A SECONDARY/OTHER PLACEMENT. IS THERE SOME THING IMPORTANT ABOUT PRIMARY/OTHER DESIGNATION (YOU RECORD SOMETHING ELSE?)

In terms of the people actually using these dogs' services (the Customers) - We don't need to track that. That data is managed by the handler and their agency- we only need to keep track of what dogs are paired with whom at what agencies. SEEMS LIKE THE DOGHANDLERAGENCY(PLACEMENT) IS A KEY TABLE IN THE DATABASE APPLICATION

It WOULD be good to have a start date and end date for each placement though, as they do change.
YES AND PROBABLY THE SAME FOR HANDLER ASSIGNMENT AND STATUS AND ALL CHANGES

By attributes for agencies, did you mean what types are there? There are 8 types - Child Advocacy Center, Prosecutor's Office, Law Enforcement Agency, Nonprofit, Sexual Assault Center, Family Justice Center, CASA/GAL, Court, and Other.
IS THIS 8 TYPES OR A LIST OF 8 AGENCIES YOU WILL WORK WITH. IF TYPES, DO YOU HAVE A LIST OF CURRENT AGENCIES. HOW DO AGENCIES MATERIALIZE (ORIGINATE IN YOUR DATABASE/BUSINESS?
HOW DO HANDLERS ORIGINATE/BECOME KNOWN TO YOUR BUSINESS?

[orange]

Any update to status??