Need Optimization in Database Design

Question

1.00/5 (2 votes)

See more:

I have a table applied in two different approaches:

GroupModule:
Id, GroupId, ModuleId, Permission(varchar())
1 1 1 View:true,Create:false,Delete:true,Edit:true
2 1 2 View:true,Create:true,Delete:true,Edit:false

GroupModule:
Id,GroupId,ModuleId,PermissionId

In the first example i would have create a prsing method so that i can perform authorization for a specific Group.

In Second example i have permissionid's

Since this is the most important table ,as it is used for authorization purpose, so there is a great concern for performance.

What would be the best approach in this scenario.
I have posted two other questions but i have not recieved any response, kindly let me know if you need more explanation.

Posted 3-Aug-15 1:40am

Member 9129971

Updated 3-Aug-15 2:00am

v2

Add a Solution

1 solution

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)

ZurdoDev · Answer 1 · 2015-08-03T04:08:00

Solution 2

I personally would create a table for storing your permissions. Then a table to store what permissions either a group or user has. I would not concatenate all their permissions together into a single column.

Posted 3-Aug-15 4:08am

ZurdoDev

Comments

Member 9129971 3-Aug-15 11:18am

you are probably asking for the second approach i have mentioned in my Question.
Can you tell me disadvantages of concatination over just storing data in Id form
like

GroupModule:
Id,GroupId,ModuleId,PermissionId

ZurdoDev 3-Aug-15 11:22am

Disadvantages of storing them all as a single string? It's hard to use. What if you want to add a permission? It just doesn't make any sense to do it.

Member 9129971 3-Aug-15 13:19pm

Actually i am rebuilding this system and in pervious system it is implemented as a string,also the parsing method is already built. And there is also alternate solution for addding permission , i have no issue in implementing string method except performance issue. I am just finding the best option in terms of performance.

ZurdoDev 3-Aug-15 13:21pm

Performance will depend on your server and your code so I'd still suggest doing some testing and see what works best for you.

Member 9129971 3-Aug-15 11:22am

The reason i am greatly concerned about this table is that this is the mostly used table, and it will be called on every requet. So this must be optimized as much as possible.

If we use first approach (concatination in a single column) then we have advantage of quering in a single row,although there is overhead of string parsing.
But
if we use second approach (store data in Id form) then we have to perform query that will effect multiple rows , not columns,

ZurdoDev 3-Aug-15 11:23am

You'll need to do some testing then and see what works best for you.

Ben J. Boyle 3-Aug-15 11:35am

You can also limit your query to just the permission you need to check and have a single row returned. Rather than the concatenated row that has all permissions, edit your query just to find "Does user x have edit permission?" when in an edit scenario, or "does user y have read permission?" - either would return the single row you need for the situation at hand.

In addition, you can also cache the data at the web server so that you're not constantly hitting the database.