Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2018, 06:06 AM   #16
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,720
Thanks: 64
Thanked 1,251 Times in 1,150 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: New customer management system

CJ beat me to it. When you have multiple tables, you do not help yourself.

Quote:
My question is just, how do I know from which table to pick the products?
You solve that problem by not having multiple tables. You absolutely MUST read up on the concept of "normalization" before taking this too far. Many of us will tell you the same exact thing because we know WHY we think a particular idea is good. And the idea of having multiple tables that describe essentially the same thing - pet food - is one of the NOT SO GOOD ideas.

I wonder if your problem is that you have (pardon the metaphor) dived into the deep end of the pool when you should still be working the shallow end where it is harder to get in over your head. Some serious reading on normalization and some contemplation of queries that include WHERE clauses might help.

Example: If every type of pet food has the same characteristics but is just targeted to different animals, then add one field to the table for animal type. Then a QUERY from the pet food table will list all examples of CAT food with something like this:

Code:
SELECT ProdIDCode, ProdName, ProdMaker, ProdPkgSize, ProdPrice, ProdDescription, etc FROM tblPetFood WHERE ProdAnimal = "CAT" ;
Then imagine the same query for the animals "DOG" and "LLAMA" and "WOMBAT" and "CAPE EALAND BUFFALO" (or whatever else you stock.)

You can do updates that include WHERE clauses when you get that Excel spreadsheet. All you need is to import the Excel data to a working table and then write an INSERT INTO (append) query with appropriate WHERE-type selectivity.

As to your bonus table? Consider a table with these fields: < BonusCode, BonusBuy, BonusGet, BonusDiscount, BonusOneBuy >. Then (with Bonus Code being just an arbitrary number)

<1, 5, 1, 100, TRUE> would be Discount 1 = buy 5 at one time (BonusOneBuy flag true), get 1 free (100% discount).

<2, 4, 1, 50, TRUE> would be Discount 2 = buy 4 at one time, get 1 half-off (50% discount).

<3, 10, 10, 20, TRUE> would be Discount 3 = buy 10, get another 10 (in same purchase at 20% discount) - which is the same as - buy 20 and get 10% off.

<4, 9, 1, 100, FALSE> would be Discount 4 = Buy 9 across multiple purchases (BonusOneBuy flag false). We will track your purchases. When you buy the 9th one, you get the 10th one free (100% discount).

To support the latter kind of discount, you need a transaction-based table to remember that customer's previous purchases.

If you use this kind of discount table, it doesn't matter what kind of animal you are feeding. All it matters is that you have an entry in the discount table for that particular discount scheme. And it is possible using JOIN queries to tie tables together so that internally, you are working on data records that contain everything you need to manage the operation on-the-fly.

Everybody says you want to combine tables. You are trying to split tables. But here is where you will find help: Read up on how to use queries. The reason is that when you are driving things through some kind of form, you don't ever need to actually touch a table. You can drive a form through a query. Forms aren't picky. They work quite well when given data via a SELECT query. But let the QUERY touch your table(s) to pick out what you need and filter out the rest. Let the QUERY put things together for you as well as picking them apart.

Queries are your friend.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-09-2018, 02:34 AM   #17
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,521
Thanks: 40
Thanked 3,401 Times in 3,295 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: New customer management system

one other point, if you were to have separate tables for each pricelist so you can simply 'copy/paste' - what would you do if the layout and/or format changes?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 11-09-2018, 05:47 AM   #18
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 692
Thanks: 35
Thanked 15 Times in 14 Posts
MickJav is on a distinguished road
Re: New customer management system

Hope these pics give you a few idears


__________________
If an example or something I have done has helped you please click the scales or thanks.


Examples
Calendar/Holidays Example:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Spin Up/Spin Down Example:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Find And Replace Tool:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Stop Watch Timer:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
Old 11-20-2018, 12:12 PM   #19
MarkusPalsson
Newly Registered User
 
Join Date: Nov 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
MarkusPalsson is on a distinguished road
Re: New customer management system

So I finally found some time today to work on my project. I've read about normalization as you all recomended, and I think I got the hang of it, and also I've tried to take in as much of your input as I could. I'll submit the work so far and I'd appreciate your comments on the progress.



I now have the following tables in the design:


tblCustomers (storing customer information)
tblPurchases (storing customer purchases)
tblProducts (storing product names, price, weight etc.)

tblBounsCards (storing number of purchases for each customer and product type)
tblBrands (storing brand names and what type of reward program they use)

tblRewardPrograms (storing min qty for bounus and what the bonus is)


You'll find the relations in the attached file.



DocMan said if I can't do it on paper, I can't do it in access. I've tried to keep that in mind, and I think with the tables above I can manage the system on paper. The process goes something like this.


