Fabrication Estimating Tool - Database Table Design

[drow]

Hello Access Forumers,



A friend who is starting a pipe fabrication company has asked to help him get started by creating an estimating tool for quoting on work (I have used access once, and therefore am the resident expert among my friends). The idea is that based on the material, pipe size, pipe thickness, type of work being performed, and quantity, a cost can be determined. I have reviewed the Northwinds example as well as other variants where the customer/product/order relationships have been quite well documented, and while some of this could be incorporated, I feel that the structure of this database is somewhat different. Searching the forums has not yielded any results for this case, but if this topic has been previously covered please let me know (and a point in the right direction would also be appreciated). I am hoping for a little advice from this user group with regards setting up the tables and the associated PK's, FK's etc before heading down the wrong path.

The issue that I am struggling to sort out is how to create "unique data", because the data is structured in more of a format where the intersection of the data yields the result. Some example data is provided below:

Pipe Diameter (in)	Sch. 20	Sch. 40	Sch. 60
2"	$10	$15	$20
3"	$20	$30	$40
4"	$30	$45	$60

In this table, the left column provides the pipe diameter, and the top row provides the wall thickness (or schedule). This could be sample pricing for a butt weld joint type for carbon steel. There would be similar pricing data for other material types (stainless steel etc.) and joint types. The question I have is how to link all this information together, and what is the best manner to do so. For example, if each record were to be unique and, in order to allow access to query by each field, would I need to create a table that looks as follows?

Pipe Diameter (in)	Schedule	Price ($)
2"	20	10
2"	40	15
2"	60	20
3"	20	20
3"	40	30
3"	60	40
4"	20	30
4"	40	45
4"	60	60

