Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 51

Thread: Table design help needed...

  1. #1
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Table design help needed...

    I have a table called rom_rt that has the fields:
    Body_region_id
    Body_region
    Side
    Rom_plane
    Degree_change
    Normal_value

    I need to store range of motion values for each body part. For example, cervical (Body_region) would have extension, flexion, left lateral flexion, right lateral flexion, left rotation and right rotation (Rom_plane with left and right being the "Side" field). My question: Is the above table set up right or do I need to have a field for each value that I need? Looking more like this:
    Body_region_id
    Flexion
    Extension
    Left_lat_flex
    Right_lat_flex
    ... and so on

    I would prefer to use the first way, but can't figure out how to set that up on a form so that each value would popluate the correct record. I thought using a variable would be the only way this might work.
    Don

  2. #2
    Member
    Real Name
    Patrick PIttman
    Join Date
    Mar 2007
    Posts
    48

    Default Re: Table design help needed...

    You'll need a field to capture the values for extension, flexion, etc. for each body region/part/side. There's no other way around it.

    This can be done in one of two ways - create a single table with all the fields needed (preferred) or create seperate tables for left, right, etc. with fields applicable to each part. To do the second method, you'll need a key field that links all tables together - maybe a patient ID number? Then create a set of the various tables to create your form.

    Hope this helps....

  3. #3
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default Re: Table design help needed...

    This sounds like it could be a very complex situation. To know exactly how to set up the table(s) probably requires a full understanding of how the data will be used (entered, searched on, and viewed) and what type of reports are needed.

    My gut instinct is that you may need some 1:M tables but I can't be sure. I am fairly certain that lookup tables or lookup lists should be used to fill in the range of motion rather than creating a separate field for each one. You could either build different lookup tables/lists for each "group" of allowable motions (not every body part moves in the same ways) or build a lookup table that has a field which can be used as a filter to only show the allowable values.

    The advantage of a lookup table over a list is that the list has to be typed (or pasted) into the field rules of each field that it applies to and an update means finding and changing every appropriate field. With a lookup table, updates are easy because there is only one table to be updated. (Even if you have multiple lookup tables for different groups of "possible answers", most updates will only apply to one of them.)

    Hmmm, having said that, now I'm not sure. If each body part can have multiple ranges of motion (and most can!) then I have to go back to the "how the data will be used" issue. If you are only entering one range of motion for a body part because of ????, then a simple lookup should be adequate. However, if a body part has 3 possible ranges of motion and you may want to comment on 1, 2, or all 3, then the 1:M table may be the better solution. Also, something like this seems that it would include some kind of statement for the range of motion and that would have to be entered also. Of course, the 1:M links are more complicated to set up initially but, if it's done right, they will make data input and reporting easier. And the corrollary is that trying to force something that should be 1:M into a single table often results in major problems when you try to get (force) it to do what you want later.

    Hopefully someone else who understands more about your situation will read this and respond.

    Edit: Seems like PPittman may have the answer - no lookups just a bunch of separate fields.

  4. #4
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,027

    Default Re: Table design help needed...

    I agree with the one field for each part up to about 250 fields. Leave enough room in the fileds for the description. Old Timers rule of thumb is: "If possible make it all one table".

    If you are using this for an airplane, for instance, you could then have all the data on one form or report without a lot of extra work.

    Dave

  5. #5
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Thanks for pointing me in the right direction. I thought that might be what I need to do. Thanks,

    Don

  6. #6
    Member trackmanpete's Avatar
    Real Name
    Pete Schuder
    Join Date
    Sep 2005
    Location
    Mansfield, MA
    Posts
    592

    Default Re: Table design help needed...

    I am a bit confused by some of the answers given, if I am to understand the question posted initially correctly. In reading design books for databases, I was to understand that fewer fields per table was the goal, not more. Also, if you were to set up your project using a spreadsheet and many of the records are repetitive (same data), then you would want to move away from a flat file setup and gravitate towards a relational database. If the body movements are basically the same or can be placed in a lookup table to be used by the different body parts, then would not a relational setup be the way to go? Am I missing something with my thinking on this?

    Pete

  7. #7
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,643

    Default Re: Table design help needed...

    The old adage - Normalise until it hurts and denormalise until it works.

    There is no clear science in this, but perhaps an art of trial and error (aka experience)
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  8. #8
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,027

    Default Re: Table design help needed...

    I think his idea was to have a few fields and then put in what was necessary in those fields, but it makes better sense to have a field for each spot on the plane, boat, whatever it is, etc.

    if you are building a car:

    make 1 record for each type car and if possible make a field for each part. If you have too many fields by making one for each part, make another table and connect it 1-1, and so on.

    If you make another car(say a 2 door ltd instead of a 4dr bel air), that is simply another record.

    you can always make lookup tables that may not be connected to the car table, but you can put the specs there to fill in instead of typing each time.

    HTML Code:
    Also, if you were to set up your project using a spreadsheet and 
    many of the records are repetitive (same data), then you would want to 
    move away from a flat file setup and gravitate towards a relational 
    database.
    I believe a relational database is needed where you sell a car and have 2 or more trades. You need the sales table and you need a trades table where you need to connect to 2 or more records in a 1 to many.

    Those are my understandings to date. More discussion may be helpful and I am open to it.

    Dave Mason

    ps I have my #1 program running and my names and money were all in one table. It became necessary to move the numbers to another table due to more calculations happening. I was over 255 fields with the new stuff. It was a hassle, but it happened. now, I have names, money1-1, inventory1-1, trades(which is still inventory by alias)1-MANY, and a couple others in varying sets.

  9. #9
    Member
    Real Name
    Paul Main
    Join Date
    Feb 2005
    Location
    Bucks, UK
    Posts
    206

    Default Re: Table design help needed...

    I am in the same position of designing a database. I have made a separate table per datablock being entered per screen. This is because there is a lot of data and calculations per input screen. They will be linked on a common key in this case ID_Item. This application is going be used over a network so the question is. Is it best to use a set or just open and close each table when needed. I want to minimise the amount of data moving over the network.

    thanks

    Paul

  10. #10
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    One of the downsides to using a field for every choice (in this case, for all the range of motions for each joint... I have limited that to about 68 fields) is that I will usually perform a range of motion exam on one body part... which means that the 60 other fields will ususally be null values. Though, I'm not sure if this is a big downside. btw, I will be doing the same (creating the same table) for strength tests as I am for range of motion. It sounds like I will needs to use separate fields for each range of motion and each joint and each side of the body... unless someone has a better solution.

  11. #11
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,643

    Default Re: Table design help needed...

    Don

    I've seen some other products that do this job (when visiting my chiropractor) and I think you would be heading down the wrong road with the single record design.

    You've tackled a comprehensive system for a first time design and building exercise.

    Have you looked at other products to see what they do?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default Re: Table design help needed...

    I stand by my original statement: "To know exactly how to set up the table(s) probably requires a full understanding of how the data will be used (entered, searched on, and viewed) and what type of reports are needed."

    That goes along with Al's statements which are actually more useful:
    The old adage - Normalise until it hurts and denormalise until it works.

    There is no clear science in this, but perhaps an art of trial and error (aka experience)
    When I read "I will usually perform a range of motion exam on one body part", I go back to thinking that some 1:M tables in a set would probably be more appropriate than having the majority of fields in the table being left empty.

    When I read "I have made a separate table per datablock being entered per screen", my first reaction was, OMG - how many thousands of tables with exactly the same fields will this create? Hopefully I misunderstood the situation - not sure what a "datablock" is. Even if there are dozens of calculations, using one table should not make them any slower or faster. If you are duplicating the same fields in multiple tables, you should probably read up on database normalization.

    On the other hand, since I don't know the details on either of these (and don't have the time to figure it out even if the details were provided), I can't be sure of either one. My recommendation would have to be to read up on normalization if you aren't familiar with it (use a search engine and search on "database normalization") then follow Al's advice and "Normalise until it hurts and denormalise until it works." His recommendation to look at other products that are doing something similar is also excellent advice.

    Hint - you don't have to become an expert on normalization. Just read up on it a bit, try to understand a couple examples for levels 1, 2, and 3. Read about levels 4 and 5 if you want to but don't worry a lot about them.

    Although it may seem painful and useless to new database developers to read about normalization, I've seen many databases created by new developers and I wish some of them had understood those basic concepts before they started. Especially when working on a complex situation. Sometimes it would have taken more time to fix the mess they created than it would have to start over from scratch. I don't know of anyone who has read about normalization and then felt they had wasted their time. In fact, the opposite is true. Everyone I've heard from felt that it was very helpful. One hour spent learning about normalization will probably save you dozens of hours of development time later on.

  13. #13
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Table design help needed...

    "Database Normalization & Design Concepts" is one of the presentations at the ATEC conference in a couple of weeks.

  14. #14
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,027

    Default Re: Table design help needed...

    Don,

    Could you tell us what you are doing that requires the app to be built?

    Some cases require flat file and some require one to many or other.

    Give us a scenario, what you are building, maybe a scenarion of one or two fields and the possibilties for that field. We all seem to be reading your post differently.

    I like to sit down on paper and draw out what I need and then put it in a database form so I can figure out what I need to change. Was taught that way.

    The design of a database is the step that makes the rest anywhere from easy to impossible.

    Dave Mason

  15. #15
    Member
    Real Name
    Paul Main
    Join Date
    Feb 2005
    Location
    Bucks, UK
    Posts
    206

    Default Re: Table design help needed...

    Cal,

    Let me clarify. I can have one record of 1800 bytes, or I can divide this up and have a separate table per input screen, all linked by a common field "ID_Item". The tables will have variable numbers of fields. I should pointout that sometimes there will be no data for a given input form.

    My main concern is to ensure that it works well over a network. I suppose the less data being retrieved the better. Which brings me back to my original question about hhow alpha 5 works over a network. Does it move just the record data or what, when you access a record and place the data in a screen.

    Thanks

  16. #16
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Quote Originally Posted by Al Buchholz View Post
    Don

    I've seen some other products that do this job (when visiting my chiropractor) and I think you would be heading down the wrong road with the single record design.

    You've tackled a comprehensive system for a first time design and building exercise.

    Have you looked at other products to see what they do?
    Al,

    You're probably right about the single record design.

    I've had plenty of people (programmers) tell me I probably couldn't do this on my own. Nothing ventured nothing gained.

    The reason I decided to "play" around with this project is because there are no good programs out there (as far as I'm concerned) and I didn't have the resources to pay someone to do it (estimates over $100,000). Although I only plan on building the notes part of the program, at least for now.

  17. #17
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Quote Originally Posted by DaveM View Post
    Don,

    Could you tell us what you are doing that requires the app to be built?

    Some cases require flat file and some require one to many or other.

    Give us a scenario, what you are building, maybe a scenarion of one or two fields and the possibilties for that field. We all seem to be reading your post differently.

    I like to sit down on paper and draw out what I need and then put it in a database form so I can figure out what I need to change. Was taught that way.

    The design of a database is the step that makes the rest anywhere from easy to impossible.

    Dave Mason
    Dave,
    If you haven't guessed already I am a chiropractor (who happens to like computers) that feels I can do a better job in developing a program than what is currently offered. I have a good friend who is a programmer (not on Alpha). We have worked for several months (part time & weekends) first developing the table structure and design and now I am working on the "meat and potatoes" of the notes part of the program. I think it is coming along very well. I do not want to develop a full practice management program on my own... but would hope to hire a full time Alpha programmer to help with this eventually.


    My first message had three attachments which I thought would be enogh to explain what I am trying to do. If not, I'll try to explain a sample scenario:

    While working on a patient I discover that the main complaint is the cervical region. I want to only perform a detailed exam on that part of the body. So I would not want to do a range of motion test of the lower back or shoulder etc. So I would choose that region do do my ROM exam in which I will input cervical flexio, cervical extension, cervical left lateral flexion, cervical right lateral flexion, cervical left rotation and cervical right rotation. I want those numbers stored to be able to retrieve in a report or even duplicate to another exam for a future date. I also want to be able to make calculations based on two exams to show change (hopefully improvement). Keep in mind that this is only one part of my exam. I will also be doing strength tests, orthopedic tests etc. for the same part of the body.

    I hope that make more sense. Thanks for reading.

    Don

  18. #18
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,027

    Default Re: Table design help needed...

    Yes it does. I thought you were designing aircraft?

    You need on table for patient to keep it all in perspective. You then need a table on the(or more exams) with lookups.

    Your exam table could be connected in a set by patient number. The exam tables next main field should be a lookup for the region to be examined(you would have a table just for this) and each of the other fields could also have lookups for your range of motion, strength, etc as you need, but would key on the part of the body you chose with the first lookup for a direction for their own lookup. You can have a lookup on a table that only sees what you filter for.

    YOU CAN DO THIS!

    Now that it is clearer, it will be a bit of work, but very doable. You do need a set though so you can see 2 different exams in a report. You can break the body parts into different tables if you want to limit the fields to a certain number.


    FYI I had a set running over our lan where the master table had the max number of fields used. I had to break it into 2 tables due to new fields added. There was no difference in speed with 2 tables. The set just had one more table.

    Dave

  19. #19
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Dave,
    Thanks again for your help. I am sitting down now and putting it on paper. That does make it easier to conceptualize.

    If I understand correctly, I'll have a set that contains a patient table, an exam reference table, as well as many body part tables which contains the specific tests (rom, orthopedic, neurologic etc.) for that body region. That would replace the current structure I have now in which each table is based on the type of test (ie. all range of motion on one table) vs. each body part containing all the tests.

    Did I understand correctly?


    Don

  20. #20
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Table design help needed...

    Paul,
    This thread is Don's posting and it can get really confusing when someone elses issue/needs are inserted. I don't want to be rude, but it would be best if you began a new thread with your need/issue so it can be focused upon and it not confusingly intermingle with Don's deal. Thanks.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  21. #21
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Table design help needed...

    Don,
    Dave is correct, you can do this. Except I think your structure needs to be like this:

    PATIENT -1:many- BODYREGION - 1:many -EXAM

    Why? Having been a chiropractic patient, I am one person (Patient). I had several areas of my body that were attended to (many-regions) and these were attended to on many different exam visits (manY-Exam) where the exam yielded the results of the exam (flexion, extension). The exam would have a date, the exam type (rom, neuro,etc) and the historic exam actions (ex: flexion) which could be filtered by that type and action to show the exams over time. Or with some cool scripting building arrays for each motion, and using Xdialog, you could view several motions exam values over time.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  22. #22
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,649

    Default Re: Table design help needed...

    Not to disagree with Dave or Mike, but I would suggest something like this:

    PATIENT -1:many- EXAM - 1:many - BODYREGION

    Because you each patient may have several exams, and you probably want to see the results of each particular exam. BUT also you may want:

    PATIENT -1:many- BODYREGION 1:1 Exam

    Because you probably want to look at the patient's body region results in one group (linked to the particular exam in order to put it into context.

  23. #23
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,643

    Default Re: Table design help needed...

    Don

    It may (and usually) takes multiple sets to describe the relationships and how they should be used for a particular task.

    The overhead caused by a new set is limited to a new index being created and maintained (unless it's already there for another purpose). The advantage is the flexiblilty of looking at the data efficiently from multiple viewpoints.

    So one form may need one set, another form may need another set, and a report may need it's own set. It is better to have multiple efficient sets, then to have one set trying to be all things to all purposes...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  24. #24
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Table design help needed...

    I think all three (Dave,Mike and Peter)are correct,

    cause I think of the set design as being one 'layer' removed from the table structure ie the the number of and the purpose of the tables. As long as you get the tables to hold the data in the correct relation to the circumstances then you can create sets to your hearts content to view/manipulate the data as you see fit.

    Edit: Basically what Al said except he said it better.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  25. #25
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Thanks to everyone for their comments. I'm one step higher in a long staircase to the top.

  26. #26
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Table design help needed...

    Don,
    See if attached example might interest you. Good luck.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  27. #27
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Mike,
    VERY COOL! THANK YOU! I will study this more thoroughly... I am sure it will save me many many hours.
    Don

  28. #28
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Table design help needed...

    Don,
    You are welcome. I started out with action scripting but slipped into Xbasic for MY expediency, and always for my education. Please don't hesitate to ask questions. I might know the answers...:D
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  29. #29
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Table design help needed...

    Don, I notice a fix is needed. I changed the button name (see pic) and that button needs the following at the TOP of the OnPush event script:
    Code:
    	'Get 'Value' property of 'Client_id' in Form 'F_Exams' .
    	DIM GLOBAL vclientID AS c
    	vclientID = parentform:Client_id.value
    When I was building/testing this was established. If you go in and immediately press that button.... error because the variable was not yet dimmed.

    Good luck
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  30. #30
    Member
    Real Name
    Don Lavigne
    Join Date
    Jan 2007
    Location
    New Jersey
    Posts
    223

    Default Re: Table design help needed...

    Mike,
    Thank again... by studying this example you have easily saved this "newbie" weeks of work. Not just the table and set design, but also some of the code and action scripting you have used in buttons. I am sure I'll have more questions as I dig further.

    Don

Similar Threads

  1. More Methods needed, alpha design team please read
    By David Priest in forum Alpha Five Version 6
    Replies: 7
    Last Post: 03-11-2005, 05:33 AM
  2. web design help needed, I think
    By Wanda Tucker in forum Alpha Five Version 5
    Replies: 1
    Last Post: 04-12-2004, 07:48 PM
  3. Backorder design advice needed
    By Pat Bremkamp in forum Alpha Five Version 5
    Replies: 4
    Last Post: 02-22-2004, 09:56 PM
  4. Design Help Needed
    By Tom Patten in forum Alpha Five Version 4
    Replies: 6
    Last Post: 02-20-2001, 09:21 AM
  5. design suggestion needed for foreign/US zip
    By Steve Pick in forum Alpha Five Version 4
    Replies: 5
    Last Post: 04-23-2000, 07:57 PM

Bookmarks

Posting Permissions

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