Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Table design help needed...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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
    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....

    Comment


      #3
      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.

      Comment


        #4
        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
        Dave Mason
        [email protected]
        Skype is dave.mason46

        Comment


          #5
          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

          Comment


            #6
            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
            "Ollie, remember how dumb I used to be? Well, I'm much better now."

            Pete

            Comment


              #7
              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.
              "Make it as simple as possible, but not simpler."
              Albert Einstein

              http://www.iadn.com/images/media/iadn_member.png

              Comment


                #8
                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.
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  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

                  Comment


                    #10
                    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.

                    Comment


                      #11
                      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.
                      "Make it as simple as possible, but not simpler."
                      Albert Einstein

                      http://www.iadn.com/images/media/iadn_member.png

                      Comment


                        #12
                        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.

                        Comment


                          #13
                          Re: Table design help needed...

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

                          Comment


                            #14
                            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
                            Dave Mason
                            [email protected]
                            Skype is dave.mason46

                            Comment


                              #15
                              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

                              Comment

                              Working...
                              X