Question Database Design for Timber Products - really stuck on this one?

[Johnrg]

Goodmorning all,

I work at a sawmilling company down here in New Zealand and we are looking at getting a new database designed rather than buying an off the shelf access system.

The timber products that we sell are made into packs (bundles).

Each pack has a unique number.

Each pack is made up of a certain amount of boards (depending on board size) that have the following criteria:

Grade
Treatment
Dryness
Finish
Nominal Width
Nominal Thickness
Actual Width
Actual Thickness
Length (3.0, 3.6, 4.2, 4.8, 5.4, 6.0, 6.6, 7.2m etc)

An individual pack usually has boards at several different lengths.

This is all fairly straight forward to put into a "Pack Details" table but where my design concept struggles is length critera.

Here in New Zealand we use the metric system and 99% of the time our sawmill produces, and our customers order standard lengths that can be allocated in a table as above using a new feild for each length. We have expressions that work out invoice calulations based on these lengths such as total peices, total meters, total cubic meters which is the amount of peices x length x nominal width x nominal thickness.

The problem comes when very ocassionally we get and order or produce a product that has a different length than we have allowed for in our pack details table and related invoice expressions.

What I need is some way to accomodate a couple of user definable length feilds in the database so that we can use our standard lengths most of the time but add one or two new lengths if required. This would also need to translate to all the expressions in the database.

Has anyone got any advice here?

At the moment I have all the pack details, including length in one table but I am not sure if this is the way to go as lengths and there related expressions are very fixed this way.

Any ideas would be appreciated.

JG

 

[Scooterbug]

It's actually very simple. Create a table to store the different lengths. Then just record the LengthID where you would have the length. You can then enter as many lengths you wanted to. You can use a combo box on the form for selecting the length.

 

[dcb]

If you dont want the user to always see these adhoc lengths -

Create a table with your default lengths but dont link this to your main table... (ie no id's)
in your main table set the length field to a combo box (lookup) - and make sure "limit to list" is false

Of course you can just skip the main table part and do the same on the form...

Dont know how or where you get your billing calcs from but I am assuming they are "calculated" not directly linked to a length table

 

[Johnrg]

Thanks for the answers guys.

I think I understand what you are saying however in terms of actually entering this data in for each record/pack how would we go about this process if we have a pack has that multiple different lengths that need to reside in each record?

It would be a very time consuming process if the operator had to choose each length from a combo box and then enter the data for that length.

In our current database we have these lengths as feilds across a subform and the operator just tabs across and enters the data in each feild which is very quick.

for example heres a typical packet from my mill:

Grade: 1 Frame
Treatment: Untreated
Dryness: KD
Finish: Gauged
Lengths: 10/3.0m, 25/3.6m, 55/4.8m, 77/6.0m, 100/6.6m.

Yes the billing info is calculated on reports and in queries using expressions - heres one of the expressions - as you can see it's impossible to add a new length to this expression on a case by case basis if we have a length that is not standard.

=NZ([300]*0.3)+NZ([400]*0.4)+NZ([600]*0.6)+NZ([900]*0.9)+NZ([1090]*0.109)+NZ([1200]*1.2)+NZ([1500]*1.5)+NZ([1800]*1.8)+NZ([2100]*2.1)+NZ([2400]*2.4)+NZ([2700]*2.7)+NZ([3000]*3)+NZ([3300]*3.3)+NZ([3600]*3.6)+NZ([3900]*3.9)+NZ([4200]*4.2)+NZ([4500]*4.5)+NZ([4800]*4.8)+NZ([5100]*5.1)+NZ([5200]*5.2)+NZ([5400]*5.4)+NZ([5700]*5.7)+NZ([6000]*6)+NZ([6600]*6.6)+NZ([7200]*7.2)

Thanks

JohnG

 

[Fifty2One]

Just a suggestion - place all of the 'non-standard' sizes into the same database.
I have built production applications which also need the adhoc definitions for a customer as a build on spec or prototype which may become regular production or may be a one of 'special' I include the specs within the same database as the common items but I include a date-time stamp which updates if the item is run through production or the specs have been 'tweeked'. The production manager pulls a list of the items not run through within a given amount of time and reviews the validity of keeping the specification.

 

[Simon_MT]

If you predetermined lenghts and width and thickness sofaras as lenghts are concerned you could automatically drop the length into the "Pack file" and create a code for Variable and only in this case the Pack File has to be manually updated. This is duplicating information but does allow for both standard and variable lengths.

Simon

 

[Scooterbug]

Thanks for the answers guys.

I think I understand what you are saying however in terms of actually entering this data in for each record/pack how would we go about this process if we have a pack has that multiple different lengths that need to reside in each record?

I will offer suggestions in a bit...

It would be a very time consuming process if the operator had to choose each length from a combo box and then enter the data for that length.

You can store whatever data you want with each length record. For example, if the price for length 100 is $1.00 a piece, the price can be stored in the table that as the length. If you need some kind of number for calculations, those too can be stored.

In our current database we have these lengths as feilds across a subform and the operator just tabs across and enters the data in each feild which is very quick.

I get a sneaking suspicion that there might be a normalization issue. It sounds like you are using Access like excel. Not a good thing to do.

for example heres a typical packet from my mill:

Grade: 1 Frame
Treatment: Untreated
Dryness: KD
Finish: Gauged
Lengths: 10/3.0m, 25/3.6m, 55/4.8m, 77/6.0m, 100/6.6m.

Looks like there are multiple lengths and maybe quantities in each pack. This tells me that there can be multiple Lengths per pack, with multiple packs per order. I would use 2 tables for this. One to record each unique length in a pack. Each length in a pack would have their own entry. Record a PackID with each record so that you can tie them all together. Then have a table to record the PackIDs for each Order. If there are standard packs that are common, you could either flag those packs or create a table with a list of the pack numbers for reference.

Yes the billing info is calculated on reports and in queries using expressions - heres one of the expressions - as you can see it's impossible to add a new length to this expression on a case by case basis if we have a length that is not standard.

=NZ([300]*0.3)+NZ([400]*0.4)+NZ([600]*0.6)+NZ([900]*0.9)+NZ([1090]*0.109)+NZ([1200]*1.2)+NZ([1500]*1.5)+NZ([1800]*1.8)+NZ([2100]*2.1)+NZ([2400]*2.4)+NZ([2700]*2.7)+NZ([3000]*3)+NZ([3300]*3.3)+NZ([3600]*3.6)+NZ([3900]*3.9)+NZ([4200]*4.2)+NZ([4500]*4.5)+NZ([4800]*4.8)+NZ([5100]*5.1)+NZ([5200]*5.2)+NZ([5400]*5.4)+NZ([5700]*5.7)+NZ([6000]*6)+NZ([6600]*6.6)+NZ([7200]*7.2)

The variables you use for calculations can be stored in the table that contains the various lengths. You can then use them in expressions in a query.