In this way, each record set is unique. However, because of common pipe diameters, would I also need to create some kind of product ID as the primary key? For example, there could be a naming convention of pipe diameter.schedule that would create a unique primary key for each record (for a 3" schedule 40 pipe this would be 3.40). If this is the case, this will require a significant amount of work based on the number of combinations available.

So, as you can see, I am a little unsure as to how to proceed and I cant help but feel there is a better/more efficient way to perform this task.

Hopefully this explanation is sufficient. Please let me know if it is not.

Thanks in advance!

[orange]

Do some research on Normalization. Getting a data model designed that matches your business needs is key to any database project.
There is a Normalization link within this Design Principles document.
http://forums.aspfree.com/attachment...2&d=1201055452

Good luck. And don't try shortcut the data model and design.

[drow]

Hi Mr. Orange,

Thanks for your speedy and informative response. I will have a read of the phlonx article and report back if there are still any questions.

Thanks again!

[drow]

Hi Mr. Orange/Other Forumers,

I have read the phlonx tutorial regarding database normalization to 3NF, but I cant seem to get past the 2NF stage and I am wondering if it is a result of an error in my interpretation of the concatenated primary key from the 1NF step. I am hoping someone can tell me if I am on the right track or if I have been staring at the data too long/not doing my homework.

The important fields I have come up with are:

Material
Pipe Diameter
Pipe Thickness
Work Type
Work Description
Unit Price

For clarity's sake, some sample data is provided below to show some potential variants for these parameters:

Material	Pipe_Diameter	Pipe_Thickness	Work_Type	Work_Description	Unit_Price
Carbon Steel	2"	40	Butt Weld	Standard butt weld	$40
Carbon Steel	2"	40	Bevel	45 degree bevel	$20
Stainless Steel	2"	40	Butt Weld	Standard butt weld	$70
Stainless Steel	2"	40	Bevel	45 degree bevel	$35

Based on the last step in 1NF in the tutorial for identification of the primary key, the tutorial recommends that the user identify the column or group of columns that uniquely identifies each row. According to my interpretation of this question, I would need the following combination of primary keys to have a unique row:

Material (PK)
Pipe Diameter (PK)
Pipe Thickness (PK)
Work Type (PK)
Work Description
Unit Price

Can someone please confirm if this is the correct interpretation of the rules because, if so, it does not allow for many relational tables to be built. Essentially, it seems as though this would fall under a "product variant" case similar to if ikea were to offer the same table with playdough and oak as the material options and red, black, and magenta as the color choices. I was not able to find any similar cases to this in example files (the northwinds example only provides "single variant" products) or through searching the forums/google. If anyone has any resources on this topic it would be greatly appreciated.

Thanks

[drow]

Hello Again,

Do any of the access guru's out there have any input on this topic? I am not expecting someone to solve my problem, and I realize that the topic of database normalization is well documented, but if anyone has any examples or literature on a product variant case, this would be really helpful as I think I need a new viewpoint.

Thanks in advance!

[drow]

Hi Mr. Orange/Others,

Another reply is being made to this post in the hopes of "reviving" it and not creating a duplicate post as per recommendations from those in the "Forum Support" section of this forum.

I have done some reading/exercises on database normalization but I cant seem to complete the second normal form based on what I have identified as my concatenated primary keys, or at least there is very little "segregation" of data (as per post on February 11). It comes down to an issue of unique data as has been discussed in the Feb 11th post. I can image that there is a similar case for a manufacturer that makes different variants of the same product - would anyone have any examples of a database like this or a data normalization example for this case? Alternatively, if anyone can see any flaws in my logic for data normalization based on the Feb 11th post, that would also be great. If the information provided is not clear, or additional information is required, please let me know as I am a bit stumped!

Thanks

[recyan]

Till some one comes along,
Referring your post : No - 4,
will
Work_Type Work_Description
be the same?
i.e. for
Work_Type
Butt Weld
will the
Work_Description
always be
Standard Butt Weld

and

for
Work_Type
Bevel
will the
Work_Description
always be
45 degrees Bevel.

Thanks

[drow]

Hi recyan,

Thanks for your reply. The quick answer to your question is "no". Without going into every variation, those Work_Types were provided for sample data. There could be various types of joints (different welding methods, different preparation methods), all of which would require a different Work_Type. If it is useful I could provide a more comprehensive list. The Work_Description field is paired with Work_Type field and provides a brief description of the work being performed (i.e. A 1:1 relationship between Work_Type and Work_Description).

Similarly, there will be more pipe diameters and pipe thicknesses than those provided, and there is a one to many relationship for each -each pipe diameter can have many pipe thicknesses, and each pipe thickness can have many pipe diameters.

Hope that all made sense! Bring on the questions!

Thanks

[recyan]

Till some one comes along, just a few thoughts (Pls note : I won't be able to explain what I have suggested based on 1NF, 2NF, 3NF, etc):

Material            Pipe_Diameter    Pipe_Thickness        Work_Type        Work_Description        Unit_Price
Carbon Steel        2"                40                    Butt Weld        Standard butt weld        $40
Carbon Steel        2"                40                    Bevel            45 degree bevel            $20
Stainless Steel     2"                40                    Butt Weld        Square-groove butt weld     $70
Stainless Steel     2"                40                    Bevel            Flare bevel groove weld         $35 

tblMaterial
MaterialID - PK
Material


tblDiameters
Diameter - PK - Type Number

tblSchedule
PipeThickness - PK - Type Number

tblWorkType
WorkTypeID - PK
WorkType

tblWorkTypeTypes
WorkTypeTypesID - PK
WorkTypeTypes
WorkTypeID - FK

tblProductsWorkTypeTypes
ProductID - PK
MaterialID - FK
Diameter - FK
PipeThickness - FK
WorkTypeTypesID - FK

tblProductWorkTypeTypesRates
ProductRatesID - PK
ProductID - FK
Rate
DateOfModification

Thanks

[recyan]

A slight variation of above, actually the original thought which I started with and my mind started wandering :

Material            Pipe_Diameter    Pipe_Thickness        Work_Type        Work_Description        Unit_Price
Carbon Steel        2"                40                    Butt Weld        Standard butt weld        $40
Carbon Steel        2"                40                    Bevel            45 degree bevel            $20
 Stainless Steel        2"                40                    Butt Weld        Square-groove butt weld        $70
 Stainless Steel        2"                40                    Bevel            Flare bevel groove weld            $35 

tblMaterial
MaterialID - PK
Material

tblDiameters
Diameter - PK - Type Number

tblSchedule
PipeThickness - PK - Type Number

tblWorkType
WorkTypeID - PK
WorkType

tblWorkTypeTypes
WorkTypeTypesID - PK
WorkTypeTypes
WorkTypeID - FK

tblProducts
ProductID - PK
MaterialID - FK
Diameter - FK
PipeThickness - FK

tblProductsWorkTypeTypes
ProductWorkTypeTypesID - PK
ProductID - FK
WorkTypeTypesID - FK

tblProductWorkTypeTypesRates
ProductRatesID - PK
ProductWorkTypeTypesID - FK
Rate
DateOfModification

Thanks

[drow]

Hi recyan,

Thank you for your prompt response, and my apologies for my extremely tardy one. Please do not take this as a lack of appreciation - I really appreciate you taking the time to respond. I didn't have time to look at your response in detail until yesterday and did not want to respond until I had something meaningful to respond with.

For the tblMaterial, is there a benefit to assigning an auto number PK field if I can ensure that each material type name is unique, and therefore use a text field? This might make tblProducts more "readable" for any additional editing. Most of the other fields having PKs that are numbers, which aligns well with their actual values.

While your suggestions for tables make sense and are extremely helpful, in this case it seems as though normalization can only pare down the data so far. The majority of data will still end up in the tblProducts where the many combinations are examined (please correct this statement if incorrect).

Anyway, thank you again for your response - I will continue building the database with the full set of variations, and see what happens.

Thanks

[recyan]

For the tblMaterial, is there a benefit to assigning an auto number PK field if I can ensure that each material type name is unique, and therefore use a text field?

I usually prefer to have a autonumber field as PK & the key text field set to Unique, No Duplicates Allowed.
I once happened to be sitting with a developer who was developing a package for his client. At that time the Client had asked the PK to be Text with the First alphabet indicating something & the Next 2 digits indicating something else & the last 2 digits indicating something else else.
At that time, I had suggested to the Client thro the developer about the limitations that perhaps could be faced in the future, as we were limiting ourselves to 26 alphabets, 99 and 99 digits. The client was in a hurry & was under pressure from his superiors to deliver & asked the developer to proceed without looking at the same. Later on, after a year, my friend, the developer told me, that he had been called to tackle the same problem that we had foreseen.
A Text Material Code, that appears good & sufficient today and all en-compassing, may not remain so tomorrow.
Sorry for the long ramble.
However, IMO, there is nothing wrong in having a Text Field as PK, provided care has been taken.

Let's hope, those experienced & more knowledgeable , come across this thread & give their insights.
Will be a learning experience for me too.

Thanks

[orange]

I would go with an Autonumber PK to ensure uniqueness at the database level. That's what autonumbers are for -- used by Access, not the user, and is unique.

I don't encourage "custom coding systems" where each character or group has some special meaning. They tend to be too complex and often are not well thought out-- can't have a new code because only 0-9 were set up(allowed) as recyan has mentioned.

I recommend you set up some test data with and evolving database structure to ensure you can get the data you need both in and out of the database. Don't "finalize" your structure until you are confident that you test data works flawlessly.

Good luck. It's an interesting project. There was one on one of the forums that dealt with lenses -- I can't find a link at the moment - but, like your requirement, the "product/service" is really more a set of attributes and each combination represents a "product/service" .

[drow]

Recyan/mr. Orange

Thank you for you responses. While I don't anticipate the material names changing, your words of caution bring some scenarios to mind where this could occur. I will follow your advice and use the auto-number - any querys to update pricing can be designed to show the material as text.

Recyan, thank you for pointing out the date modified for the rate field. This was something that I had not initially considered but its usefulness is immediately apparent.

Mr. Orange, that lens project sounds interesting. I will do some searching to see what I find, but if you come across it in the meantime and can post it on the forum, that would be great!

Thank you to both of you for your support.

[recyan]

Appreciate your taking a look at the suggestions made, trying to understand things & questioning things without blindly accepting them.
In fact your questions helped me to once again question and re-think and get confirmed ( Thanks Orange ) certain premises I had built long time back.

Best of Luck with your project.

Thanks