Forum Discussion
Promotion Database
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.
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.
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?
