Inventory database - sensible table structure

[Angus McKinnon]

At the moment, I'm trying to tidy up some of the inventory databases that my company has spawned, and ideally have one solution covering everything. (At the moment data is spread over a few different, standalone files that have been made up ad-hoc for specific purpose). 

I've spent a bit of time working out what the users are going to need, and how we can re-use the existing data within a new structure. What's evolved is essentially a three-tier table structure, as follows:

1 - Products 

This table holds the products and packages that are offered to customers and invoiced for. The users are typically picking products (via a lookup portal) and adding them to invoices. 

2 - Items

This table has one record for each type/model of item that we own. (Irrespective of the quantity). It's based on actual physical items rather than sales packages. 

3 - Pieces

This table holds one record for each individual physical item. It'll contain test results and manufacturers serial numbers for equipment that requires it. 

 

The theory is that users would continue picking products as they do at present. Each product would link to one or more items, and the count of related pieces would be used to calculate availability and highlight shortages etc. 

Does this sound like a reasonable enough plan? I'm not sure I can think of any better way of organising it. It's tempting to do away with the Product table entirely, but the problem is that what appears on a customer invoice is not what is actually handed out, they get a single line item saying "Neat package" with a price, and that package can comprise two or more Items. (The names of which would be gobbledegook to many customers)

Further complicating matters is the fact that Items can be used as part of a package, or invoiced individually, if a customer chooses to take just that item alone. Of course, one item going out individually would prevent a package from being built with it. 

 

Are there any neater ways of doing what I'm trying to achieve? Or can anyone spot any pitfalls that I'm about to blunder into?

[Angus McKinnon]

I've thought a bit more about this (rather than just jumping straight in for once)

I reckon I'll need a join table between Items and Products, since Items can appear in multiple Product offerings (i.e. as part of a package or as a single line item themselves). 

Does that make sense?

[bcooney]

http://filemakerinventoryresources.com