Hey @Dwalz3,
Sounds like a fun project. At the end of the day it will depend on how robust your system needs to be, and how much granularity you need. However, I would recommend:
Accounts table (clients)
Products table (wines in your case)
Orders table (on a given date, a given client orders multiple line items)
Orders Line Items table (a given order can have one or multiple line item records, each having at least a given product and a given qty) -this would be one example of a junction table.
Depending on how often pricing chances, you can have Price at a:
Product level and have a lookup at an Orders Line Items table -however, (i) this assumes that all clients get charged the same price per unit always; and (ii) prices will never change, because if they do change the lookup at the orders line item table will be automatically changed meaning that all historic data will be messed up.
Product level but just use it as a reference for the moment the order line item comes through, and then have a Currency field where you will input (manually or automatically) the actual price being charged per unit for such line item. E.g. Reference price (lookup from Product) could be $30, so Actual Price could be also 30. If Reference price changes tomorrow to $40, your Actual Price (currency field) will be static and stick to 30. Different example, Referece price (lookup from Product) could be $30 but for whatever reason you can still charge $25 or $40 on such specific line item.
Price List table (new table not mentioned above). This one will be useful if (a) prices change often, and you want to keep a historic reference of all prices at any point in time, or (b) each client gets a different pricing logic (individually or per Client Tier for example).
For tracking certain data (e.g. wines per account, available wines, etc.) you will want to leverage lookups and rollups on your different tables.
Specifically for tracking wine availability, you will want to create not order Sales Orders (would decrease your stock/availability) but also Inbound Orders (or similar naming, to have your stock/availability increased). Specifically for wine stock/availability, you would create a rollup at a Product level, which will fetch Qty or adjusted Qty from the Orders Line Items table.
Feel free to grab a slot using this link if you’d like to go through your specific use case together. I’d be happy to show you (or any future reader) around and share with you some tips and tricks :D
Mike, Consultant @ Automatic Nation
YouTube Channel
Reckon I’d add a ‘Movement’ table that’s linked to the Accounts table and the Wine table, and each record would represent the amount that’s been transferred to an account
You could then add a rollup field to the Wine table to see how much you had left to distribute of each Wine
---
Maybe there is some way for accounts to select which wines they would like or the amounts and then receive that information back for a new total of still available wines
Interesting. Yeah if the wine info and availability is public information, you could do this pretty easily with an Interface that you set to public and a form. And so your clients would open this link and see all the available wines, then they could click on a button for that wine that would open a form and set how much of that wine they requested
---
If you could talk a bit more about your business logic that’d be very helpful for giving you advice! For example, how do you define what a top account is?
With reference to the Movement table above, if you’re letting people request wines, then you’d probably need a ‘Status’ field to say ‘Approved’ or something that would affect the rollup as well since until a request is approved it shouldn’t affect your inventory after all
