Fabrication Estimating Tool - Database Table Design Round 2

[drow]

Hello All,

Previously (quite a long time ago) I posted an article on a database I was trying to build for a friend in order to build quotes for a pipe fabrication business. This thread can be found here:

https://www.accessforums.net/databas...ign-32250.html

After a long hiatus and some delays due to my real job, I am now working on this project again and hoping that those out there can lend some guidance. In its simplest form, the task was to take the price book that the company had developed, and integrate it into a database so that they don't have to refer to the book each time to build their quotes (currently in excel). I find this project a little more complex than a standard quotation/invoicing project because rather than a scenario where product A costs X dollars, there are multiple criteria required by the user to input in order to determine a price for each "piece of work" to be performed.

After analyzing the price book and transitioning all of the data to a form that is "database friendly", I have come up with the following table which I hope will match the cascading drop down fields that the user will navigate with to determine the price for the "piece of work" to be performed. A simplified version of the table is below. The field of pipe fabrication is fairly convoluted so if you have any questions please ask (this wikipedia webpage is also helpful for some background information: http://en.wikipedia.org/wiki/Nominal_Pipe_Size).

Component	Measurement System	Task Category	Task Type	Task	Price Criteria #1	Price Criteria #2
Pipe	Schedule	Fabrication	Prep	Pre-Heating	Pipe Diameter	Pipe Schedule
Pipe	Schedule	Fabrication	Weld	Butt Weld	Pipe Diameter	Pipe Schedule
Pipe	Schedule	Fabrication	Finish	Post-Heating	Pipe Diameter	Pipe Schedule
Pipe	Schedule	Testing	Examination	Magnetic Examination	Pipe Diameter	Pipe Schedule
Pipe	Schedule	Logistics	Protection	Pipe Protectors	Pipe Diameter	Pipe Schedule
Pipe	Wall Thickness	Fabrication	Prep	Pre-Heating	Pipe Diameter	Pipe Schedule
Pipe	Wall Thickness	Fabrication	Weld	Butt Weld	Pipe Diameter	Pipe Schedule
Pipe	Wall Thickness	Fabrication	Finish	Post-Heating	Pipe Diameter	Pipe Schedule
Pipe	Wall Thickness	Testing	Examination	Magnetic Examination	Pipe Diameter	Pipe Schedule
Pipe	Wall Thickness	Logistics	Protection	Pipe Protectors	Pipe Diameter	Pipe Schedule
Flange	Flange Class	Fabrication	Weld	Flange Weld	Flange Diameter	Flange Class
Flange	Flange Class	Testing	Examination	Magnetic Examination	Flange Diameter	Flange Class
Flange	Flange Class	Logistics	Protection	Flange Protectors	Flange Diameter	Flange Class

For the purpose of this post, I have not included all combinations (as there are many). The intent is that the user uses the fields to specify the criteria that leads to a single price for that "piece of work". The two fields that truly determine the price are the last two columns. However, the other fields are used both for categorization of work (for reporting etc) as well as to restrict the options at the next field to a manageable amount. It should be noted that there are differences between column 2 and column 7 in the table - column 2 defines which system is to be used, and column 7 is where the user would input the exact value within this measurement system (see below).

The step that I am slightly confused on is how to incorporate the pricing criteria (particularly the second criteria) fields into a single table. To understand this problem I need to provide you with the permissible options for these fields. With regards to pipe, the permissible options for price criteria #2 as shown in the table are:

For pipe schedule:

Pipe Schedule
20
30
40
60
80
100
120
140
160

And for wall thickness:

Wall Thickness (in)
0.375
0.500
0.750
1.000
1.250
1.500
1.750
2.000
2.250
2.500
2.750
3.000
3.250
3.500
3.750
4.000
4.250
4.500
4.750
5.000
5.250
5.500
5.750
6.000

Finally, for flanges:

Flange Class (lbs)
150
300
400
600
900
1500
2500

So, as you can see, the options for this category are very different, and I am struggling to see how to place them into a single field. Ideally, I think I should split the data into tables as follows

tbl_Pipe_Schedule

PK	Schedule
1	20
2	30
3	40

(for all schedule numbers)

And create similar tables for tbl_Pipe_Thickness and tbl_Flange_Class. However, in order to make this data fit within the structure that has been established, I think I will actually need to do the following

tbl_Price_Criteria_2

PK	Price Criteria
1	20
2	30
3	40
4	60
5	80
6	100
7	120
8	140
9	160
10	0.375
11	0.500
12	0.750
13	1.000
14	1.250
15	1.500
16	1.750
17	2.000
18	2.250
19	2.500
20	2.750
21	3.000
22	3.250
23	3.500
24	3.750
25	4.000
26	4.250
27	4.500
28	4.750
29	5.000
30	5.250
31	5.500
32	5.750
33	6.000
34	150
35	300
36	400
37	600
38	900
39	1500
40	2500

So, I have written quite a lot here. If anyone out there has time to respond, I would appreciate if you could tell me:
-If the question makes sense
-If you require any more information
-If you have any advice on the problem stated.

Thanks

[trevor40]

you allmost have all of what you need, you need to use a form to get the data and do the calculations there.
if you seperate tables for ranges of items ie size, weight, class. They don't have to be linked tables

Pipe Schedule*wall Thickness (in)*Flange Class (lbs)*Price Criteria=customer price (sample only, won't work as is)
can all come from seperate tables

[orange]

If you want to get all combinations of
Pipe Schedule*wall Thickness (in)*Flange Class (lbs)*Price Criteria you can do so quite easily by means of aCartesian Product (Cross Join). or see http://www.sqlguides.com/sql_cross_join.php

My guess is that your price for each combination will not be static -- prices will change, at least on some combinations, over time. So,you will probably add anther field eg PriceEffectiveDate or similar, to ensure you can change one or more prices over the life of the system.

[drow]

trevor40, orange, thank you for your responses.

If I wanted to have three tables (tbl_Schedule, tbl_Thickness, tbl_FlangeClass) all with an autonumber PK and then the relevant values for the applicable table (as shown in first post), is there a way that I can have these three tables create one table, tbl_Price_Critieria_2 as shown at the end of post #1? It would need to append only the unique value to create a complete list for price criteria 2 (there are no duplicates for the data shown, but for other tables there would be). I was hoping to use relations to define that the values for all three tables are related to the "price criteria" field in the tbl_Price_Critieria_2 table, but I cant quite wrap my head around it.

Thanks

[trevor40]

creating a table of calculated values is very bad practice,
if you have the tables you can do all the calculations on a form or report.

[orange]

Here are some facts about your data based on your post.

I have made tables from your post data.

PipeScheduleID	PipeSchedule
1	20
2	30
3	40
4	60
5	80
6	100
7	120
8	140
9	160

WallThicknessID	WallThickness_inches
1	0.375
2	0.5
3	0.75
4	1
5	1.25
6	1.5
7	1.75
8	2
9	2.25
10	2.5
11	2.75
12	3
13	3.25
14	3.5
15	3.75
16	4
17	4.25
18	4.5
19	4.75
20	5
21	5.25
22	5.5
23	5.75
24	6

FlangeClassID	FlangeClass_lbs
1	150
2	300
3	400
4	600
5	900
6	1500
7	2500

Here is the SQL for the query

SELECT pipescheduleID
,WallThicknessID
,FlangeClassID
FROM 
PipeSchedule
, WallThickness
, FlangeClass;

and see the attached jpg for a Cartesian Product of your data.
As I said you would add a Price field and a field for PriceEffectiveDate or similar.

Good luck with your project.

[drow]

Hello trevor40 and orange,

I don't think I did a very good job of explaining the project so I will make a second attempt here. To define the physical dimensions of pipe, and therefore the cost to perform associated tasks on it, there are two dimensions required: the diameter of the pipe and the wall thickness. Because there are multiple ways to define these fields, lets just generalize and say diameter = "size" and wall thickness = "rating". These two fields would correspond to price criteria #1 and price criteria#2 in the first table in the original post. Now, orange, the tables in your post of pipeschedule, wallthickness, and flangeclass, would all fall under the rating field. Therefore it is not so much a cartesian product, as it is an addition of all of these fields, as they are mutually exclusive options. I was hoping to define the values for these table individually, and they run a query to combine them all into a single field (while accounting for duplicates that may exist between the two fields). Then, if the user wanted to, at a later date, update and add another schedule number, it would be added to the "rating" field automatically. Hopefully that made a bit more sense. Please let me know if any details of the explanation are lacking.

Thanks

[orange]

I'm going to suggest this tutorial for you to work through. It will help you get your facts straight and help layout a business description from which you can design tables and relationships.

Good luck with your project.

[drow]

Hi orange,

Thanks for the suggestion. That was a quite helpful document for providing a nice summary or concepts I had read about elsewhere, and some of his other documents nicely outline a more formal approach to design than I had been taking.

However, that being said, I am still a little confused about how to combine three fields in separate tables into one field in another. Based on the fact that is doesnt seem like it falls easily into the basic relationships that can be defined, I get the sense I am doing something fundamentally wrong. I will give it a bit more thought.

Final question if you are still game - I have read in passing that there is a good practice when creating entity relationship diagrams in access that you create a copy of a table (usually the same table name but denoted with a "_1") to define further relationships. Are you aware of any resources that discuss this specific requirement for entity relationship diagrams and the appropriate usage?

Thanks

[orange]

There are many books on approaches to design. My favorite is Michael Hernandez --Data Base Design for Mere Mortals. A lot of his stuff is mentioned at RogersAccessLibrary. His 12 Steps to Better Databases is part of the tutorials on database design on the site.

However, that being said, I am still a little confused about how to combine three fields in separate tables into one field in another. Based on the fact that is doesnt seem like it falls easily into the basic relationships that can be defined, I get the sense I am doing something fundamentally wrong. I will give it a bit more thought.

You're going to have to provide context on this. An example with some of your data would be helpful.

If you have a relationship/junction table that involves 3 tables, I would make a separate PK in the junction; use each of the other FKs to the other 3 tables as parts of a composite unique index to avoid duplicates.

But I'll wait for you example before confusing the issue.

As for your "final" question, that usually involves a lookup or reference table. If you have a situation, for example, a CountryLookup table with fields such as CountryID(PK), CountryName, CountryISOCode, and your applicatiom is dealing with things such as - SupplierCountryId, PurchaserCountryId, CustomerCountryId - you would/could use the CountryLookup table to resolve/relate these. Each would use the CountryId, but the context is different. In your data model you could show 3 separate uses of the CountryLookup table as separate entities with your table_1, table_2 approach. In effect each of these "fields" is getting its value from a common set of codes/values.

Hope that helps a little.

[drow]

Hi Orange,

Thanks for your continued support. With regards to providing some context for my question, I recently followed one of the recommendations from Rogers 12 steps to better database design and created a narrative for the project. The relevant part of the narrative is as follows, with the most important sentence bolded:

****************************

• Work can be performed on the following piping system components: piping, flanges, fittings, attachments, items, and modules.

• Work can be performed for components with the following measurement systems: schedule, thickness, IPS, class, and per.

• Piping can be measured by the following measurement systems: schedule, thickness, and IPS.

• Flanges can be measured by the following measurement system: class.

• Fittings can defined by the following measurement systems: class, schedule, thickness, and IPS.

• Attachments can be defined by the measurement system “thickness”.

• Items and modules can be defined by the measurement system “per”.
• The pricing for each tasks will be determined by the component, the measurement system, the size (typically diameter), and the rating (can be class, schedule, thickness etc. – dependent on the measurement system).

***************************

This last sentence essentially summarizes the table that I provided in my first post, but I have changed "price criteria #1" and "price criteria #2" to "size" and "rating", respectively. The problem that I am currently grappling with is the "size" field. If piping is considered, the measurement systems and their associated sizes are listed below:

By Schedule:

Size
2
2.5
3
4
5
6
8
10
12
14
16
18
20
24

By IPS:

Size
2
2.5
3
4
5
6
8
10
12
14
16
18
20
24

Thickness:

Size
3
4
5
6
8
10
12
14
16
18
20
22
24
26
28
30
32
34
36
38
40
42
44
46
48

So, I am currently thinking that I need to somehow combine the results of the three separate sizes for each of the measurement systems into one, unique, master size field. However, because doesnt seem to fall into one of the standard types of relationships, I get the sense that I am doing something wrong.

Hopefully that makes some sense.

Thanks

[orange]

I'm just not following your

three separate sizes for each of the measurement systems

.

I showed the Cartesian product approach to getting all unique combinations of schedule, thickness and flange.
You can do the same for the 3 "things" you 're concerned with.

[drow]

Hi orange. Sorry for not getting back to you sooner and thanks for your reply. I ended up having to make a many to many table which defined each of the allowable combinations (with cartesian it gives all and unfortunately I only needed certain cases). Anyway these are the things that seem to trip me up. I do have another question but I think it might be more relevant to make a new thread rather than have essentially a whole new topic within this thread.Thanks again for your support, and maybe see you at the next thread?