SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Confused, need help!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 05/03/2004 :  17:51:08  Show Profile
I have the following table structure which is comprised of a material categories table, materials table, concrete mix table, and concrete recipe table.


tbMaterialCategories
CategoryID | Category 
-----------------------
1          | Aggregates
2          | Rebar 
3          | Mesh

tbMaterials
MaterialID (PK) | fkCategoryID (fk) | Material
-----------------------------------------------
1               | 1                 | Stone
2               | 1                 | Sand
3               | 1                 | Cement
4               | 2                 | 10M
5               | 3                 | 4 X 4

tbMixDesigns
MixID (PK) | Description
-------------------------
1          | Super strength

tbConcreteMixRecipe
fkMixID (PK|FK) | fkMaterialID (PK|FK) | Quantity
-------------------------------------------------
1               | 1                    | 900
1               | 2                    | 1500
1               | 3                    | 450



Here is where the confusion is. After an estimate is done, the information must be "Exported" to another DB system which cannot be
altered. The Schema on the other machine is ancient but I have to
follow the same account codes.

Here is the problem:
The account codes lets say for producing a certain type of product are: 10 - Concrete
1F - Mesh
1D - Rebar


Now I have no idea how to create a relationship between the account codes and the materials as shown above. If you note, some account codes [Mesh(1F) AND Rebar(1D)] are directly associated to materials on the materials list and other account codes [Concrete(10)] are associated to materials that are a culmination of materials on the materials list. Does this make sence?

Any help or suggestions?

Mike B

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 05/03/2004 :  18:03:10  Show Profile  Visit MichaelP's Homepage
Give each material an Account code. For Concrete, give all of the materials that make up Concrete the same account code.
When you export, do a SELECT DISTINCT AccountCode.

Will that work?

This estimation problem has been a real tough one.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 05/03/2004 :  18:24:41  Show Profile
quote:
Originally posted by MichaelP

Give each material an Account code. For Concrete, give all of the materials that make up Concrete the same account code.
When you export, do a SELECT DISTINCT AccountCode.

Will that work?


I thought about this, I am not sure why I nixed it! I will look at this again.

quote:

This estimation problem has been a real tough one.


You can say that again!

I have a meeting every monday morning with the end users (Estimating department/Accounting department). They are flexible, but not flexible enough I am afraid, and it doesn't help that the current accounting system is so ancient and they refuse to change it....

Mike B

Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 05/03/2004 :  18:43:01  Show Profile
quote:

Will that work?



I think part of the reason I did not do this was because other then what I showed earlier, the problem gets even more complicated. There
is also hardware which, like concrete, is made of several different materials. This hardware can be either Account : Castin (1J) OR Account : Field (1G) but they can contain the same materials, or even be the same hardware.

eg:

tbMaterialCategories
CategoryID | Category 
-----------------------
.......
5          | Flat Bar
6          | Headed Anchors

tbMaterials
MaterialID (PK) | fkCategoryID (fk) | Material
-----------------------------------------------
....
6               | 5                 | 4 X 1/4 
....
....
10              | 6                 | 6 1/8

tbHardware
HardwareID | Hardware
------------------------
1          | 4X4X1/4 Plate w/ 2 HAS

tbHardareMaterials
fkHardwareID | fkMaterialID | Measure  | Quantity
------------------------------------------------
1            | 6            | 4        | 1
1            | 10           | 1        | 2



Now like I said, that 4X4X1/4 Plate can be either cast into the product or sent as field hardware to site, which means it would get
charge to one account or the other.

Mike B
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 05/03/2004 :  18:45:09  Show Profile  Visit MichaelP's Homepage
Could you make two different hardware rows, one for cast-in and one for field?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 05/03/2004 :  19:05:09  Show Profile
quote:
Originally posted by MichaelP

Could you make two different hardware rows, one for cast-in and one for field?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>


You mean the same record, repeated?

tbHardware
HardwareID | Hardware                | fkAccountID
-------------------------------------------------
1          | 4X4X1/4 Plate w/ 2 HAS  | 1C (Cast in)
1          | 4X4X1/4 Plate w/ 2 HAS  | 1F (Field)


Or duplicate the materials? I am not sure this would be a good solution.

Mike B


Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 05/03/2004 :  19:14:31  Show Profile  Visit MichaelP's Homepage
I was thinking more along the lines of the second option, but you'd need to indicate in the "hardware" field if it was Cast In or Field so that when people go to choose the hardware item, they choose the right thing. If the descriptions are the same, and some concrete stuff makes it to the field w/o rebar that SHOULD have rebar, that would be bad.

I can not be held responsible for any suggestion that causes a building to go squish.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 05/03/2004 :  19:38:39  Show Profile
quote:
Originally posted by MichaelP

I was thinking more along the lines of the second option, but you'd need to indicate in the "hardware" field if it was Cast In or Field so that when people go to choose the hardware item, they choose the right thing. If the descriptions are the same, and some concrete stuff makes it to the field w/o rebar that SHOULD have rebar, that would be bad.

I can not be held responsible for any suggestion that causes a building to go squish.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>



There must be a way to re-arrange the tables or add a junction table so that this could work cleanly without the use of duplicate records.

Maybe I should look at adding a Account -> MaterialCategory junction?


tbAccountMaterialCategories
fkAccountID (PK|FK) | fkCategoryID (PK|FK)
-------------------------------------------
1A (Concrete)       | 1 (Aggregates)
1B (Castin hardware)| 2 (Rebar)
1B (Castin Hardware)| 6 (Flat Plates)
1B (Castin Hardware)| 10 (HAS)
1C (Field Hardware) | 2 (Rebar)
1C (Field Hardware) | 6 (Flat Plates)
etc...

Hmmmmm, ??

Mike B



Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 05/03/2004 :  20:04:47  Show Profile  Visit MichaelP's Homepage
That sounds like a plan.
That way, if you ever get a new system, you'll already have some "good" codes in place and you can get rid of those ugly legacy codes.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 05/03/2004 :  20:35:53  Show Profile
quote:
Originally posted by MichaelP

That sounds like a plan.
That way, if you ever get a new system, you'll already have some "good" codes in place and you can get rid of those ugly legacy codes.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>


Thanks for your insight Michael. I will work on it some and keep ya posted on what I do through this post!

Time to turn in. 14 hours is enough for one day :)!

Mike B
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000