Pat, Firstly, I very much appreciate the time you are taking to help me suss this out. It's very kind of you!
I think I will rename the inventory to "
Catalogue" as regardless of whether it was a piece we made for our shop stock or a special commission, everything we make can be made in a choice of whatever a customer desires.
Is the price standard across all suppliers? If not, you need to add a supplier to the table?
- No I will add a supplier table
Whenever you have a picture, you would save it in the images folder and add the file name to a text field. Then you can display the photo on the quote and/or on the inventory form
Yes, I will name these according to the ID code from the inventory/catalogue
You might need to add findings as inventory items. Then you would use a junction table to connect the finding to a quote or an inventory item. This makes me lean toward requiring a quote to "define" each inventory item. Then inventory is just inventory info. I don't know how you handle invoicing but I assume you don't need it in this app. However managing inventory by updating the quantity field leaves a lot to be desired. Mostly because it leaves no audit trail. This is probably not a big problem for you but in most applications, I would use a transaction table to add or subtract from inventory so you have a better shot at keeping it clean.
Little unsure , do you mean add findings to the inventory(catalogue) table itself? I had consider this approach but decided it might be easier as Table/ list on its own that could include its own categories, Stones, chains, findings, services form contactors etc being as these are all outsourced extras to the work we do in-house. It would relate to the supplier list. Maybe this is what you just explained....Junction table sounds the way to go.
I'm just talking so you can clarify your thoughts also and think about what you want out of the application.
Originally we sorted all information together under three main categories:
CM = custom-made pieces
JB = STOCK Jewellery Items made from scratch (no cast patterns so more like one-offs)
YS= Stock items that have a cast pattern number associated with them and these are pieces that are cast for reproduction (these items are made and stocked in store on a regular basis)
We have photo albums of all jewellery we make CM, JB and YS. The photographs are coded for example
CM807 or
YS601.
The scenario is, a customer may come into the shop and not find something off the shelf. They start looking in our photo album, they see something they like and want a price. It takes a long time to work this out on the fly, especially if they start wanting different options of metal etc.. If I could automate this based on the information recorded for that piece it would be a great advantage.
the information we need to create a new quote is
-the weight
-the original metal it was made in
- the labour times
- Information on the extra items, suppliers etc
The hardest part for me was also how to store and relate all this information. I want to keep the original values from this Catalogue intact but I don't see sense in having a bunch of quotes which may or may not be actually made stored in the same place as your historic information from this catalogue.
I was thinking a one-to-many relationship could exist. One Catalogue item 'CM807' could have many quotes made for several different customers.
Quotes and Orders could be stored together as you would not have an order exist without a quote first as values of precious metals are constantly changing? I was thinking that a simple yes/no field could exist to turn an accepted quote into an order.
But once it becomes an order it then has to be made.
The hardest part for me was how to then create my Jobsheet for the workshop as I think ideally, it should now become its own new Catalogue item. Because this report was based on the new Catalogue entry and not the quote I could not pull the relevant information about customer details and outstanding balance as these were based on a report based on the Quote.
In short, I would like to figure out how my new
Order/Quote relates to my new entry into the Catalogue.
My head is spinning too!
The other problem (maybe it is not a problem) I need to be able to create a quote from scratch from something that does not exist in our Catalogue by entering my estimated weights and metals but maybe this is just a different form that is used a variation of the Quotes done from the catalogue. It would probably be good to consider this now instead of crossing that bridge when I come to it. Which is the usual reason I get into difficulties, bad planning on my part.
I think analyzing the outputs that you require and then what you would like will help.
I think this database would mostly serve these purposes:
- to generate customer orders and quotes
- customer history if a customer piece is lost or stolen we can recreate it.
- For valuation/ appraisal reports for customers to have for insurance purposes
- For the workshop to lookup stock items from time to time to see their labour times and weights etc
Stock Inventory is mostly managed from our online store but our database would be useful for our shop stock take only in as much as a Printed out list filtered into types like
earrings,
rings, that are YS and JB that we could tick off. I don't really think we would use it for managing stock-in and stock-out quantities as such. Like you say a small company we usually just have a manual written list of pieces we sell and look in the cabinets to see where the gaps are to replenish this once a week. It's manageable on this small scale.
I will create some basic tables and maybe work them out on paper first.
Thanks again for your kindness,
I would like to return the favour in some way,
Regards
Allison