Promotion Database

[Nate_Alcantara]

Having trouble with some parts of my form design. I'm not sure which control to use in terms of promotion to allocation as this has a one-many relationship. How can I design it for the end-user to encode it efficiently?

[#2]

I'm afraid your going to have to explains things to us as we have no clue what you are referring to when you say 'promotion to allocation'. Please explain your table structure and existing form design and what you are now trying to achieve. Also, an image can greatly help us understand quickly.

In general terms, if I need to display a one to many relationship in a form of be using a subform.

[#3]

@Daniel_Pineault I work for a canned goods manufacturing company and one of its marketing efforts is creating promotions/trade activities. Promotion to allocation means that one promotion has multiple accounts/customers to be allocated to. In our system, our customers have address codes so I have a table for this plus a table for the promotion details.

My main problem is in terms of encoding for the end-user, how can I design the form efficiently enough that multiple accounts can be encoded per promotion form. 

[#4]

@Nate_Alcantara 

PMFJI:

Data is stored in tables. What we need to see, therefore, is the table design. Thanks.

[#5]

@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.

[#6]

@Nate_Alcantara 

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 type

I 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.

[#7]

@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!!

[#8]

@Nate_Alcantara 

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. 

[#9]

Hi @George Hepworth. Here are the summarized relationships.

1 Trade Activity = 1 Division (Essentially, under a division, there are several trade activities)

1 Trade Activity = Many Channels

1 Trade Activity = Many Areas

1 Trade Activity = Many Volume Allocations

What are your suggestions on this?

[#10]

@Nate_Alcantara 

Thanks for the details. When each trade activity is undertaken on behalf of a single division, but each division can engage in multiple trade activities, that is a one-to-many relationship: one division to many trade activities. Your current diagram shows that properly, but, as previously noted, you should enforce Referential Integrity. 

The other three appear to be many-to-many relationships. One trade activity can be conducted through many channels. One channel can be used in many trade activities, and so on for the other two.

The way we handle this is a junction table. It has at a minimum two fields. One is the TradeActivityID and the other is the ChannelID. In this junction, perhaps called TradeActivityChannel, the TradeActivityID is a foreign key to the TradeActivityID primary key in the TradeActivity table, and the ChannelID is a foreign key to the ChannelID primary key in the Channel table. In the TradeActivityChannel, these two fields together make up the primary key for that table. We usually refer to these as a Composite Primary Key. Sometimes additional fields are included, such as a DateEffective or DateEnded field if there is a time limit involved.

The same thing applies to the other two tables with many-to-many relationships.

The interface for these relationships will be a main form with subforms. Rather than try to explain it, I'll offer an example.