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

Newbie and Tables and sets design

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

  • Newbie and Tables and sets design

    Hi All

    I have been here for awhile but still have a hard time with data workflow design. I have read the articles on one to one links and one to many links, but still get confused on what is primary table and child tables. Without getting into a lot of detail.
    I have a table for customer information, a table for job description, a table for materials used, and a table for time on the job.
    I have customer table as primary table with a one to many link to the jobs table
    and a one to many link from jobs table to materials table and to time table.

    My work flow in my mind is to be able to create a jobsheet for a particular customer fill out the material used and the time on the job whether it be 1/4 of an hour or 2 weeks worth of time. I also would like to go back at some time and see what i have done for any particular customer.

    Is my understanding of the basic principal's of tables and set correct, for my work flow description?

    Or am I headed in the wrong direction?

    Thanks

    Mark

  • #2
    Re: Newbie and Tables and sets design

    Mark,
    Your 1>M linkage is correct. One customer can have many jobs. One job can have numerous materials. If you think of it that way, it might be more understandable.

    Originally posted by popellis View Post
    Hi All

    I have been here for awhile but still have a hard time with data workflow design. I have read the articles on one to one links and one to many links, but still get confused on what is primary table and child tables. Without getting into a lot of detail.

    I have a table for customer information, a table for job description, a table for materials used, and a table for time on the job.

    I have customer table as primary table with a one to many link to the jobs table and a one to many link from jobs table to materials table and to time table.

    My work flow in my mind is to be able to create a jobsheet for a particular customer fill out the material used and the time on the job whether it be 1/4 of an hour or 2 weeks worth of time. I also would like to go back at some time and see what i have done for any particular customer.

    Is my understanding of the basic principal's of tables and set correct, for my work flow description?

    Or am I headed in the wrong direction?

    Thanks

    Mark
    Dave
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.

    Comment


    • #3
      Re: Newbie and Tables and sets design

      Originally posted by davej View Post
      Mark,
      Your 1>M linkage is correct. One customer can have many jobs. One job can have numerous materials. If you think of it that way, it might be more understandable.



      Dave
      Thanks Dave

      But here is where I overthink or confuse myself.
      If each jobsheet had it own identifing number would this becom a 1 to 1 link to Customers? As in each jobsheet would only have one customer. The jobsheet would be the parent and customers would be the the child along with materials and time. I realize that it is a different approach and like anything there is usually more than 1 way of doing it. Although this approach may not be wrong is it wrong for the work flow I am trying to achieve?

      Sorry if I am not to obvious.

      Mark

      Comment


      • #4
        Re: Newbie and Tables and sets design

        Mark,

        If each jobsheet had it own identifing number would this becom a 1 to 1 link to Customers? Yes

        As in each jobsheet would only have one customer. The jobsheet would be the parent and customers would be the the child along with materials and time. I realize that it is a different approach and like anything there is usually more than 1 way of doing it. Although this approach may not be wrong is it wrong for the work flow I am trying to achieve?
        I don't know what 'workflow' you are trying to achieve, so I can't really answer that.

        Depending on the direction you are going will determine whether you have a 1>M or a M>1 link. If I understand you correctly, with each jobsheet having its own unique ID number (1 jobsheet/ID number = 1 customer), there is a 1>1 link from from job sheet to customer, BUT, you would be much better off thinking in terms of customer (parent) to jobsheet (child), which is a 1>M relationship (one customer having many jobsheets). Remember that the customer is the most important part of the puzzle.

        Also, keep in mind that one jobsheet can have multiple inventory items and there isn't really a relationship between the customer and the inventory items as such; the jobsheet is what provides the linkage between those entities.

        Dave
        Dave Jampole
        www.customalpha.com

        Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.

        Comment


        • #5
          Re: Newbie and Tables and sets design

          Originally posted by davej View Post
          Mark,

          If each jobsheet had it own identifing number would this becom a 1 to 1 link to Customers? Yes

          As in each jobsheet would only have one customer. The jobsheet would be the parent and customers would be the the child along with materials and time. I realize that it is a different approach and like anything there is usually more than 1 way of doing it. Although this approach may not be wrong is it wrong for the work flow I am trying to achieve?
          I don't know what 'workflow' you are trying to achieve, so I can't really answer that.

          Depending on the direction you are going will determine whether you have a 1>M or a M>1 link. If I understand you correctly, with each jobsheet having its own unique ID number (1 jobsheet/ID number = 1 customer), there is a 1>1 link from from job sheet to customer, BUT, you would be much better off thinking in terms of customer (parent) to jobsheet (child), which is a 1>M relationship (one customer having many jobsheets). Remember that the customer is the most important part of the puzzle.

          Also, keep in mind that one jobsheet can have multiple inventory items and there isn't really a relationship between the customer and the inventory items as such; the jobsheet is what provides the linkage between those entities.

          Dave
          Thanks Dave this is what I thought, but I had to clear it up in my mind as to what I was trying to do. I guess in my Mind that the paper forms I fill out now are my jobsheet. This is where I fill in customer information and description of work to be performed, along with material used and time used to complete the job. I had set my database up this way but everytime I looked at the description or examples of 1 to 1 and 1 to many. I really could not understand that by creating a unique number for a jobsheet even though 1 customer could have many jobsheets that jobsheet 123 for customer xyz was a 1 to 1 relationship. It is confusing depending on how you look at it.

          Thanks again

          Mark

          Comment


          • #6
            Re: Newbie and Tables and sets design

            Mark,

            It is confusing depending on how you look at it. if you try to overthink it. If you just think in terms of: one parent can have one or many children, but each child can only have one parent (forgetting for a minute the reality of two-parent families). This 1>M relationship can entend down with a child becoming a parent and having children - or in the bigger picture, the children of the children can be thought of as grandchildren of the top parent.

            At the topmost level is the parent; at the next level down are one (or more) children. There can be children at the third level down who can be viewed as as children of their parents and grandchildren of the topmost parent. This can go as deep as necessary, but as a practical matter, if the structure is too deep, it is a good idea to revisit your design. It is better to be wider than deeper. Designing a relationship too deep can, quite often, lead to problems down the road.

            Dave
            Dave Jampole
            www.customalpha.com

            Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.

            Comment


            • #7
              Re: Newbie and Tables and sets design

              Originally posted by popellis View Post
              Hi All

              I have been here for awhile but still have a hard time with data workflow design. I have read the articles on one to one links and one to many links, but still get confused on what is primary table and child tables. Without getting into a lot of detail.
              I have a table for customer information, a table for job description, a table for materials used, and a table for time on the job.
              I have customer table as primary table with a one to many link to the jobs table
              and a one to many link from jobs table to materials table and to time table.

              My work flow in my mind is to be able to create a jobsheet for a particular customer fill out the material used and the time on the job whether it be 1/4 of an hour or 2 weeks worth of time. I also would like to go back at some time and see what i have done for any particular customer.

              Is my understanding of the basic principal's of tables and set correct, for my work flow description?

              Or am I headed in the wrong direction?

              Thanks

              Mark
              Mark,

              In your work flow description you actually have two seperate and distinct tasks. Each requiring a different set. A set is only a definition/description on how you want to view your data. A set does not determine the relationship of one table to another, you do that by determining the 'business rules' rules of your data. the set then reflects those rules that you have determined. This you seem to be doing. Your 'over thinking' led you to the realisation that "... each jobsheet would only have one customer." The complementary rule to this (I gather from your description) is that each customer can have many jobs. These rules then will guide/control your set design. So....

              the set you have described in your post will be fine for the task of
              I also would like to go back at some time and see what i have done for any particular customer.
              In this case the Customer is the primary focus so it is the parent.

              But for the creation of a new job sheet make the jobsheet table the parent. Link the customer to it one to one. And the two other tables to the jobsheet table one to many. (Similar to the Invioce set in Alphasports) Setup a lookup in the field rules of the jobsheet table for the cust_Id field so you can pick a customer when you create a new job.


              Hope this doesn't add to your confusion.
              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.

              Comment


              • #8
                Re: Newbie and Tables and sets design

                Here is a little sample that hopefully will help. I added a table for materials that would serve as a lookup table for the materials used table. I then added a set (and a form for that set) that has the materials table as parent for the purpose of viewing which customers used a certain material. Probably useless info but just to show the possibility.


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

                Comment


                • #9
                  Re: Newbie and Tables and sets design

                  Originally posted by Tim Kiebert View Post
                  Mark,

                  In your work flow description you actually have two seperate and distinct tasks. Each requiring a different set. A set is only a definition/description on how you want to view your data. A set does not determine the relationship of one table to another, you do that by determining the 'business rules' rules of your data. the set then reflects those rules that you have determined. This you seem to be doing. Your 'over thinking' led you to the realisation that "... each jobsheet would only have one customer." The complementary rule to this (I gather from your description) is that each customer can have many jobs. These rules then will guide/control your set design. So....

                  the set you have described in your post will be fine for the task ofIn this case the Customer is the primary focus so it is the parent.

                  But for the creation of a new job sheet make the jobsheet table the parent. Link the customer to it one to one. And the two other tables to the jobsheet table one to many. (Similar to the Invioce set in Alphasports) Setup a lookup in the field rules of the jobsheet table for the cust_Id field so you can pick a customer when you create a new job.


                  Hope this doesn't add to your confusion.
                  Thank you both Tim and Dave for yor help

                  Tim now I am still a little confused, so hypathetically if I had a new customer and I filled out a form with the information for that new customer. After that record is saved and my customer is in my table of customers. I would like to create a button that would take me to another form to fill out a new jobsheet with the new customer information filled in. Would I create a new set to reflect the 1 to 1 relationship of job to customer or would you just create different forms from the 1 to M set.
                  I have included the database, it's not complete and not all the forms are what I really want I'm just trying to get something to work for me. I have a more difficult one to do after this one, I have been playing around with this for 2 years during the winter months I still use paper forms, I still get compaints from the office that they can't read my writing. (Chuckle)

                  Comment


                  • #10
                    Re: Newbie and Tables and sets design

                    Hi, I am new to A5 but I was trying to teach myself Access so I realize the terms are somewhat different but it adds to my confusion. When I look at the mock up db you did here, I am not sure why the id is the same for the job time, materials used and materials. I thought each record in a table has to have a unique identifier. What am I missing?

                    Comment


                    • #11
                      Re: Newbie and Tables and sets design

                      Originally posted by categ View Post
                      Hi, I am new to A5 but I was trying to teach myself Access so I realize the terms are somewhat different but it adds to my confusion. When I look at the mock up db you did here, I am not sure why the id is the same for the job time, materials used and materials. I thought each record in a table has to have a unique identifier. What am I missing?
                      Hi Cate

                      If you are referring to Tim Kieberts file forMark.zip
                      and you are looking at the "job_custs" (set) this is probably a poor example and confusing because you are looking at the first record of each table, and each record has an auto number ID field, those fields were not setup for anything special so they all started with "1" they are unique as to where they belong.

                      Customer ID #1 belongs to Table Customer and Field cust_no.
                      Job ID #1 belongs to table jobs and job_ID
                      and so on.

                      To be more descriptive in the field rules for the form you can stat that the customer ID field should start with "Cust-000001" and Job ID start with "Job-00-000-000-000-001". Or any way you like it. I believe Tim was just showing a quick example of relationships and set design using the same tables, just working with the data differently. There is allot you can do in "field rules".

                      I hope this helps explain it to you, from 1 newbie to another. (1 to 1 relationship) :) Like 1 newbie can have many questions (1 to many relationship)

                      Mark

                      Comment


                      • #12
                        Re: Newbie and Tables and sets design

                        Thanks for the reply. I was trying to figure out why the ID field for all 3 of those tables were called; Rec_id. Doesn't each table need a unique identifier?

                        Comment


                        • #13
                          Re: Newbie and Tables and sets design

                          Its not the field name that makes an 'identifier' unique, its the value in that field. And, the value in that table needs only be unique for that particular table, not accross all or other tables.

                          I have a field in most of my tables named just ID. Its implicit that, if that is in the Customer table, its the Customer ID, and so on. It would be improper (my rules) to name that field Customer_ID. However, maybe if I had a Contact table related to the Customer table, I would have fields: ID (wich is the Contact_ID) and Customer_ID (which would relate back to the ID field in the Customer table.
                          Steve Wood
                          Join the ALPHA DEVELOPERS NETWORK
                          There is no Cloud. It's just someone else's computer.
                          Web - Mobile - Hosting - Products - Frameworks - Developer Resources
                          AlphaToGo | IADN (100% Alpha Anywhere Websites)

                          Comment

                          Working...
                          X