Portal/Database Setup Structure Problem
My concern is over the following tables: Companies, Purchase Orders, Incoming Quotes, Bill of Materials, Parts Inventory, and Line Items. In my PO's, Quotes, and BOM's tables I want to setup portals so the user can add parts from my Parts table via the Line Items table. I can do this, however I want to do it in a way that my database becomes "Parts-centric."
What I mean by that, is that when I add a part on my PO layout's portal, for example, I would like that part's manufacturer (a field that is pulled from the Parts table) to show up on the portal; additionally I'd like the ability to click on that part and have the database navigate me to that part's layout in the Parts table.
On this part's layout (and on every part's layout) I want, from there, to be able to see any PO, Quote, or BOM (that will be displayed as portals in that part's layout) that features that part on any of their portals. So, that part-centric aspect is that if the user has a question about what PO, Quote, or BOM features this part, they can do it all from the Part's layout, via portals and clicking on records to navigate through the tables.
Now, the trick here for me, is that because Parts, PO's, BOM's, and Incoming Quotes are ultimately linked to my Companies table (they pull Manufacturers or Client ID's from that), I cannot link them together in the table without creating additional table occurrences - and if I don't setup my table occurrences properly, I won't be able to see the multiple portals I want to see on a single Parts table layout.
My question is - a tall order - what kind of relationship/table occurrence algorithm do I setup here? I am trying to figure it out, and am having great difficulty. Or is this even the write philosophy when creating a relational database? Thanks.