Alpha Video Training
Results 1 to 13 of 13

Thread: Newbie and Tables and sets design

  1. #1
    Member popellis's Avatar
    Real Name
    Mark Ellis
    Join Date
    Jan 2004
    Location
    Erie,PA
    Posts
    196

    Default 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. #2
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

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

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


  3. #3
    Member popellis's Avatar
    Real Name
    Mark Ellis
    Join Date
    Jan 2004
    Location
    Erie,PA
    Posts
    196

    Default Re: Newbie and Tables and sets design

    Quote 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

  4. #4
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

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


  5. #5
    Member popellis's Avatar
    Real Name
    Mark Ellis
    Join Date
    Jan 2004
    Location
    Erie,PA
    Posts
    196

    Default Re: Newbie and Tables and sets design

    Quote 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

  6. #6
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

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


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

    Default Re: Newbie and Tables and sets design

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

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

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

  9. #9
    Member popellis's Avatar
    Real Name
    Mark Ellis
    Join Date
    Jan 2004
    Location
    Erie,PA
    Posts
    196

    Default Re: Newbie and Tables and sets design

    Quote 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)

  10. #10
    Member
    Real Name
    cate garo
    Join Date
    Sep 2006
    Posts
    4

    Default 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?

  11. #11
    Member popellis's Avatar
    Real Name
    Mark Ellis
    Join Date
    Jan 2004
    Location
    Erie,PA
    Posts
    196

    Default Re: Newbie and Tables and sets design

    Quote 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

  12. #12
    Member
    Real Name
    cate garo
    Join Date
    Sep 2006
    Posts
    4

    Default 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?

  13. #13
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,842

    Default 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)

Similar Threads

  1. I can't seem to get my Sets correct for my tables
    By Matt Haworth in forum Alpha Five Version 6
    Replies: 1
    Last Post: 08-18-2005, 12:16 PM
  2. Design issue for newbie to Alpha 5
    By dave Thompson in forum Alpha Five Version 5
    Replies: 3
    Last Post: 02-24-2004, 07:57 AM
  3. Update tables/sets etc.
    By Roger Adams in forum Alpha Five Version 4
    Replies: 0
    Last Post: 03-20-2003, 07:24 AM
  4. Databases, Sets, and Tables
    By Rob Schippers in forum Alpha Five Version 4
    Replies: 2
    Last Post: 12-04-2002, 06:05 PM
  5. Problem with Tables and Sets
    By Todd Canipe in forum Alpha Five Version 5
    Replies: 1
    Last Post: 12-02-2002, 03:30 AM

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
  •