Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unanswered: DB Design Help Needed

    This one is giving me a headache.

    I have been given the task of adding case worker information to our database. The database is SQL Server 7.

    Here's the relationship breakdown as I understood it:
    The case worker is employed by the agency.
    The agency has many locations
    The locations house many caseworkers.

    [Attached is a jpg of a db diagram showing what I built.]

    Now the problem:

    I found out that the agency does indeed have many offices, but the caseworker can work at many offices.

    A couple of examples would be:
    Caseworker 1 may work Monday-Wednesday at office 1 and Thursday-Friday at office 2

    Caseworker 2 may work mornings at office 1 and evenings at office 3.

    My db structure ties him to one office.

    The question is...

    How would I layout the data structure to accommodate for those workers that work in many offices? My employer is big on data normalization and would like to use one ID to identify the caseworker, the location he/she is at, and the agency he/she works for.

    Thanks,

    Rob Hill
    rhill@thisbox.com
    Attached Thumbnails Attached Thumbnails diagram.jpg  

  2. #2
    Join Date
    Jul 2002
    Posts
    3
    Ok I think I figured it out. Feedback is more than welcome.

    Attached is another diagram showing what I've come up with.

    Here each CaseworkerID could actually refer to desks owned by caseworkers.

    Off topic, but I am using SQL Server but I don't consider my question to be related to SQL Server... Is there a better place for posts such as this?

    Any other suggestions would be helpful.

    Thanks,

    Rob Hill
    rhill@thisbox.com
    Attached Thumbnails Attached Thumbnails diagram2.jpg  

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You would need to create an associative entity, a cross relationship between CaseWorkers and AgencyLocation

    Example

    CREATE TABLE Schedule
    (
    CaseWorkerID int NOT NULL,
    AgencyLocationID int NOT NULL
    )

    So given a CaseWorkerID you could then get a list of Locations that the work is assigned to. You can add additional columns to this table to indicate when the worker is at this location.

    CREATE TABLE Schedule
    (
    CaseWorkerID int NOT NULL,
    AgencyLocationID int NOT NULL,
    Monday bit,
    Tuesday bit,
    Wednesday bit,
    Thursday bit,
    Friday bit
    )

    This is just an example.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •