What's the best way to think about this? [complex product/specs/sourcing/ordering issues]


Hi, all! I’m setting up my bases, and I think I might be trying to do too much in a single base. Yet all the information is related (many-to-many, for sure). Can you help me figure out if I’m asking too much from a single base?

I am a jeweler. I do custom/bespoke jewelry, and I have a signature collection that I sell both wholesale and retail. The work is handmade by me and my studio assistants here in my studio. I’m trying to set up our product catalog, order tracking, materials sourcing, and technical specs for each design. Pieces are made to order for larger wholesale orders, and we also maintain a small amount of inventory for ecommerce sales and retail shows we travel to.

I started with the Product Catalog & Orders template.

(As a side note, I don’t think the way orders are handled works very well in that template because of the way they are entered. Our orders can contain hundreds of items, and I need to be able to import a CSV from our ecommerce system. But, that’s a secondary issue. I’m trying to get the catalog, specs and sourcing set up first.)

Here’s an overview of the data:
16 Collections.
Each collection includes up to 30 pieces, some more than others.
Pieces can have variants for size, style, metal selection, gemstone selection, etc.

Gemstone varieties include type, size, cut, shape. Vendors vary, meaning I have two or three sources for 4 mm blue chalcedony in a round rose cut, for instance.

Making pieces require metal materials on hand in strip, sheet, wire, tubing, findings, all in varieties of gauges, dimensions, etc. For metals, I have several vendors as well, although I tend to use certain vendors for certain things, and there’s not a lot of shifting sources when it comes to metals.

When we go to trade shows, we write sometimes dozens of orders. In addition to basic tracking needs, I need to create an aggregate for all of the orders that also give me totals for gemstones and materials needed. Sometimes it can take weeks to get the gemstones in stock that we need, and I don’t want shipping delays because I’m waiting on gems. (Up to now, I’ve been using the hashmarks-on-paper method for figuring out how many gemstones I need, in what kinds, in what cuts, in what sizes, and checking that against what’s in my stone stash. ARRGGGHHH!!)

Here’s an example of one of our line sheets, and this is one of the less complicated ones. This doesn’t include any of the specs that I mentioned, for either gemstones or metal materials. This is just what buyers see.

So, I’m a little confused. A lot confused. I know what I need to know, but I don’t know if it’s possible to get all of this into one base, or if I need separate bases for the specs and sourcing side, separate from the catalog and orders side. How should I be thinking about this?

I hope this is clear, or clear enough.