Data, Table structure

[mjdemaris]

Hello All,

I am looking for a good way to store some data, and keep it normalized, and allow for searching/filtering on several different fields.

We have about 10 departments who order stuff. A user must fill out a requistion, which then must be approved and ordered.

Currently we have a separate spreadsheet for the history of each department, which includes: Description, Part Number, Price, Vendor and Date Last Ordered.

Sometimes, someone orders the same item from another vendor, so we may end up with 5 different vendors for the same item. Normally, I would create three tables: Items, Vendors and a join table Part Numbers.

But, by throwing which department ordered the item into the mix, it is looking a little messy.

I am thinking:
Items:
ItemID
Description
Notes

Join Table:
ItemID
VendorID
DateLastORdered
Price
DepartmentID

Vendors
VendorID
VendorName

Departments
DepartmentID
DeptartmentName

What do you think?

Mike

 

[MarkK]

Hey Mike:
I think you'll need these tables:

Department
Order
OrderDetail (this is the join table between Order and Product)
Vendor
Product (Part, or Item)

To me, requisition (requested), approved and ordered are status possibilities for an order object. (single field)

DateLastOrdered should not be stored in a field, it should be retrieved from OrderDetail source data.

Price should be a property of a product, but should also be stored in the OrderDetail table, so you can change the list price of a product without altering historical orders.

Hope this helps,
Mark

 

[mjdemaris]

Thanks, Mark.

What do you think of this layout?

 

[MarkK]

That looks pretty good, but I wonder about . . .

• Doesn't the Order need to link directly to a vendor?
• Does the order need a date?
• If the order is dated, you don't need to store the DateLastOrdered as a field in OrderDetail
• tblItem should have the price you sell it for
• tblItemDetail should show the price you buy it for FROM THAT VENDOR

 

[mjdemaris]

Well, yes, the vendor does need to be linked to the order, as does each item on the order list (details). That way, if I want to reorder a particular item, I can search by vendor, vendor part number, or item description.

And, yes, the order does need a date.

If the price is stored in tblItems, then I couldn't have another price store from another vendor. If I put it in a Vendor/Item join table, I can have 5 items from every vendor and 5 different part numbers and 5 different prices.

And, I see some miscommunication here. This application is to allow our users to request tools, parts, and supplies through the company and from the vendors we buy from. So, we are not using this to take orders from other companies, but as an internal paper trail, which also allows us to allocate the funds spent for each department.

As a maintenance guy, I would: either go online and find a wrench that I need, or find a previously ordered wrench that fits my need, and add that to the requisition/order. Then, a manager would review and approve or deny it. If approved, it is sent to the purchasing agent, who actually makes the call (or goes online) to order the wrench. (multiple items on one order is allowed)

And, as you stated, each order would be from one particular vendor - much easier for the purchasing agent and money management.

 

[mjdemaris]

Here is an updated structure.

 

[jdraw]

I looked at your latest structure, and offer these comments. (I may not understand your business, so challenge anything I suggest)
-Partnumber is derived from what?---my guess is that Itemid will get you Partnumber
- every table has a field PK called ID, not sure where you are on this project, but I'd adjust these to
DeptID, OrderId etc. (not critical, but can be confusing)
-it seems a User is in a Department, so you need a relation between Dept and User
-I'm not following the
-it seems Vendors supply/sell Items, so a relation between Vendor and item of some sort is needed
-it seems that an Item could be on Many OrderDetails, so the relation would be 1 Item -->Many OrderDetails

As noted above, I may not understand your business, but I am attaching a draft data model for consideration.

Good luck.

 

[gemma-the-husky]

I think there are a few observations in order concerning your last post.

Well, yes, the vendor does need to be linked to the order, as does each item on the order list (details). That way, if I want to reorder a particular item, I can search by vendor, vendor part number, or item description.

And, yes, the order does need a date.

If the price is stored in tblItems, then I couldn't have another price store from another vendor. If I put it in a Vendor/Item join table, I can have 5 items from every vendor and 5 different part numbers and 5 different prices.

And, I see some miscommunication here. This application is to allow our users to request tools, parts, and supplies through the company and from the vendors we buy from. So, we are not using this to take orders from other companies, but as an internal paper trail, which also allows us to allocate the funds spent for each department.

As a maintenance guy, I would: either go online and find a wrench that I need, or find a previously ordered wrench that fits my need, and add that to the requisition/order. Then, a manager would review and approve or deny it. If approved, it is sent to the purchasing agent, who actually makes the call (or goes online) to order the wrench. (multiple items on one order is allowed)

And, as you stated, each order would be from one particular vendor - much easier for the purchasing agent and money management.

1. the vendor needs a reference on an order, not the order items. how can the order items be ordered other than from the vendor. If you are talking about a requisition list, then it's a different thing. A requisition list may end up as several orders. An order (logically) is an order on a single supplier.

2. the price would not normally be stored in an item table, I wouldn't have thought. I would have a supply table, linking item to vendor, and storing the price (or even a price history) in that table (or maybe a price history in a linked table)

3. surely as a maintenance guy, you select a part from a pre-approved list of suppliers, for the part. Why would you go researching alternative suppliers? If you want to get a different approved part, surely that is the responsibility of the purchasing department. You can do it your way of course, but that would mean you need a way to order items where you haven't even got a purchasing recxord, and may not even have a supplier account. I can't think you would want all mtce engineers doing this, really.

I am not sure if you are a big company or a little company. The system you describe sounds like a big company, with separate departments. Even so, the functions you describe ought to be in any system. I can't see that approving a new supplier for a product is logically part of the "order" process per se.

 

[mjdemaris]

J: part number is derived from the vendor. Nice ER diagram. What program is that? You are correct on the users/dept and vendor/item and OrderDetails.

Dave: I am not clear what you mean by “requisition list.” We affectionately call each form a “requisition”. I’ll get a screen shot of that for you…should have done that earlier! (doh!)

I put the price and part number back into a join table.

As far as our business practices regarding vendors…well, I think someone is working on an approved list, but I am not sure too many people are concerned about that. The powers that be pretty much let us order from whoever – amazon, ebay, sears.com, etc. It would be better if we had an approved list!

So, here is a revision of the structure.

 

[mjdemaris]

Here is the current "form" we use in Excel.

 

[jdraw]

Looks good. Test it to make sure.

Model was made with ErWin Community Edition

 

[mjdemaris]

Absolutely!