Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Opinions on a table name please...
Message
From
14/05/2010 03:46:00
 
 
To
13/05/2010 23:04:36
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP2
Database:
Visual FoxPro
Miscellaneous
Thread ID:
01464518
Message ID:
01464529
Views:
63
>I have a table of child items (raw steel, bolts, tubing, paint, structural steel pieces, misc hardware, etc.) that we order from vendors through Purchase Orders and charge each item to a particular Job.
>
>Therefore, each record in this table is linked to both the Jobs table and the PurchaseOrders table by a separate foreign key for each. It’s link to each of these parent tables is equally important, and I cannot see how it is more related to one than the other.
>
>What should be the NAME of this child table?

I see two general possibilities:

1. Name the table for the objects it nominally contains. In your case it could be something like "Materials". That could work well for purely physical items, but in practice service charges tend to creep in over time as well so it may need to be more generic. "Items" is very generic but actually not a bad name in some systems. Another possibility is "SKUs" (Stock Keeping Units) which is used in retail, but might be confusing here because it sounds like you're not maintaining any inventory (or at least trying not to).

2. Include information on how it's linked to other tables e.g. "OrderJobItems"

If you really want to normalize this, you might want to consider adding yet another lookup table. Maybe your operation orders items from vendors, and charges them to jobs, that are totally unique to each order and job. But in many cases there are lots of items that are the same from order to order, and job to job.
PurchaseOrders table:
PurchaseOrder_ID (pk)
...

Jobs table:
Job_ID (pk)
...

ItemsLookup table:
Item_ID (pk)
Description (e.g. "Cap Screws")
Dimensions (e.g. "1/4 x 2")
UnitsOfMeasure (e.g. "Each" or "100Pack") (could be yet another lookup table)
Type (could be another lookup table) (e.g. "Materials" [vs. "Services"])
Category (could be another lookup table) (e.g. "Fasteners")
...

OrderJobItems table:
OrderJobItems_ID (pk)
PurchaseOrder_ID (fk)
Job_ID (fk)
Item_ID (fk)
Quantity
POCost
JobPrice
...
If you don't have a lookup table like this, then the user has to add the details of each OrderJobItem every time. If you have the table, probably most of the time they can pick from pre-existing Items in the ItemsLookup table. They only have to add a new Item to that table (probably) once in a while, when something new or unique comes along.

It can also help with reporting. Suppose someone wants to know how many 1/4" x 2" cap screws were used across all Jobs, because a potential new supplier might offer you a good price for large volume purchases. If you don't have a lookup table, you're vulnerable to those items being entered inconsistently in the OrderJobItems table e.g.

Job 1: Desc: [1/4" x 2" cap screws] Qty: [100]
Job 2: Desc: [Box of 2" x 0.25" screws, 100 count] Qty: [1]

Good luck trying to consolidate them in cases like that.

On the other hand, if you do have an ItemsLookup table, you can easily pick out the OrderJobItems rows that have the correct Item_ID.

The one thing you have to be wary of with an ItemsLookup table, where users are allowed to add new Items, is duplicate entries. If the user can't quickly/easily find an existing item, they may add an unnecessary new one. This usually means you have to have a consistent naming scheme for your items and a good search facility. I always think of Patrick Stewart as Jean-Luc Picard: "Tea, Earl Grey, hot".

Also it's a good idea to have another search happen whenever a user enters a new Item, showing them pre-existing "near matches", if any, to give them another chance to find the right pre-existing item rather than entering a duplicate.
Regards. Al

"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov

Neither a despot, nor a doormat, be

Every app wants to be a database app when it grows up
Previous
Reply
Map
View

Click here to load this message in the networking platform