Is this a good structure? (1 Viewer)

[damonc]

Hello,
this is my first post here, but I have used many helpfull posts in the past.

I am building a DB at work, and would like some advice. The DB is used to store detials on marketing activities. A marketing item is usually (but not always) related to an agent. Some agents, have many different branches (in other countries, for example), some agents have one branch, and some have no branches.

I'm planning on using cascading combo boxes for data entry. Would that be best?

When an agent has no branches, I assume I should leave the field empty, is that correct?

I have attached my relationships window, and would like to know if I have related the tables together in the best possible way.

Also, I need to be able to report on (for example):

• Agent A - All Branches

• Agent A - Branch 2 only

Will the current design allow me to do this.

I hope this makes sense.
Thanks

 

[Steve R.]

The proposed use of combo boxes does not seem "correct". I suppose they could be used to provide values for a query.

I assume that tbl_marketingregister will be your main table. The tbl_branch should be linked to tbl_marketingregister not tbl_agent. You shouldn't need the agentID field in the tbl_branc. To recover all the branches assigned to an agent you would SELECT branchID FROM tbl_marketingregister WHERE agentID = somevalue.

 

[damonc]

The tbl_branch should be linked to tbl_marketingregister not tbl_agent. You shouldn't need the agentID field in the tbl_branc.

This seems strange, because the branch is dependent on the agent, not on the marketing item.

To recover all the branches assigned to an agent you would SELECT branchID FROM tbl_marketingregister WHERE agentID = somevalue.

How will this SELECT query work, if there is no agentID in tbl_branch. How will it know which branch belongs to which agent if there is no relationship between the tables?

 

[Steve R.]

Your going to have to read up on this. Assume that your tbl_marketingregister looks like the sample below.

rc#1      fld#1       fld#2     agentID     tbl_branch       fld #3
1           zzz        xxx        45          16             xxxx
2           sds        sdd        34          25             sdsd
3           asc        des        45          23             svsd
4           fff        dfr        45          23             dgfer

SELECT branchID FROM tbl_marketingregister WHERE agentID = 45. This will return the branchID values of 16, and 23. Since 23 exists twice, a total of 3 records will be returned.


SELECT branchID FROM tbl_marketingregister WHERE agentID = 34. This will return one record for branchID equal to 25.

You still need the tbl_branch to provide data about that branch, such as its address, phone number etc.

Potentially you can also create a TBL_Junction which would simply be a listing of the agentID and BranchID values.

 

[damonc]

Thanks ortaias,
I have been doing lots of reading, and thought I had it nailed. Oh well, back to the drawing board.

I will use your advice to try and get it working better.

 

[Steve R.]

This seems strange, because the branch is dependent on the agent, not on the marketing item.

I just noticed where you are located. We just saw "The Proposition" Sunday night. Very good movie.

In thinking about the quote above, the relationship between the agent and the branch may be temporary and subject to change (ie the agent could have one or more branches assigned to him). My suggested layout appears deficient in that the current agent/branch assignments would require "manual" linking at the time of data entry.

I will assume that it would be better to have have a table that contains the agentID and BranchID so that the agents current territory could be viewed. The junction table would accomplish this. Note> If a branch can only only only have one agent assigned to it, then your suggested layout for the tbl_branch would be OK and a junction table would not be needed.

 

[damonc]

I just noticed where you are located

Probably because I only just updated my profile

the relationship between the agent and the branch may be temporary and subject to change (ie the agent could have one or more branches assigned to him).

Note> If a branch can only only only have one agent assigned to it, then your suggested layout for the tbl_branch would be OK and a junction table would not be needed.

Correct, an agent can have many branches. But a branch that belongs to Agent A, will never belong to Agent B. Think of it like a bank: Every bank has multiple branches, but they don't share branches. Two banks can have a branch in the same suburb as each other, but the branches are specific to each bank.

After your last post, I have tried linking tbl_branch directly to tbl_marketingregister. Things seem to be working OK. But I still have agentID (fk) in tbl_branch, because I don't know how else to do it.