Angus McKinnon 0 Posted October 25, 2020 Share Posted October 25, 2020 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? Link to post Share on other sites
Angus McKinnon 0 Posted November 3, 2020 Author Share Posted November 3, 2020 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? Link to post Share on other sites
bcooney 144 Posted November 5, 2020 Share Posted November 5, 2020 http://filemakerinventoryresources.com Link to post Share on other sites
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now