Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Unanswered: Table and Relationship question

    Hello, I'm developing a DB that will be used for operational use at a med clinic so it is heavily form dependent for non-technical users. It tracks patients as they cycle through a clinic (inputting data along the way).

    Everything will need to be unique per patient appointment.

    With one appointment there are well over 100 required fields.... I'm trying to split these into multiple tables even though they will all need to be updated, and modified based on the same appointment.

    The thing that worries me is there are two memo fields. One of them will typically be multiple pages. I'm worried that having all of these records AND the two memo fields in the same table, while having 5-10 users modifying and editing the single table via multiple queries and forms will severely slow this DB down.

    For example in one form (the one that is used to book apt) the user enters data and then copies/pastes a large memo field. I want to be able to append the data/information into two tables simultaniously but still have the same apoitment ID for both records.

    I know that 1:1 relationships are dumb, but I can't think of a good way to separate out the memo or other fields as they are unique per apt. When I have a 1:1 relationship, I can't figure out how to append to both tables (for example Apt_ID is PK in both tables and has a 1:1 relationship *?Is this right/possible?*, and I want to add a Pt_Name and Vital_Signs to two separate tables)

    I could really use some help...

    It's been a long while since I've tried to build a DB, and nothing I've done has been this complex...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What database server are you using? SQL Server has the varchar(max) datatype which intelligently decides whether to store strings inline or in a separate data structure.

    Also, how many rows are you expecting?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    I'm building it in MS Access unfortunately... I know minimal SQL/VBA but do use it for some of the more complex queries.. I was hoping to build it using PHP, or completely in SQL/VBA

    just takes google/forums and patience... couldn't find much on this subject though

    I've attached a picture of the relationships... If I can't seperate/update with 1 append query than all of the VS table/Memo table/ Patient Table will have to be combined into the appointment table

  4. #4
    Join Date
    Nov 2010
    Posts
    9

    Mansyse second,

    relationships
    Attached Thumbnails Attached Thumbnails Relationship.bmp  

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This sort of issue is heavily dependent on the database platform you are using, and my MS Access is a little rusty. I am going to move your thread to the MS Access forum, where there are folks with more experience in this matter.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BennyC View Post
    I know that 1:1 relationships are dumb
    Then you know more than me; some modelling techniques require them. Care to elaborate.

    You absolutely do not repeat columns across the tables, except for the PK.

    My only concern is that 100 plus columns suggests a database not properly normalised.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2010
    Posts
    9
    Seems that most of what I read recommends that if you would have a 1:1 relationship it's better to just put the records in the same table.

    I would like to normalize the data but am running into difficulties since 1 Append query would have to span multiple tables. I still can't figure out how to do this.

    With one append query I will be adding about 20-40 records from a single form, that will be unique per Appointment.

    The main issue is how do I have one entry method for so many records, that all should be going into different tables.

Tags for this Thread

Posting Permissions

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