Need help setting up table structure for ordering by unit.

[ajhayes]

I posted a question here last week about an ordering database I'm trying to set up, and got some excellent help. So I'm hoping some of you might be able to get me on the right track with another question I've got on the same project.

Here's some general info on what I've done so far (For the record, I'm quite a novice at this.):

The database I'm setting up is for a small electric utility company to submit orders to the inventory warehouse for the parts that will be needed to build each project. The user enters the work request number (that comes from an unrelated work order system), and then can enter any number of materials orders for that project. Right now, the item number and quantity is entered by the user. I have a table that is imported from an excel spreadsheet from the inventory warehouse that has all of the item numbers and descriptions, and when the user enters the item number, it populates the description on the form. There is a query based on the current order number, and a report based on this query which can be submitted to the warehouse. All of this is working beautifully and it's doing exactly what we need... except now I've been requested to add a variation on this and that's where I need assistance.

When we are going to build a new powerline, each pole has a poletop configuration classification, or "unit number". They would like to be able to have the option to order by unit rather than having to enter each individual item specifically. For example, a C1 unit would have one crossarm, 3 insulators, and 3 pins; where a C2 unit would have two crossarms, 6 insulators, and 6 pins. Ultimately I need to have an order form that would show all of the individual components and required quantities to submit to the warehouse. So if they entered a quantity of five C1's, I'd need the report to list 5 crossarms, 15 insulators, and 15 pins.

I hope I'm explaining this question well enough. I didn't want to get too bogged down in the electrical terminology. Any suggestions in general on how to set this up? At this point, I'm just looking for some general suggestions on table/query structure.

Thanks so much,

April

[ChipR]

The question to ask here is whether you want to be able to order by unit only, or also by individual item number mixed together.
Do you need to record the units ordered, or can you just use them to quickly enter orders, and save only the item totals?

It seems like either way you will need a table with:

UnitNumber - text, PK
CrossArmCount - integer
InsulatorCount - integer
PinCount - integer
OtherPartCount - integer
etc. for all parts

[FishVal]

Hi, ChipR.

The table could be "more normalized". ... IMHO

Kind regards,
Fish

[ajhayes]

They are okay with having the option of "order by part number" where all items within that order are by part number and then a separate option of "order by unit" where all items within that order are done by unit number. As long as the two different orders can still be linked back to the project. For instance, the guy who does the metering portion may want to order his stuff by order number, where the guys who build the line would want to order by unit.

[ajhayes]

These are my initial thoughts:

Table: tbl_unit_items
Fields:
Item Number
Unit Number
Qty Per Unit

Table: tbl_units_order ed
Fields:
Order Number (auto number)
Unit Number
Qty of Units

Then I thought I would set up a query with the two tables and then multiply the qty of units times the qty per unit for each item in the unit.

Is this even close?

[FishVal]

Looks perfect in a case if unit items collection will not be modified once defined.

[ChipR]

Sounds like that should work, and I think that's what Fish meant by "more normalized."
It seems the obvious solutions like to evade me late in the day.

[Stewart Ross]

Hmm, your requirement is a fairly tricky one to implement in a fully-general way. If at all possible try to keep table designs general enough to cope with other assembly groupings, not just one type, which perhaps is the disadvantage of Chip's suggestion. The table structure suggested is not fully normalised - which is OK if you can trade the disadvantages that lack of normalisation may bring for the simpler implementation that it allows.

In a generalised solution, what you are implementing can be either a higher-level grouped assembly - in which case you need to have another table with a many-1 relationship to reflect the grouping - or a self-reflexive one where a part can be a member of another part using a 'comprises' 1-many relationship on the same table.

Either way, dealing with such a grouping complicates selection procedures, presenting users with parts but also with assemblies comprising of parts - sounds simple but is far from simple to implement.

PS hadn't seen Fish's post and the subsequent replies when I drafted this one!

-Stewart

[ajhayes]

Are you saying as long as the group of items that make up a unit don't change? I can't really see any reason why they would change, but I suppose there's the possibility that they might want to change to a different brand of insulator or something like that in the future, which would be assigned a new part number when it came into our warehouse.

[ChipR]

I would definitely advise against trying to treat a unit as a part comprised of other parts. You can use a query to sum the unit's parts with other parts of the order, it's just going to be tricky, like Stewart says.

[nico5038]

This is the so-called classic Bill of Materials problem.
Check http://en.wikipedia.org/wiki/Bill_of_materials for the general description.

I would advise to use the selected item to record the "sub items" as a recipe so you always have a reference to the "constructi on" at the moment of ordering. Just in case someone changes the construction of the item after ordering.

Nic;o)

[OldBirdman]

My warehouse stocks 3 items, Insulators, Poles, and Crossarms. ALL orders are for units. But a unit is composed of items, from 1 to many. The tables are:

Table1=Orders
Order# Unit Qty  Comment
   1         14   2   Pole Assembly
   1         21  14  Crossarm
   2         21   1   Crossarm
Table2=items in units
Unit# Item# Qty
   14    6187  1    
   14    1442  2
   14    1741  6
   21    1442  1
   37    6187  1
   72    1442  1
Table3=item list
Item#  Desc 
 1442   Crossarm
 1741   Insulator (Glass)
 6187   Pole 60'

Warehouse gets:

SELECT Table1.[Order#], Table2.[Item#], [Table1].[Qty]*[Table2].[Qty] AS Quantity, Table3.Desc
FROM (Table1 INNER JOIN Table2 ON Table1.[Unit#] = Table2.[Unit#]) INNER JOIN Table3 ON Table2.[Item#] = Table3.[Item#]
ORDER BY Table1.[Order#];

[FishVal]

Actually, [tbl_units_order ed] should not be linked neither to [Units] nor to [Items] if particular item model/supplier/price is expected to change in time.

[OldBirdman]

My point here was only that by ordering in Units, not Items, for everything, the tables & queries were quite practical. I didn't try to collect rows of the query into total lines, apply prices, or consider how the distribution system works.
By creating a Units table, and considering everything as units, the problem became fairly simple. Actual implimentation would need to track costs, shipping schedules, urgency (restore electricty after storm, etc.) For those Units comprised of a single Item, these could be added to the Units table using UNION, whenever needed.
I just disagreed with:

I would definitely advise against trying to treat a unit as a part comprised of other parts

your requirement is a fairly tricky one to implement in a fully-general way.

Looks perfect in a case if unit items collection will not be modified once defined

[ChipR]

- or a self-reflexive one where a part can be a member of another part using a 'comprises' 1-many relationship on the same table.
-Stewart

I was actually talking about a part comprised of other parts, and only parts. Having everything in units comprised of parts is a great idea, and quite different.