1. Customer comes wants to register a purchase. Is the customer new, go to 2, else 3.
2. Create new customer. Go to 4.
3. Check in tblBonusCards if the customer has a bonus card of the chosen type. If yes, go to 5, else go to 4.
4. Create a bonus card in tblBonusCards and set nbrPurchases =1 and set the bonus value equal to the price of the bought product. Go to 7.
5. Increase nbrPurchases by 1 and calculate a new bonus value, then check if the required number of purchases in tblRewardPrograms is reached. If yes go to 6, else go to 7.
6. Give the customer a discount equal to the bonus value on the current purchase. Set BonusUsed flag = 1 in the purchase table. Reset counter and bonus value in bonus card table.

7. Done.


The bonus value might require further explanation. It is a number in the bonus cards table which stores the value of the cheapest product bought up until the current purchase, and this will be used to calculate the reward when the bonus card is filled.



So now I can record customers purchases over time. Do you think the current design is doable in access?


I've attached the files in a zip archive as I still don't have 10 posts. I hope you'll still take the extra time and view the files =). Thank you all!



Best Regards,
Markus
Attached Files
File Type: zip flowchart_and_relations.zip (200.3 KB, 25 views)
MarkusPalsson is offline   Reply With Quote
Old 11-25-2018, 01:10 PM   #20
MarkusPalsson
Newly Registered User
 
Join Date: Nov 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
MarkusPalsson is on a distinguished road
Re: New customer management system

No feedback at all...? I could really use a helping hand here.


Best Regards,
Markus
MarkusPalsson is offline   Reply With Quote
Old 11-25-2018, 02:33 PM   #21
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,720
Thanks: 64
Thanked 1,251 Times in 1,150 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: New customer management system

Prima facie, it appears that you have normalized your tables. You have blended all of your products into a single table, with brands and brand-based discounting. Your customer cards are covered as well.

Now, however, you will find yourself floating for a bit. The reason is that YOU are the project expert, so unless you have specific questions, you will have to work somewhat on your own. This doesn't mean we aren't here - but it does mean that in the absence of a question, we don't have an answer. (They kind of go together...)

You DID take our advice. It shows. You have started on a process for adding new or updating old customers. The details of that are up to you.

I might have played with moving the relationships around in the window to put all the "ONE" sides to the left and all of the "MANY" sides to the right, where possible. (Obviously, sometimes that is NOT possible.) The relationship drawing algorithm lets you do that and is pretty good about it. I should add: That grouping of ONE sides and MANY sides is a personal visual preference that DOES NOT indicate you did anything wrong. But if you try it, it is possible that you will see different factors about the relationships you have defined. Sometimes things like that "click" and sometimes they don't.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-25-2018, 02:56 PM   #22
MarkusPalsson
Newly Registered User
 
Join Date: Nov 2018
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
MarkusPalsson is on a distinguished road
Re: New customer management system

All right Doc Man, thanks. I just wanted to know I was headed in the right direction before I dug deeper. As I said, I'm new to this.

My first objective was to investigate wether it was possible or not to realize this project within MS access. The second was to have a solid set of tables and a solid plan before going into details, to avoid spending valuable time running in the wrong direction.
I guess the first two parts are done, so details are the next stop. I'll probably return soon with some VBA related questions, but first I'll try to do a bit of research and trial and error.



Best Regards,
Markus

MarkusPalsson is offline   Reply With Quote
Old 11-25-2018, 07:51 PM   #23
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,720
Thanks: 64
Thanked 1,251 Times in 1,150 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: New customer management system

I'll address the implied questions:

1. This IS possible within Access. No question. No doubt. But (there's ALWAYS a "but") you have to imagine it. We aren't in the business of writing it for you because only you know the ins and outs of your business model. But is it possible? Resoundingly yes.

2. By building tables and understanding how they relate to your business, you are building a solid plan to go with what you are trying to accomplish. By normalizing as many of us suggested, you curtailed that issue of "running in the wrong direction." You aren't done yet, and we all understand that "trial and error" bit. But when you have a specific question to ask, even just a "which of these X ways is better?" type of question, we are here.

I'll add a note here: You are building a model of your business in the microcosm that is MS Access. You are abstracting properties, values, and methodology into your computer corresponding to the real world business model that you would have used if all you had was ledger books and accountant's analysis pads. Just remember that if you ever come into a conflict between your business and your Access model - the business is always right. You NEVER let the model run your business. You only want the model to TRACK your business. Never let the tail wag the dog.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
library management system mrrul3z General 1 11-26-2010 09:16 AM
Stock Management System mis General 20 01-25-2010 03:48 AM
Order management system garywood84 General 11 12-22-2007 05:33 PM
Using Access for Customer Management? dalesellers Forms 0 10-05-2006 05:43 PM
CD Management System Slate General 1 04-29-2003 02:06 AM




All times are GMT -8. The time now is 01:51 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World