problem in removing loop

[#1]

HI All,

Please have a look at the attached diagram

Client table has branchid and deskid. desk table has deskid and desk name and branches table has branchid and branch name. I have removed the branchid from client table at the moment to avoid circular loop. All clients have branchid but some clients do not have deskids. Therefore, we cannot determine branch name for those clients using the link through deskid  client---->desk------->branches. The other option is to connect client table directly to branches table using branchid, but this will create a circular loop. [client------->desk------>branches------>client]

The other solution that I can use is to combine desk and branches table, and use branchid and deskid as combined key in both tables. the combined table of desk and branches have all values for desks and branches. However the combined values for those clients who have null desk will be different, and again we cannot fetch branch name for them using combined keys as the combined branch/desk table does not have any branch and null desk combination. Can someone guide me how to resolve this situation

Regards

Arif

[m_woolf]

Can you rename the BranchID field in the Desk table so the link will be from client to branches only?

[#3]

Thanks a lot for your response. I also thought about that, but that will break the relationship between desk table and branches table because client table does not have all deskid from Desk table. I need all values of desks and branches in listboxes, the link between them. The customer wants that if he select a branch, only relevant values should appear in desk list boxes, moving branches to client table will break that relationship because of missing deskids from client table

Regards

Arif

[m_woolf]

If you duplicated the BranchID field in the Branches table and named it something like BranchDeskID and did the same in the Desk table, then you would have the link that you need for those 2 tables.

[#5]

Do you mean I should duplicate branch table. One connects with desk table and the other one with client table?

Arif

[Anonymous]

istead of maintianing 4 tables just maintain only two tables . otherwise send us the complete screen shot with some sample data if possible.  Will send you a sample application

[m_woolf]

No, not the table, just the BranchID field. Something like this:

Load

     BranchID,

     BranchID as BrachDeskID,

     other fields as needed,

from Branches

and in the Desk table

load

     BranchID as BranchDeskId,

     other fields as needed

from Desks    

[#8]

I forgot to mention that branch has one to many relationship with desks. One Branch has many desk while one desk belongs to only one branch. In the above link, it is hard to get a specifc desk using branch table as a link

Regards

Arif