PDA

View Full Version : Newbie and Tables and sets design


ABC123

popellis
03-10-2007, 12:04 PM
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

davej
03-10-2007, 01:51 PM
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.


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

popellis
03-10-2007, 03:19 PM
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

davej
03-10-2007, 03:53 PM
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

popellis
03-10-2007, 05:20 PM
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

davej
03-10-2007, 05:55 PM
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

Tim Kiebert
03-11-2007, 12:40 AM
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

MarkMark,

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
03-11-2007, 01:20 AM
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.

popellis
03-11-2007, 10:30 AM
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)

categ
03-11-2007, 12:19 PM
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?

popellis
03-11-2007, 02:11 PM
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

categ
03-11-2007, 04:16 PM
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?

Steve Wood
03-11-2007, 04:35 PM
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.