|
|
Posted by Jerry Stuckle on 09/28/98 11:29
Pat Farrell wrote:
> Jerry Stuckle wrote:
>
>>J Mox wrote:
>>
>>>The way I have currently coded it I would not need to change any more
>>>code to add or edit a permission either way and yes the way it is
>>>currently setup each user would need to be assigned 100 permissions with
>>>values of either 1 or 0. So I guess it comes down to which type of query
>>>would return a single users permissions faster.
>
>
>>Read up on database normalization.
>
>
> PMFJI, but if the OP cares about performance, normalization is not usually
> the solution. Normalization is about eliminating errors and update
> anomolies. And for flexibility and decoupling of code that uses the
> tables.
>
> If you want fast, use bit flags and store 30 per integer.
> It will make the code that uses it ugly and hard to maintain, but
> getting three integers per select is tons faster than doing a join.
>
>
>
"... not *usually* this solution...".
When you understand normalization, you can spot the rules - and the
exceptions to the rules.
In this case, having everything in 101 columns is a complete waste of
performance - typically a database will have to read all 101 columns,
even if it only returns one or two columns.
In this case, three columns with an index on the userid and code can get
the database to the exact record quite quickly.
Your way can save minimal time - and assumes each permission is only
"yes" or "no". If it were, there would be no need to even have the "no"
values in the database. And not only would it be a nightmare to
maintain, it would be even harder to expand in the future.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|