Results 1 to 5 of 5
  1. #1
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20

    Designing a database

    Greetings.



    For the past 10 years, I have been using a database that I created to keep track of patients. However, when I created this, I was using MS Access 2003 and more concerned about the front end (the form used to enter the data) rather than the data itself and the relationships, etc. I upgraded to Office 2016 and decided it was time to learn more about Access since I was designing all these queries and reports, most of which couldn't be used because there were actually no (i.e., zero) relationships among the tables I had. I've picked up the Access 2016 Bible and have been reading through this, and I've realized that what I had was a complete mess.

    So, I've started from scratch.

    Here's the basic need. I am a surgeon. For research purposes and for personal record keeping, I log all the patients I operate on. A patient and his/her characteristics would be the main piece of data. However, I may operate on the patient more than once and do different procedures on that person. There are right-sided and left-sided procedures.

    In the database I have attached, I have 6 tables: tbl_patients (the main record), tbl_surgeries (the surgery event), tbl_icd10 (the diganosis codes -- in the example there are four, but in the full table there will be about 1000 or more), tbl_CPT (the 5-digit codes for each procedure; the mature table will have about 1000 entry as well), tbl_category (simple diagnosis categories), tbl_docs (the list of doctors who send me patients).

    There are no actual people in the skeleton db. Here's the relationship. I figured the one-to-many would be appropriate since a patient could have many surgeries.

    Click image for larger version. 

Name:	relationship.jpg 
Views:	36 
Size:	147.3 KB 
ID:	26203

    Here is the tbl_patients in action.

    Click image for larger version. 

Name:	table.jpg 
Views:	37 
Size:	215.0 KB 
ID:	26204

    Anyway, I would be thankful to anyone who could give me a few pointers about my table normalization, the relationships, whether I should use "lookup wizards" for inputting data or whether I should allow multiple entries for fields (ICD10, CPT -- in reality, that's true for any patient/procedure). I can already tell that a lot of people do not recommend lookups on the table level...

    I tried to upload the database, but it exceeded the 500 kb limit.

    Here is the link to it on Google Drive.

    Thank you very much.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    multi value fields are OK if used in the right situation and there is no expectation of upsizing to sql server or a different backend. Problem is many developers use them unwisely. The issue is not the tickbox method of selecting items - if it was a control (like the multivalue listbox) it would not be an issue - it's the fact that it forms part of the data and relationships in a way the developer has limited control. It introduces additional complexities in query design and in itself it cannot be indexed which has potential performance issues. Providing those things don't matter i.e. the lookup data is relatively static and short (e.g. days of week) - then multi value fields are fine.

    lookup fields on the other hand just serve to confuse the issue the amount of time wasted by people thinking they are looking at text when it it's actually a number - and even naming the field as if it is text (as you have for Category and Referring Doctor). If you want to use a query to find patients referred by a particular referring doctor you will either need to know their ID or include the tblDocs as well if you only know their name - so what does it save you? - and for a dlookup you would need to know their id. I can see you are using the cascading tables, but the usual route would be to use forms and subforms which is where combos should be used and you have a much higher degree of control over changes to the data - with cascading tables you have none.

    With regards your relationships - they look OK to me but I would enforce referential integrity, remove spaces from names and be consistent with names - if the value in two tables are the same thing, call them the same (e.g. Referring Doctor is the same value as ReferringDocID, but displaying ReferringDoc, in time that will become confusing). The only departure I have (and this is my preference) is that with primary and foreign keys I use a PK or FK suffix rather than ID so I know which end of a relationship the field is. It also makes writing queries slightly simpler because I don't need to identify the table.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For the most part, I agree with Alex.

    However,
    I never use spaces, punctuation or special characters in object names.
    I never use Look Up FIELDS, Multi-value fields or Calculated fields in tables.
    I never begin object names with a number.

    Almost all (99.99%) of my tables use an autonumber as the PK field.
    You might read these:
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers


    Granted, I don't know your requirements, but I would think the table structures/relationships would be more like:
    Click image for larger version. 

Name:	kwelch.jpg 
Views:	32 
Size:	37.7 KB 
ID:	26205
    "tbl_surgeries" is a junction table:
    One patient can have many referring doctors and
    One doctor can refer many patients.
    This is a many-to-many relationship.


    The other 3 junction tables are what Access is doing behind the scenes when you use MFV fields in tables. But by using MFV fields, you can't easily search for specific ICD10 or CPT codes.

    BTW, "Name" is a reserved word in Access.... plus it is not very descriptive - name of what? And with people names, it is easier to join the first and last names than it is to split them.



    Good luck with your project.........
    and Welcome to the forum.......
    Attached Files Attached Files

  4. #4
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20
    Thank you for your responses and for setting up that relationship!

    Clearly I need to go back to the books and read a bit more. This relationship arrangement is nothing like what I would have thought it should be!

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your relationships are a reflection of your business rules. To get some practice you should try one of these tutorials. Each tutorial follows a process that leads you from a business description, to a properly designed database structure. They contain solutions as well.

    http://www.rogersaccesslibrary.com/T...lationship.zip
    http://www.rogersaccesslibrary.com/T...nformation.zip
    http://www.rogersaccesslibrary.com/T...getsDesign.zip

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help designing a database
    By docpanderson in forum Programming
    Replies: 4
    Last Post: 07-01-2014, 03:03 PM
  2. database designing
    By tommyried in forum Database Design
    Replies: 1
    Last Post: 02-27-2014, 12:41 PM
  3. Designing my database
    By ldypurple in forum Database Design
    Replies: 11
    Last Post: 03-06-2013, 10:58 PM
  4. Designing a database for a log
    By neo651 in forum Database Design
    Replies: 3
    Last Post: 10-31-2011, 11:45 AM
  5. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 AM

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