Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    Unanswered: Structure Question

    The Set Up
    I have an access 2k DB that tracks several clients. I have a Client_Tbl Table that keeps the clients main information Id# names etc.. Each client has a treatment plan consisting of Goals and within those Goals are several objectives. Typically each client has 5 - 9 goals and each goal has 3-6 objectives. The data is mostly unique.

    The Question
    As illustrated each client has differeing amounts of information to track. I do not think addning fields for the most potential goals and objectives therein to my Clients_tbl as adding 54 fields to the Table seems sloopy especially given that half of them could be NULL.
    How would you set this up?
    Would this be better posted in another forum ?
    Darasen

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Client_Tbl:
    IDclient, auto, PK
    blah blah to do with Client

    tblGoals:
    IDgoal, auto, PK
    IDclient, long, FK
    blah blah to do with the goals

    tblObjective:
    IDobjective, auto, PK
    IDgoal, long, FK
    blah blah to do with objectives

    each Client can have from 0 to millions of Goals, each Goal can have from 0 to millions of Objectives.

    ?? izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You nailed it on that response Izy. Darasen, the important thing is to leverage the strengths of a relational database. You explained it well when you told us you have Clients, those Clients can have any number of Goals (one to many relationship), and those Goals can have any number of Objectives (another one to many relationship) If you set up a table for Clients, another for Goals, and another for Objectives as Izy suggests and establish these relationships, you have a very efficient database that is less cluttered.

    TD

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Thanks for the responses.
    I was thinking along those lines and, it's nice to have confirmation. I can do the SQL and am picking up more and more of the VB but the data structure I worry about the most. Actually a good thing I guess. you can always make new queries and forms but a bad structure and your DB is no good.

    The once Flat DB handed to me has grown to 8 tables now.

    Thanks again.
    Darasen

Posting Permissions

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