Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15

    Junction tables and relationships

    I posted a question yesterday using the example that I wanted to sort specific tasks by farmer - I've since been informed that I don't need to be so vague as to what I'm trying to accomplish - sorry Ajax and Micron!



    So what I'm really trying to do is create a catalog of the specific orthopedic problems that the orthopedic surgeons in our region (and neighbouring regions and cities) will and won't do. For example, we have many surgeons who will do knees (Primary Problem), however some won't accept knee referrals with arthritis, others won't do ligament tears, some require x-rays with all referrals, whereas others will accept with an MRI only as long as there isn't any signs of arthritis on the MRI... It gets even more complicated because some of these surgeons will also treat ankles, hips, shoulders, elbows, etc, and each of those primary problems have many "sub-problems" or "secondary problems" which come with their own nuances.

    On top of that, we also have Triage Assessment Clinics who specialize in just Hip and Knee arthritis, or just shoulders, or just feet, etc. They also accept orthopedic referrals, and they too have their own requirements.

    My end goal is to be able to create queries that pull up which surgeons do the specific sub-problem, and by city or region (and if I'm not asking too much, I would love it if I could also create a relationship between the surgeon, sub-problem, and that surgeon's specific requirements for that sub-problem)
    Example: "Knee meniscal tears with arthritis"
    - Dr. A. Smith; requirements - x-ray done within last 6 months
    - Dr. B. Jones; requirements - x-ray done within last 9 months
    - Dr. C. Martin; requirements - unknown

    I think I understand what Micron suggested yesterday about creating a junction table, but wondering if it's wise to connect more than 2 tables to the junction? In my picture below, you can see I haven't connected the Triage Assessment Clinic table to the junction table, primary or secondary problems yet. Perhaps the Orthos and Triage Clinics should be in one table? Or the Triage Assessment Clinics should get their own junction table with the secondary problems?

    And right now I have imaging requirements (i.e. xrays, MRIs, ultrasounds, etc) as a combo box under Secondary Problems... I'm not sure if that's ideal or if it should also be it's own table.

    Any suggestions as to how I have my relationships arranged would be most appreciated!

    Click image for larger version. 

