Confused, need help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Confused, need help!

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-03 : 17:51:08

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

2489 Posts

Posted - 2004-05-03 : 18:03:10

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>

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-03 : 18:24:41

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

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-03 : 18:43:01

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

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-03 : 18:45:09

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>

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-03 : 19:05:09

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

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-03 : 19:14:31

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>

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-03 : 19:38:39

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

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-03 : 20:04:47

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>

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-03 : 20:35:53

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources