Can anyone suggest me a table structure for the following type of requirement?

Question

0.00/5 (No votes)

See more:

I have a requirement such as every user is assigned some company branch location such as A,B,C and is also assigned account_type such as D,E,F,G,H. There is a table named customer codes which contains the customer_code name, code etc along with the branch which maybe A or B or C or All and account type which may be D or E or F or G or H or ALL. So my goal is that when USER 1 logs in he should only be able to view the customer codes, name associated with branch (A or B or C) and account_type (D or E or F or G or H or ALL).

I have a table structure in mind such as

User_branch_act_type

---------------------------------------------------
Sr-no   |   Username  |  Branch  |  Account Type  |
---------------------------------------------------
1       |   User 1    |  A       |   F,H          |
1       |   User 2    |  C       |   D,E.F        |
1       |   User 3    |  A,B,C   |   E,H          |
1       |   User 4    |  B,C     |   G,E          |
---------------------------------------------------

Customer Codes

--------------------------------------------------------------------------
Sr_no   |  Customer Name  |  Customer Codes  |  Branch  |  Account_Type  |
--------------------------------------------------------------------------
1       |  Customer 1     |   566            |  B       |  D             |
1       |  Customer 2     |   545            |  A       |  G             |
1       |  Customer 3     |   512            |  C       |  E             |
1       |  Customer 4     |   589            |  A       |  H             |
1       |  Customer 5     |   537            |  B       |  F             |
--------------------------------------------------------------------------

So for instance User 1 searches for the code of the customer he should only be allowed to search for the customers whose branch is A i.e Customer 2 and Customer 4 and at the same time he should only be able to view the customer codes whose account type is F or H. Therefore User 1 should be able to get the customer code of Customer 4.

Any advice is appreciated. Is the table structure feasible enough to retrieve the data or should I modify it? Thanks in advance.

Posted 23-Mar-14 20:27pm

Ankit Chaturvedi

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Accepted Answer · 2014-03-23T20:32:00

Solution 1

Generally avoid encoding data with commas into columns, create a details table instead this will make you life easier and queries much simpler.

Posted 23-Mar-14 20:32pm

Mehdi Gholam

Comments

Ankit Chaturvedi 24-Mar-14 2:36am

I understand encoding data with commas is not a good approach but can you be a more specific like what do you mean by Details table? Sorry I didnt get you. A little about columns names would be good. Thanks

Mehdi Gholam 24-Mar-14 2:42am

tblUserBranch : id, idUser, Branch
tblUserAccount: id, idUser, AccountType

Sergey Alexandrovich Kryukov 24-Mar-14 2:47am

5ed. It looks like I wrote my answer (please see) at the time you added this comment. Before you did, I would consider your answer as not clear. Frankly, if I asked this question, I would hardly understand what to do, but your last comment clarified that.
—SA

Mehdi Gholam 24-Mar-14 2:51am

Sadly sometimes I incorrectly assume the users know the basics of table design :)

Sergey Alexandrovich Kryukov 24-Mar-14 3:02am

It happens. In this case, if OP understood table design well (which is not such a trivial art), he wouldn't ask a question on such an elementary problem. Besides, I don't think the term "detail table" is absolutely definitive...
—SA

Sergey Alexandrovich Kryukov · Accepted Answer · 2014-03-23T20:43:00

Solution 2

This is a problem of many-to-many relation. For example, you can create a separate table "user-branch associations" with two foreign keys, one referencing a user, another one — a branch. Same thing about account types: a table "user-account type associations".

—SA

Posted 23-Mar-14 20:43pm

Sergey Alexandrovich Kryukov

Comments

Mehdi Gholam 24-Mar-14 2:50am

yes, 5'ed

Sergey Alexandrovich Kryukov 24-Mar-14 3:00am

Thank you.
—SA

Peter Leow · Accepted Answer · 2014-03-23T20:51:00

1. Remove the account type from the 'user_branch_act_type' table
2. Create another table say 'user_account' like this with Sr_no and Account type forming the composite key
table: user_account
Sr_no, primary key
Account Type, primary key
3. link the 2 tables together thru Sr_no
4. link 'user_account' table to 'customer codes' table thru account_type fields

Do the same for branch column.