Name:	Junction relationship.PNG 
Views:	50 
Size:	32.4 KB 
ID:	40950

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    #1 - ditch the multi value fields.
    #1 - ditch the attachment fields (yes they are both #1)
    #2 - you have provided what seems like a realistic example of what the db is supposed to support and I wonder why you felt a need to be secretive about it. Doesn't matter, I guess, but at least it's something we can start to relate to. On the surface, it appears to be a very complicated process that you want to support and I think this will take some time for anyone to begin making focused suggestions. More than likely, after reviewing your latest post a few times you're likely to get more questions than answers initially as the process starts to be understood. I know that will be the case for me. Here's my initial thoughts based on what you posted:

    ortho problems
    - are they "knees" or issues with the knee (osteoarthritis, torn meniscus, fracture, bursitis, etc.) Is body part/area more applicable (knee, shoulder, hip)?
    - and the db is only for ortho issues?
    are surgeons and clinics not the same entity (a provider or something else?)
    you might want supporting tables for city, region, state etc. data. Should figure this out in advance. Upside is there's more control over accurate inputs.
    you seem to have disqualifiers (e.g. no arthritis) and qualifiers (e.g. must have x-ray). These are probably best as 2 separate tables.
    - can/should qualifiers/disqualifiers be grouped by provider type?
    your current schema requires that there is a secondary problem in order to have records in your junction. If no secondary, there can be no primary. I wouldn't worry about that now as your schema is likely to change several times as you think this through.

    Based on your image, I'd guess that this is for Canada - maybe even Ontario.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    duplicate post
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Quote Originally Posted by Micron View Post
    duplicate post
    Yes, sorry Micron, I don't participate in many forums, or often. Should I delete my original post?

  5. #5
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Quote Originally Posted by Micron View Post
    you have provided what seems like a realistic example of what the db is supposed to support and I wonder why you felt a need to be secretive about it. Doesn't matter, I guess, but at least it's something we can start to relate to. On the surface, it appears to be a very complicated process that you want to support and I think this will take some time for anyone to begin making focused suggestions. More than likely, after reviewing your latest post a few times you're likely to get more questions than answers initially as the process starts to be understood.
    Yes, I know I didn't need to be so secretive, sorry for the confusion. I had my reasons, but in retrospect they were silly. Anyways, thank you for taking your time to examine what I have started and for answering some of my questions, despite my silliness

    - As for Ortho problems, you are correct, the primary problem is the body part, so knee, hip, shoulder, spine, etc. The secondary problem is the actual issue with the body part, so OA, torn meniscus, etc.
    - The database is only for orthopedic issues, however sometimes a referral will have a non-orthopedic issue, which we add to our excel spreadsheet so we know where the referral should really go (oncology, plastic surgeon, sports med, etc)
    - The surgeons and triage assessment clinics are different entities, but might deal with the same body parts, secondary problems, and be in the same city or region.

    I am going to re-draw my relationships on paper, make a list of possible queries I might want to use to help me zero in on what tables I need/don't need, and see what I come up with.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Duplicate post referred to the fact that I ended up posting the same thing twice by accident. What you should do is mark the other one as solved, I think.

    What you shouldn't do IMHO is quote a long answer like that one. It causes unnecessary scrolling. Click reply, not reply with quote?

    Will review your reply shortly. Breakky and coffee are calling for me.
    Did I guess the locale correctly?

  7. #7
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Done and done, marked the other as solved. Sorry for causing so much duplication and confusion, definitely not my intention.

    And yes, you guessed the locale correctly. Did LHIN give it away?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    tblAnatomy
    AnatomyID; autonumber
    Anatomy; text - e.g. knee
    AnatDesc; text - e.g. all knee components, e.g. tibia, fibula, patella...

    tblCondition
    CondtionID; autonumber
    Condition; text - e.g. TM
    CondDesc; text - e.g. torn meniscus

    So far, any anatomical part can be added without affecting table design. Same for conditions. How do we stipulate that the shoulder cannot have a TM? With a junction table:

    tblAnatCond
    AnatCondID; autonumber
    AnatomyID_fk; long - ID value from tblAnatomy
    ConditionID_fk; long - ID value from tblCondition
    fields 2 and 3 would be a composite index

    Now you can match any body location with only the conditions that apply.

    The LHIN and WSIB gave it away. Did you check out About Me in my profile?

    EDIT - momma was standing there with her coat on, waiting for me so I had to cut it short. I want to add that the terms I used were my own thoughts about how to compartmentalize two entities. Figuring out what the entities are is of prime importance, so if you can make sense of my terms it should help to understand the process. That is more important than what you call them. At the risk of repeating myself, normalization is the most important aspect of design.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Hello again,
    I've been working on this on and off when I have time, and now I have 3 junction tables, 1 of which I have made into a composite index. I haven't indexed the other 2 as I wanted to get some more feedback before I proceeded. Sooo... can I have this many many-to-many relationships/junction tables attached to as I do in the picture below?

    I still haven't added in a crucial piece, which is the requirements (ie. must have x-ray, within 6 months) - ultimately I've sketched it out as yet another junction table joining "Requirements" with "Timeframe". But I'm also trying to figure out if I should come at it from a qualifers/disqualifiers angle as Micron suggested above (i.e. Has OA, must have x-ray) - so perhaps it would be a "Qualifier" table and "Disqualifer" table both joined to tbl_ReferralReason?

    FYI: Referral Reason would be something like Meniscal Tear, OA, Bunion, etc.

    Click image for larger version. 

