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.