Line items from multiple tables

[Bob2011]

I'm working on a ordering system (purchase requisitions) that's integrated with asset, etc., tables.

When order line items are created, I'd like to eliminate redundant data entry, so that if a manufacturer part number, for example, is entered in an order line item record, that is related to the asset records.

I have separate goods and services tables which represent two general types of items that can be ordered. I'm thinking of having two portals on the order layout for each type, and then somehow calculate what would represent line items for the order, e.g.:

LINE # DESCRIPTION

------------------ GOODS PORTAL --

1 LCD display

2 Hammer

------------------ SERVICES PORTAL--

3 Warrantee

4 Install labor

------------------

... and then sum the totals for each of the two portals for an order total. I thought I'd check here to see if that is a reasonable approach. Thanks.

Bob

[Vaughan]

Is there a reason the goods and services are in separate tables?

[Bob2011]

Good question. The reason is that while there are many similarities, there are so many differences in the fields that exist in the tables that makes them call out to be separate entities. I simplified the examples, but the details are more like: hardware, software, and other (services, supplies, contracts).

Bob

[RodSierra]

Why split these into seperate tables? An item is an item, it has a quantity, a unit of measure, and a description as a basic example. Add fields if necessary instead of trying to categorize your items descriptions into seperate tables.

[Bob2011]

My idea is to have *many* more fields than may be needed purely for ordering to be included in the line item tables, and there are far more differences between asset types than similarities. The hardware, software, or other prototype line item tables make it so that everything that can be known about an item is entered up front (during the ordering phase); what is not known at the point of order, is serial number, organizational tag number, room number, etc. Once all the information that can be known is entered, and the order placed, then a script sets the fields of the hardware, software, or other table record to the values of the prototype record, and that asset record is then duplicated x number of times (based on quantity). Then, as needed, serial number and tag numbers are entered into asset records. The reason for this is twofold: 1) The quantity field of the line item record is just a number, whereas in the asset tables, I need x number of records. 2) After the asset records are created, they are subject to change, yet I don't want the order information to change; it is a historical record. After the asset records have been created, I may need to modify some of them, because part of the order was shipped with a substitute part, and each asset may go through changes over time, e.g., RAM might be added to a computer. I'm OK with the fact that some changes may have occurred after the order has been placed, and can use a notes field of the order record to make reference. It's a compromise I'm OK with.

software_line_item_prototype -|--|< software_asset (keeping in mind that relationship is for lookup (find the order an asset came from), and that the values of the asset are scripted (set field, probably) from the line item prototype record)

If I did go with a single line item table (and believe me, that would make things simpler in some regards), I haven't figured out a way to make that work with what I want (as just stated). For example, the (single) line item table would have to have a potential relationship with any of the three asset tables. A field in it: "type" could specify which table that would be. Without having a huge number of null values in the line item table to represent any of the asset types, it seems like something like this would be required (just a guess).

line_item -0|--|< software_asset_prototype -|--|< software_asset

line_item -0|--|< hardware_asset_prototype -|--|< hardware_asset

line_item -0|--|< other_prototype -|--|< other

One other feature I'd like to implement, is to use a line item prototype record (or asset prototype if the latter method is used) as a look up for a new line item record. I.e., I've already got something I want to order again, so why not automate the entry of that data into a new line item record (via a series of 'set field' commands, likely); modify anything that needs it, etc.

Bob