DB Design Critique

[Sanguineus]

Good Afternoon All,

I've decided to take on a personal project for my wife who is starting a small one-(wo)man band business so I can develop my access skills, so I can do my day job far better/efficiently.

Upto this point I've tended to shy away from what access could probably do natively (and therefore) better in favour of using VBA (and SQL) to get what I wanted done.

I have attached the design of my database that I hope to accomplish this with and I'm hoping it speaks for it self and was hoping for some pointers on 'getting it right' so I do it the right way, moving forward.

What I was hoping to accomplish
CustomerTitles - Small table housing 'Mr', 'Mrs' etc



CustomerDiscounts - Table housing customer wide discounts (cusomers may receive X of their total order on top of other discounts and offers)

Customers - Standard table containing contact and address information (I've also linked the customerdiscount here, is that right?)

OrderDetail - An individual order (or order occurrence) storing the items orders quantity and total before discount

OrderDiscount - This table will house quantity based discounts per product (eg. buy 2, get £1.00 off etc)

Products - The products available for customers to purchase

Ingredients - The raw materials required for each product and their cost

StockControl - a table that monitors ingredients going out (sales) against a target holding level (in essence building a shopping list)

PromoCodes - my wife is toying with the idea of issuing promocodes that give an additional discount, for advertising purposes.

I'm pretty confident I could build all this with forms and vba/sql commands, but I'm strugling to figure out how to get Access to natively pull the customerdiscount from customers table for example? or calculate the total ingredient cost of a product.

[orange]

It is difficult to assess a database design without some knowledge of the business. Your tables may be exactly what you require, but without some test data and test scenarios and/or sample outputs (reports/forms..) it is hard to provide focused response.
Your tables appear to be well designed--all with PK and relationships, no spaces in field names...
I would include "AgreedToPrice" in the OrderDetails (the ProductPrice on date of Sale) which will maintain historic data. If you rely on Price from the Product table, your historic records will be affected when the Product Price is changed. I'm not sure about the OrderDiscount table, but don't know details of its intended/proposed use, but do recognize that recording ListPrice, DiscountedPrice etc is necessary for Invoicing and Sales History. I recommend you "test the model" with some sample data,scenarios and outputs. Adjust and repeat until you are sure the database design supports your needs.
Getting the tables and relationships designed to support your business is key.

Good luck with your project.


See this link for reviewing a data model info.

[RuralGuy]

I'm guessing by your Report Post that you found nothing of value in the post from Orange. Odd...

[orange]

Hi Allan,
I dropped my intended text in the original-- then saw it and did an edit to put it back in.
How's the snow???

[Sanguineus]

At the point in time I clicked that button -- I just didn't find a post with a single line directing me to an external site basically saying "ask a specific question" particularly helpful. It looked if I am being honest an attempt at sarcasm, but having now seen the post is updated can see it was just a mistake. Orange's updated post really constructive to work with, if my report can be rescinded that would be really nice.

@orange - the business while basic is essentially how made fudge making, may partner takes a few ingredients such as chocolate, condensed milk, flavourings, to make boxes of fudge nothing massively complicated nor fancy.

I will add the AgreedToPrice - that makes perfect sense and something I've obviously overlooked.

The OrderDiscountTable in my head (so quite likely both poorly titled and the wrong methodology) I think is actually linked to the wrong table and perhaps should be linked to the OrderDetails rather than Orders. Its intended purpose was to add a further *multi-buy* style discount structure

For example: if two items of the predefined type are purchase an discount of £1.00 per applicable item is applied i.e. £10 for 1, £19.00 for two. but if 10 items are purchased a greater discount of £20.00 is applied, so £80.00 (prices are made up btw.)

I am creating a form at the moment but didn't want to get too far 'down the rabbit hole' in case I had done something silly and all the work would need to deleted and re-built upon an error's correction.

I have been entering some dummy data, but as mentioned during my original question can't seem to find a way of pulling the Discount I would set in Customers.CustomerDiscount into Orders.Discount, other than using some SQL in a form (which I thought was the 'wrong' way of doing it)

Thank you for the pointers, they're really helpful.

[RuralGuy]

@Sanguineus - Now it makes sense. Thanks for the update and consider the "Report" gone.

@Orange - Howdy Pardner. Not much snow yet at this level. I've got my fingers crossed though.

[Sanguineus]

@Sanguineus - Now it makes sense. Thanks for the update and consider the "Report" gone.

Thank you for doing that.

[RuralGuy]

Just doing my job. Good luck with your project.

[orange]

Thanks all for understanding my fast finger posting.

Sanguineus - another approach that may be useful when working through/testing code is to have a message only routine that identified the business process. For example, and we used to call this "Stub processes" back when, if you have a calculation in the flow of different processes, you have a function/sub with a message that says "Process XX - calculation for Discount needed". This sort of thing helps you with process flow while not getting off topic and into the details of a calculation while refining the process flow.
You can later design and test the required calculation(s).
For such calculations, you can work with some test inputs in a sub ( a mock up of your real situation) and use debug.print to test your routine. When you get that working, then make it a function and test; then insert the function into your application. This is the old principle of breaking it into pieces and solving the piece, rather than working with real data and trying to keep all pieces together while attempting solutions.

Good luck with your project.