Name:	Junctions.PNG 
Views:	34 
Size:	38.2 KB 
ID:	41099

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's going to take some time to digest, part of which will be to review everything you've posted thus far.
    If the 5 tables on the left are lookup tables, I would not include them in the relationship as I don't see the point if it's used properly. On that note, I have seen opinions written that say you need to do this for referential integrity If you think about it, if the autonumber id represents "appel" at the start and you have stored that number in several places, and go 'whoops, wrong spelling. You go back and change that to "apple" in the lookup table but the id doesn't change anywhere. All of a sudden you see apple in your form, not appel. So what's all that noise about referential integrity (and no, they weren't talking about cascade updates or deletes.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Yes, I'm having a hard time digesting it all too, lol. Thank you for the pointers about the look-up tables. I will do some more tinkering.

  12. #12
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    In order to design a database correctly you must first fully expose what it should do, for example:
    - there is one or more clinics that:
    who may have one or more specializations
    which may have one or more sub-specializations
    - where specialists practice who:
    specialists can work in multiple clinics
    the specilist must have a registry
    the specialist can have one or more contacts (landline, mobile phone, fax, email, ...)
    may have one or more specializations
    may have one or more sub-specializations
    - the sub-specializations are linked to the specialization
    - a specialist visits a patient, of whom he must:
    create a registry
    create contacts
    - following the visit he decides to do:
    one or more checks at one or more specialized centers
    ...
    - ...
    then for each table it is carefully decided which fields are necessary and if in a field it is thought that more values ​​could be inserted over time then it is necessary to create a separate table to relate to that mother.
    By not doing so, surely, you will be forced several times to rework the project with unnecessary waste of time.

    I apologize in advance for not my correct English not knowing it well and therefore using google translator.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You need to know what the primary entity is that the db is supposed to support. Is that people who need a referral for an affliction, or is it specialist based? I'm thinking it's the former and your layout looks like it's the latter. I get that impression because it seems to flow from left to right.

    Along the patient based idea, let's say knee is anatomy pk 1. It has 2 potential problems you'll deal with; tm and oa; it looks like refreason is supposed to handle that. Thus there are 2 anatrefreason pk id's for knee because there will be 2 fk's from anatomy. Which one of them will you use in another table? You are using neither. I doubt it is ever correct to create a junction table to unite 2 entities and then not make use of whatever it is that makes the relationship unique. The junction pk alone cannot do that because as data, it is meaningless, so not only are you not using the pk from your junction, there is no way to distinguish between one or the other. You have, I think, 3 situations where you're joining many to many and not using the table pk values at all. That makes them sort of useless, no?

    Rather than go on about what is there so far I think the answer regarding exactly what is being supported needs to be nailed down for those evaluating your schema. It seems to be anatomy based, not patient and not specialist. To me that means someone shows up at the door with an issue an you need to figure out where to send them. If that is correct, start thinking about it from that perspective. Whether or not you care to record WHO you sent WHERE I don't know, but at the moment you have nothing for the person. Along that line, something like

    tblAnatomy- AnatID; Anatomy; Description (e.g. 1; knee; text)
    tblAnatomySub- AnatSubID; AnatFK (e.g. 5; 2 [where 2 represents shoulder from anatomy])
    I think you mentioned there can be subs of anatomical parts/regions, so pk5 for shoulder would show up here as many times as there are sub areas of it.

    tblAffliction- afflictionID; term; description
    tblAnatAffliction- join the affliction id's to the areas that can have them
    and so on. I'm going to stop there as I might be way of base with what the primary purpose of the db is.

    Keep in mind that it is not always necessary to have junctions when 'many' is involved. That's why it's so important to understand normalization concepts and how to apply them to your situation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Back with a two-part question - I was going through some of the other threads but can't find an answer. My coworker who is more advanced with Access than I am asked me "where will most of the data be housed" or "which table will retain most of the information?"... I thought I understood her question, but now that I am home, I'm very confused, as that still sounds more like an excel spreadsheet rather than a relational database.

    1) is it not more ideal to have collections of information in multiple tables (i.e. Orthopedic Surgeon, Anatomy, Referral Reason, etc) vs combining some into a larger table? Especially when that info is complex?

    2) Or is what my co-worker said true, and I should have a table where I have "most data housed together?" I'm still having a hard time picturing how that would look, given that I'm trying to piece together an orthopedic problem (e.g. achilles tear) + the anatomy it belongs to (e.g. ankle) + the surgeons who do that problem (e.g. Dr. Smith, Dr. Jones) + the individual surgeons' specific requirements for accepting a referral for that problem (e.g. needs x-ray done within last 6 months, won't accept if arthritis present, etc). Perhaps if my Orthopedic Surgeon table has a column for each anatomy part as a yes/no field?

    Thanks,
    Em

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not to take anything away from your colleague but just because someone knows more than you about something doesn't make them an authority. I have seen people post in this forum with questions about stuff they are learning at college level and sometimes it amazes me what they're being taught by instructors who ought to know better.

    I imagine your question could be interpreted in more than one way. To me, it suggests spreadsheet or flat file thinking because where most of the data might be housed has no bearing on a db's schema - or at least it shouldn't. Putting attributes (fields) of entities (tables) that are not related to the entity is a classic mistake and it's as bad as having multiple fields for the same attribute. Either of these mistakes tends to result in tables that are "too wide", making record retrieval a problem.

    Hopefully someone else will provide their point of view, either agreeing or not, just so you have more than my one opinion to go on. Maybe you need to seek clarification on the meaning of that statement, and provide some sort of example that you can relate to.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Junction tables, subforms?
    By thegrimmerdiscovery in forum Access
    Replies: 2
    Last Post: 09-20-2019, 01:09 AM
  2. Creating junction table - relationships
    By jaryszek in forum Access
    Replies: 2
    Last Post: 07-13-2018, 02:35 AM
  3. Replies: 1
    Last Post: 01-27-2016, 04:30 PM
  4. Help with Junction Tables
    By sbart in forum Access
    Replies: 5
    Last Post: 02-17-2014, 12:53 PM
  5. Replies: 10
    Last Post: 07-12-2011, 11:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums