Forum Discussion
Promotion Database
PMFJI:
Data is stored in tables. What we need to see, therefore, is the table design. Thanks.
George_Hepworth These are my table designs so far and its relationship.
I need help on how to design the form in terms of target area and allocation. One promo can be applicable to multiple areas and therefore have multiple accounts to be allocated to.
Only two tables?
I see at least three or four others that could, and probably should, be included. I hope you have at least these, plus a couple others like "Client" or "Customer".
Target Channel
Target Area
Division
Promo typeI list these because I think you target the same channels more than one. I would expect that campaigns are aimed at the same target areas repeatedly.
Divisions within your organization should be listed in a table and should Promo Types.All of these would, I believe, be required for a relational database application. You may have them, of course, and only showed these two, but to be complete and accurate we need to sure they are there.
A few notes on naming conventions and standard field types are in order, although tangential to your central question.
Names should not have spaces in them, nor should other non-standard characters be part of names. E.g. # or ?
These names are acceptable, but clumsy because anytime you have to reference one, you must enclose it in square brackets.
E.g. [Address Code]
Most long-term Access developers use of the more common naming conventions which avoid spaces and other non-standard characters.
A second, much more problematic, choice is the use of a Multi-Value field for attachments. Again, experienced developers do not use this field type. One, embedded photos in an accdb causes it to bloat needlessly. Two, they are much harder to work with in the interface. Instead, you should create a separate table for PromoPhotos. It'll have three fields, i.e. those you have in the MVF now.Okay, back to the design. I'm going to use the naming convention that avoids spaces because it is so unpleasant to try to work with them. I hope you do make that change.
You have a table called AddressCodes. I'd call it AddressCode (singular), but that's one of those things that attracts different decisions. Anyway, AddressCode implies that you are concerned with Addresses as the primary "fact" about your promomotions. I see three other fields in it, though, that really make me wonder. "ParentName", "RetailChain" and "Channel". Parent of what? Do you have a separate table of "Parents"? How about "RetailChain"? Is there a separate "RetailChain" table? If not, then both of them should be created and properly related via Primary and Foreign Key fields. Looking at "AddressName" I have to ask if you have a separate table with street, city, and state details? Or are you only interested in names of addresses, which I would imagine might be something like "The Main Downtown Mall"?
Now to the TradeActivities table. The Primary Key in it is PromoID. Does that mean TradeActivity is a synonym for Promotion? If not, how are they related? If so, why two different names?One last question that I think you probably addressed, but which I'd like to clarify. What goes into the field called "Allocation"? Is that a number, a percentage, or perhaps something else?
Thanks.
George_Hepworth Hi there. I updated my database and relationships based on your suggestions (See attached photos)
Allocation means the amount of products (in cases) to be given to our customers. Which means that 1 Promotion = Many Customer allocations. My problem is I need to figure out a way how I can encode the allocation part in the Trade Activity Entry Form I designed.
I also need help in fields that need to have multiple selections and I don't know how to do that. The two fields are Target Area and Channel Type.Thanks so much for all your help!!
This certainly looks more appropriate.
Some questions and a strong suggestion, the suggestion first. You should always enforce Referential Integrity on relationships, so Divisions, etc., which do not show that, need to be modified to show that relationship to Trade Activities.
That said, before you actually do that let's address some questions about it.
Is it true that each Trade Activity is mounted for a single Division? For a Single Channel? etc. The way the four tables on the left are set up with the join to Trade Activities, that is how it would have to be. E.g. If DivID for "Division A" is stored in the DivID field for a specific Trade Activity, that means one and only one division is participating in that Trade Activity. Is that accurate? Or, does a Trade Activity involve multiple Divisions? If the former, this is a proper design, but needs to have the relationship on DivID needs to have Referential Integrity (RI) enforced. If not, we need an intermediary table between Divisions and Trade Activities. This is usually referred to as a Junction Table. These tables include--at a minimum--two fields, the Primary Key from a Trade Activity and the Primary Key from a Division. That allows you to record one or more Trade Activities for each Division, and one or more Divisions with each Trade Activity.
Again, the decision reflects how your business rules apply.The same is true for other related tables, such as Area Coverage. If one Trade Activity is set up for one Area, this is correct as shown (with RI enforced). However, if one Trade Activity can be set up for one ore more areas, then you need that Junction Table as described above.
The same question applies to Volume Allocation. If it's a one to many (one allocation for each trade activity) the current design is correct. However, if there are different allocations for different customers in a single trade activity, again a junction table is needed.
So, the business rules determine what needs to be adjusted, if anything.