Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2008
    Posts
    114

    Unanswered: Confused myself... joining tables..

    Hello.
    I don't know what planet I am on lately, I just cant figure this out.. If anyone can point me in the right direction...

    I go over a clients chart once or twice a month.
    I check all paperwork is correct and if not.. make a note of it...It's called a QA (quality assurance)

    Anyway...
    so I think
    1 client has lots of QA dates
    and.... ummm
    each date will have one record

    I think

    Tracy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you have a question?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by pootle flump View Post
    Do you have a question?
    I cant figure out the table structure.

    1 client will have lots of QA dates.

    Each QA date will have a record...

    I guess I need to compare a QA from this month with one from last month...

    ClientName
    QADate1
    Problem1
    Problem2
    QADate2
    Problem1
    Problem2 completed
    Problem3
    Problem4

    and on....... etc...

    Does it make any sense?

    Tracy

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - so the problem is database design? (FYI - tables are only ever joined in queries)
    Database design and normalisation are just the "formal application of common sense". In other words, like much of computing, database design is usually pretty easy once you have fully defined the problem.

    Looking at your table I am inferring:
    You have some clients (let us call them clients)
    For each client you perform QA on a given date (let us call that quality_assurance)
    For each quality_assurance you will identify 0 or more problems, each of which must be resolved.

    Is this correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2008
    Posts
    114
    Thats exactly right pootle

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good - so we have define the entities (or "things"). Your database design models your business (in this case a small subset of your business) to support your business processes (or "actions"). We are now interested in what you do in more detail.

    What processes do you go through for your QA? What are the stages and the actions you undertake?
    For example, what constitutes a "comparison"?
    Quote Originally Posted by Gwyar View Post
    I need to compare a QA from this month with one from last month...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2008
    Posts
    114
    Thank you for the help, it is much appreciated...
    Basically what I have to do is go though the clients paper chart and make sure everything is correct. T's crossed, I's dotted etc..
    Information releases are signed and dated by the appropriate parties. Progress notes are in a particular format and that there are a specific amount of progress notes per day....
    Whatever needs to be corrected within the paper charts basically.
    These errors or omissions, whatever they happen to be are then passed on to a primary counselor responsible for that particular client who will then hopefully correct them. When the next quality_assurance is done, the new one and the old one can be compared and modified. And a new quality_assurance is sent to the primary counselor.,

    I have a system in place that basically does that now, but instead of 1 date for when the whole quality_assurance is done, I have a date per problem, which is repeating itself. If the chart has 20 problems, there are 20 dates.
    (although saying that some problems do have date issues, but I have specified a field for that already)

    I just need one date per quality_assurance, and I cannot for the life of me figure it out... I know it can't be that hard, but I just dont know...
    This is why I am thinking I need a linking table which holds the date of the quality_assurance, so one date per record..

    Tracy

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Three tables.
    One client has 0 to many quality_assurances. One quality_assurance has 0 to many problems.
    We need to discuss what "comparing" these actually means to the database and how the results of this comparison are stored, however it might not be something we need to model.

    client {client_name, counsellor}
    quality_assurance{client_name, date}
    problem {client_name, date, problem_id, description, date_solved}
    The above are tables. The comma separated values are attribute names. The underlined attributes are primary key columns. Foreign keys (relationships) are based on matching attribute names. Obviously we need to identify more attributes.

    Got to go, but I can't leave without The Link:
    The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2008
    Posts
    114
    Thank you much..

    I have a couple of questions...
    Why use client_name and not the ClientID ?
    Disn't realise dates could be PK's?

    What connects the quality_assurance table to the Problems table?
    I am starting with a new DB so I can totally follow how it works...


    Tracy

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Access treats autonumbers and Primary Keys as synonymous. It is one of the dumbing down aspects of Access I deeply dislike.

    When creating a database you design a logical model. When you have finalised this you then create your physical model and implement this in your database.

    During your logical model phase you should only ever use natural keys. the reason is that there is less abstraction, your model is therefore simpler and normalisation (especially second normal form) is easier.
    When implementing this model you may chose to substitute some, or all, of your natural keys with surrogate keys. It is at this point you might decide you prefer to use client ID rather than client name as your primary key. Note that you do not have to use surrogate keys in a database despite the assertions of many practitioners.

    I would recommend you read that link and do some additional reading around the subject to get some context for the above.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by pootle flump View Post

    I would recommend you read that link and do some additional reading around the subject to get some context for the above.
    I printed the whole thing out and am working my way through it.
    I think I know what my problem is:
    In the ERD section of the link you sent it shows the typical sales-customer- order process. Which I understand...
    Where I fall down is on this:
    I look at this example and say..
    ok 1 customer has many orders:
    1 order has many products

    why does 'many products has many orders' keep coming into my mind?

    Im sure this is really basic stuff...But I want to know, Many orders can have lots of products, but why not the other way around. Many products have many orders.

    Thank you very much

    Tracy
    Last edited by Gwyar; 01-28-10 at 06:37.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tracy - you are not wrong. Your reasoning is quite right and, I think, you have uncovered an error in that article.

    Typically an order system would be something like:
    customer(customerID, name, address, ....)
    product(productID, name, manufacturer, ...)
    order_header(orderID, customerID, date, invoice_details, ...)
    order_line(orderID, productID)

    There is a many-to-many relationship between orders and products which is represented in the order_line (or sometimes order_details) relation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd agree its table design related
    personally I'd expect something like

    QA 'stores details of the last QA for each patient but date
    patientID
    QADate
    ReviewedBy
    'and so on

    QAIssues
    PK of the QA table + some form of ID detailing issue number
    IssueType 'optional FK to an issues table to allow you to group simialr issues (could be say medical, mental, family, environment whatever
    Description
    FollowupRequired
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by pootle flump View Post
    Tracy - you are not wrong. Your reasoning is quite right and, I think, you have uncovered an error in that article.

    Typically an order system would be something like:
    customer(customerID, name, address, ....)
    product(productID, name, manufacturer, ...)
    order_header(orderID, customerID, date, invoice_details, ...)
    order_line(orderID, productID)

    There is a many-to-many relationship between orders and products which is represented in the order_line (or sometimes order_details) relation.
    Oh... I wasn't expecting that answer...
    I guess I want to do everything with a M:M, except the initial join.

    One client has many medications 1:M
    One medication has many details 1:M
    Then I want to
    Many Details have many medications. M:M
    Or for that matter:
    One medication has many Clients.. M:M

    I am just making things more difficult for myself.. I seem to see everything as a M:M

    Tracy

    ------
    Sorry I switched to my medication example...
    Last edited by Gwyar; 01-28-10 at 07:55.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Gwyar View Post
    ------
    Sorry I switched to my medication example...
    You'll have to point me to this - I thought we were talking about QA?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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