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

Still struggling with normalization

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

    Still struggling with normalization

    I have a document form with hundreds of fields which I have placed in a rich text field on a report(thats so I could copy the document into the report). This document form was used in WordPerfect with fields that were filled in by a merge document. What I want to do is use a database to fill in the form. This will make the process faster and keep the related documents in a way I can find them without digging through paper files. It will facilitate communication and expedite making letters ,labels and other documents. Hopefully I can extend the database to handle time billing,dheck writing, keep statistics and be an efficient e-mail collector or sender. In short, automate the office.

    The problem I have with the database design, being a complete newbie to the subject, is understanding how different tables can be used to fill in fields in a document. In general, I can�t see the relational model and am stuck on the flat sort of straight line thinking.

    Say I have a table called CHILDRENtbl. In the table are fields such as:
    Client_Id, Child_Id, ChildName,ChildSSN,Child DOB, Child Age, ChildGender ChildHeSheThey, ChildHimHerThem ChildHisHerTheir.
    Ok. So now I have a table that will fill in all these attributes for every client I have concerning their children. Identifiable by the CLIENT ID, if the client has more than one child and individually by the CHILD ID, I suppose. This design of the table avoids the problem of limiting the database by having an assumed limit on Children with fields such as Child1,child2,child3. By having a table I know where the children are and can append them as I need.

    Now how do I get the children out of the table to fill in a form that needs to Identify each child separately so that fields where the different children are placed can be filled in on the document form.

    I mean from what I have read of normalization the good advice is to get rid of fields like child1, child2, child3 and child4 etc. reduce this kind of plurality down to just CHILD. However the problem I am having converting a merge document is distinguishing the first child or the second child from the rest of the list in a way that can allow them each to be placed in a specific location in the document . Is there some way to do this without having fields named child1, or first child or something that fits them in the document? If they aren�t distinguished they will all end up the same name. I mean if all you will do with the database is make a list of children then I can see how this works but if you are trying to select out a specific entity and place it in a form document in a specific location. I don�t see how you can get around the plurality convention of the field name. Maybe the trick falls with Child_id or the variables which I have no clue as to how they can be used to do this in the form document. Anyway, I am sure my ignorance about doing all this is quiet a spectacle but I shall persevere the show to gain insight.
    Many thanks for any observations
    Sincerely, George R. Kenney.

    #2
    Re: Still struggling with normalization

    Hi George

    not sure where to start so i will just jump in

    it seems to me thet you have a large project
    and know that it aint workin now - but you arent
    really sure how to go about fixing it.

    you are right that you really need a database
    and that you really need to set it up right - ie normalize it
    before you start

    on what you can expect here on the message board:

    everyone is happy to help with specific questions
    but really dont have a great amount of time to assist you
    with your project to the extent that you seem to need

    so i am going to suggest that you decide on the course that you
    are going to take
    1) attempt to wrap a new database around some really
    convoluted data in a mismatch of formats
    2) create a database that will be in a form that really represents the data

    i really dont think you can serve 2 masters here - one will
    compromise the other

    i ( and others here) will be happy to provide some help on specific questions
    but you really need to state your objective and then follow the course that
    you choose.


    i am of the camp that you really need to create your database with the
    objective of representing the data and at some point converting what
    you can salvage from your legacy data

    to that end i suggest that you might read a bit more on data normalization
    try here:http://www.datamodel.org/NormalizationRules.html
    or here:http://dev.mysql.com/tech-resources/...alization.html

    on PAPER:

    then i would take some of the old data and create a set of tables to
    represent the data in as normal a form as possible(at least 3rd)

    got a specific question???? then ask it here with enough data to allow
    the members to make logical answers -

    then create 2 or 3 of your main tables ( those that are related)
    then add a small subset of data to the tables and then you can create
    reports , forms , views, etc - and test out your design



    i think that you are worried how a one-to-many relationship
    (client and his/her children) is represented in a report

    worry not - that is what databases are all about

    you might consider a simple invoice
    this is a one to many relationship between to tables
    (the invoice header table - name, address etc ) and the
    line items table (item_id , item description, taxable , price etc)
    the fact that you have a one-to-many relationship allows you
    to print an invoice with any number of line items on it
    and each will have its own set of data


    Now how do I get the children out of the table to fill in a form that needs to Identify each child separately so that fields where the different children are placed can be filled in on the document form.
    your question above seems to be asking how to fill out a form for a client who
    may have mulptiple children and (???how this may fit in your old document)
    i say scrap the old document and then create a new one based on a
    one-to-many relationship between the client and dependent

    you really need to just get a bit experience doing some of this
    and this will be fairly easy


    layout your design on paper, create a few tables, add some data
    then you can create reports.

    dont worry about making any mistakes - YOU WILL - trust the word of
    experience
    besides this is an iterative process - it takes a couple of tries at least

    good luck - sorry i couldnt be of more help
    regards

    martin
    www.jollygreenthumb.com

    Comment


      #3
      Re: Still struggling with normalization

      From what I read, I think you are planning to still use Word Perfect. WP is one of the best word processors for merging data. Do as you suggest, create a CLIENT table and CHILD table, linked on CLIEINT_ID. Export both as delimited files (tab, comma or other).

      Read this document on how to convert those delimited files to WP data files: http://ist.uwaterloo.ca/cs/sew/cours...ge/wpmerge.pdf

      WP's own documentation on merging is awful, but...

      Read up on merging, especially using a secondary data file. Secondary means you start off merging with one file (CLIENT) and at some point run a loop using a secondary data file (CHILDREN), looping through each one with the same CLIENT_ID to list all children.

      YOu can automate all of it, but its a bear. Part of the problem is WP can't merge directly with the delimited files; they have to go through a process where they are imported (using Insert / Spreadsheet) and then saved as a DAT file, then merged with your merge document.

      The above is based on what I know about WP12. WP5.1DOS is still the best mailmerge program ever written, but it doesn't fit in today's world.
      Steve Wood
      See my profile on IADN

      Comment


        #4
        Re: Still struggling with normalization

        Martin
        Thank you so much for your advice and consideration I will try my best to follow it. I really appreciate the pointers and the web sites you sent me to. The first one I hadn�t seen and it is very clear and mind you I have been looking all over the web.
        Thanks

        Comment


          #5
          Re: Still struggling with normalization

          Steve
          Thanks for your consideration and reply. You are right about the documentation in WordPerfect. In fact one of the reasons I bought
          alpha is they actually have a better explanation of how to use worperfect�s database utility than wordperfect has. I called wordperfect once and the man there didn�t know it even existed. It took me many frustrated hours to find out that all the numeric fields wouldn�t go and had to be translated into character fields which added another layer of fields in the database date fields also. whoever heard of such a thing. client_id is a number right. that one didn�t work. Mind you after I started to understand the rules I can see that wordperfect really has everything you need to do layers of merges and the data can come from just about anywhere. Unfortunately the firm wants to move to Word because all the clients have it and WordPerfect started giving trouble with the new multifunctional printer copier. The TI time on that costs too much. So my idea is to try to create the forms in Alpha this may bloat the database I don�t know but the firm only creates 400 or 500 clients a year. It would still be necessary to be able to merge into a wordprocessor like word or wordperfect just to give flexibility to the thing. The only problem I perceive with using Alpha is the ability to create a table of contents yet I have seen some solutions with respect to that in the newsletters.

          Thanks again

          Comment


            #6
            Re: Still struggling with normalization

            One of the hardest things in the world must be to outgrow the word processor. I have gone into Dealerships in Florida where all the data was in word perfect or word. Usually one or two people could master the maze of files to get the information. I even saw people trying to do contracts with math compiled in word perfect. All was good until the audit came.

            Moving these people( at the owner's insistence ) was at best difficult. Once they got used to a new way, the processor died except for a few letters which could also have been done in a database.

            Normalizing for your needs(at the start) should not be hard. A table with the primary complete and a child with enough fields for a numbering sequence and needed data. You simply make a browse in your main form of the child data and number each child as needed. say 1-3 or however many for each parent record.

            like:

            master
            id char 8
            name char 40
            etc

            child
            id char 8
            ch_number num 2
            relation(if needed for later filtering)
            name
            etc

            then connect by id to make your one to many set with master being first table so you can add all the people you need to the second table.

            You can then make your entry form(s), reports, letters, etc. As many as you want. You can even copy and paste from your word processor(s) the letter fill.

            I hope it made better sense with what our learned friends have contributed.

            Dave
            Dave Mason
            [email protected]
            Skype is dave.mason46

            Comment


              #7
              Re: Still struggling with normalization

              Dave
              Thanks for your help, I am starting to get the idea. Your right on top of what is giving me the most trouble to understand with respect to the WordPerfect document form. I can see how to normalize although I still have a lot of work to purify the normalization, I am making progress. What I don't see very well is what to do with the multiple instances of a certain roll in view of the wordperfect document. In the hand made merge file that we had, the rolls were named for each multiple instance that we might have and rewritten in the form each time there was something different. This is pretty easy to put into a merge. With sentences like "I have three children: Harry, Larry and Moe. To automate that via a merge I would insert fields in the sentence of the form like "I have (ChildNum) (ChildPlural): (Child1),(Child2) and (Child3).

              In the flat database I did the same thing but I had a lot of trouble with the "commas" and the "and" so eventually I set up a conditional paragraphs for the numbers of possible children in memo fields based on the number. This seemed to work although it was limited to a certain number of children. However, now I want to get around using fields like Child1, Child2 or Child3 and I am wondering just how that is done using a normalized database because I can't seem to see how you identify in the document fields, the multiple instances of a certain roll.

              I am starting to understand that the trick lies somewhere in the linking between the ID the RollNumber and the Entity field. However I still can't seem to see how they all get together in the fields in the document.

              My table scheme so far goes something like HomeAddress, BusinessAddress, Individuals,Assignmets, AssignmentRoll, Relations, RelationsRoll. DocumentResults and Documents. I need the name of the relation as well as the name of the person who is that roll. The same follows for assignment. Since all of these characters are people I didn't think I needed to make an individual table for each family roll type or assignment roll type but maybe I am wrong. It would mean a lot more tables.

              Thank you for your consideration and advice.
              Sincerely
              George R. Kenney

              Comment


                #8
                Re: Still struggling with normalization

                you only need one field to connect the tables. Use Id or something named the same in each table. This field will aleways have the same characters, numbers to connect with the same.

                Your child table will have all the pertinent information for the children, cousins, uncles etc.. You should have a filed in the child that has the relationship to the parent.

                example:

                Main table:
                id=123 name=john doe address=123 main st

                in the child:

                id=123 relation=son name=jim doe etc
                id=123 relation=aunt name=maggie brown etc
                id=123 relation=cousin name=don jones

                I hope you get the idea.

                Dave
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  Re: Still struggling with normalization

                  George R. Kenney

                  One (very simple) example is worth a thousand words...


                  Study it carefully.
                  Peter
                  AlphaBase Solutions, LLC

                  [email protected]
                  https://www.alphabasesolutions.com


                  Comment


                    #10
                    Re: Still struggling with normalization

                    My 2 cents:

                    From what I read into your comments, Dave was absolutely correct:
                    Originally posted by DaveM View Post
                    One of the hardest things in the world must be to outgrow the word processor. [or spreadsheet, etc.].... Moving these people (at the owner's insistence) was at best difficult. Once they got used to a new way, the processor died except for a few letters which could also have been done in a database....
                    I've run into similar situations where the customer has insisted on "updating to use a database but making it work exactly like the current system they are familiar with." They need to understand a few things:
                    1. The reason they are changing is because the current system doesn't work as well as they would like. This means change is needed.
                    2. Databases are not word processors or spreadsheets so don't try to make them work exactly the same way. (In fact, you don't want them to work exactly the same way - read on.)
                    3. A GOOD database APPLICATION will help them to get better and more accurate information. Spreadsheets and word processors have no means of controlling what the user enters. A GOOD database APPLICATION will control what the user enters and even IF the user can enter it. It will also organize it in such a manner that almost any grouping of data can be reported on, displayed, modified, or anything else you need to do with it.

                    A database is best used for standard "stuff". It is NOT a place where you should plan to put ad-hoc information or information that isn't logically linked to something else. When people use word processors or spreadsheets, they can enter any text they want. Unfortunately, most of us have heard of "garbage in, garbage out" and, in my experience, most systems that run strictly on a word processor or spreadsheet contain a lot of "garbage in."

                    Here's a prime example of garbage in. Someone I worked with years ago was tracking warranty on some automotive parts but wanted to be sure he got exactly what the customer was complaining about so he insisted on entering the customer's words in the "problem" column of the spreadsheet rather than categorizing them by standard things like noise, loose, broken, sticking, etc. One day he came to me (as the database expert) and wanted me to import his spreadsheet into a database and pull out all the noise compliants. I said I could do it if he could tell me what "problem" to search for. He said he could list some but would have to go through the whole list to get them all - there were things like "noisy", "rattles", "rattles at low speed", "rattles sometimes", "sometimes rattles", "makes a loud noise", "makes hissing sound", "makes a hissing noise", and on and on and on. He wasn't very happy when I told him there were three ways that I knew of to fix it: (1) he could go through each one and mark the ones related to noise as "Noise" to get an accurate result, (2) I could go through each one and mark the ones related to noise as "Noise" to get an accurate result, or (3) we could make some assumptions about what text to search for and get a best estimate - which could be way off and either too high or too low on any particular issue.

                    While the above example may not apply directly to your case, it should serve as an example of at least one significant difference between the use of a database (properly designed to minimize bad data entry) and most other methods.

                    It sounds like you are (or, your company is) trying to improve the way your current system works. Let's assume your job requires you to get from one side of the country to the other on a regular basis and you are currently doing this by driving there in a car. If you wanted to improve the way you accomplish this and you just heard about a new thing called an airplane, would you still insist on having 4 wheels, a driveshaft, stopping every 4 hours, not going over 70 MPH, and staying overnight each 1/3 of the trip? I think not! Similarly, if you are "upgrading" to a database, don't restrict yourself by insisting that old methods can't be changed. Everyone involved has to understand this - there will be some changes but, in the end, the way the company works will be improved. It may take some trial and error but it also took some trial and error to get that first airplane off the ground. In the end, it was well worth the time.

                    Comment


                      #11
                      Re: Still struggling with normalization

                      Peter

                      Thanks so much for the example, I'll work with it. It makes a big difference
                      a thousand thanks.
                      Sincerely, George R. Kenney

                      Comment


                        #12
                        Re: Still struggling with normalization

                        Cal
                        Thanks for your input I appreciate you observations. I agree 100%. That's why I bought into Alpha. They are changing from WordPerfect to Word the learning curve and the rewrite is the same that it will be to set up the database. The difference is that the database could handle all the forms instantly error and drafting time reduced. Completing a rush could be done in minutes. It takes time to type in names in every document and there is always a potential catastrophic problem if a name is misspelled. The database has the answers to centralized data which is important. Splitting up the data across at least three databases in separate applications is what I just can't justify if I could do it all in one. Also there is a huge expense in upgrading software. In that nothing ever goes smoothly. Suddenly things don't work that worked before and there is a new learning curve that really offsets the new functionality most of which one never learns to use before there is another upgrade. The risk of lost data and use is always there. So I more than agree that the answer is to work it out on a database than a wordprocessor. I believe alpha can do it for me.
                        Thanks again
                        George R. Kenney

                        Comment


                          #13
                          Re: Still struggling with normalization

                          George,

                          I see only 2 tables in one database for what you want to do. I do suggest a forced lookup/list for the relationships so you dont have someone putting in "cousin" and someone else putting "cuz" meaning "cousin". That makes your end product come out sad when you are trying to make a report based on relationships.

                          I made that mistake with the first Church Database I made.

                          You can also make letters/reports that the user can change the wording. I do this with a simple .txt file. They press a button and put in their words, save it and when they print the report/letter, it imports the words to all the people who get a report/letter. It may be soon for this one, but just to open you a little to what Alpha can do.


                          Dave Mason
                          www.lotrun.com
                          Dave Mason
                          [email protected]
                          Skype is dave.mason46

                          Comment


                            #14
                            Re: Still struggling with normalization

                            Dave
                            Thank you for your advice and observations. I keep studying all of this
                            and I can see I have a long way to go to get a handle on not only the concepts but Alpha�s plethoric interface which as simple as they assume it is, the whole of all the options are at least daunting. Along time ago someone told me that in programming the idea was to get something to work and then make it work better. I got the humongous flat database to work in Lotus Approach but it is obvious that this isn�t how to do it.

                            With the simple example I was given from Peter I can see how to get at the Parents because they are a single entity but I am still having trouble with the children as I guess I have to write some kind of IF statement addressing each instance to get at the individual children and set them into a LETTER in a sequence.
                            The simple letter examples I have seem to assume one will only talk to one person with one address and not mention other people or their addresses in the same LETTER. All I am doing is moving peoples names around in a LETTER and their assignment titles or relation titles. I will condition pronouns on gender. I will talk about sons and daughters and count them as well as putting their names in the Assignment fields and Relation fields. No one can ever get more than 7 assignments but their are about 30 types not to mention the 34 relation types. I am more interested in ease of production and functionality than the purity of design but in the long run it is better to be as pure as one can be. Furthermore, it is obvious that hodgepodge designs fail miserably eventually. I need more experience so I will keep experimenting. I think I am struggling
                            with just basic things because I haven�t done enough homework. I have been going around mining out all the information I can on normalization on the internet and I bought some unreadable book about it �Handbook of database design� they forgot that nontechnical �normal�, people might try to read their book. I am getting good information though and they all seem to concure that what I did in Approach was categorically the wrong way to do
                            just about everything which makes me feel good after the 400 or 500 hrs I put in on it over a period of time. Now I want to do a better job in Alpha. I hope at least I have a better idea after my work in lotus but Alpha certainly
                            isn�t very much like Approach. The problem I had in Approach which I hope I don�t have in Alpha is the assumption that the only thing anyone ever wants to do with a database is find a group or an instance of data and list it on an invoice along with the contact information.

                            Thanks again
                            George R. Kenney

                            Comment


                              #15
                              Re: Still struggling with normalization

                              If by LETTER you mean the tab in the Control Panel named LETTER, you can't do what you want there. I don't know if it is intended to allow it, but Letters do not allow for the one-to-many relationships that have been described below. Letters are simplistic, general one or two page, notes based on information from ONE table. They output "Letters", not "Documents". Trying to us a Set on a Letter does not yield any information from the child records.

                              You could accomplish this as a Report, because it allows for one-to-many relationships, but you'd have a hard time formatting the output to look like a nice document.

                              You're really trying to build a mail merge function, which begs a word processor, which I think I said below. Alpha really does not have a utility to produce complex merge documents from data. Even the Merge to Word option is limited to one table per document.
                              Steve Wood
                              See my profile on IADN

                              Comment

                              Working...
                              X