Posted by Jerry Stuckle on 09/28/14 11:29

J Mox wrote:
> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
> news:ScadneW-KaTHxc_eRVn-pw@comcast.com...
>
>>J Mox wrote:
>>
>>>"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
>>>news:0-OdnWh8dc2_IszeRVn-sQ@comcast.com...
>>>
>>>
>>>>J Mox wrote:
>>>>
>>>>
>>>>>"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
>>>>>news:wNKdnTqy45p3KMzeRVn-ug@comcast.com...
>>>>>
>>>>>
>>>>>
>>>>>>J Mox wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hi, I am creating a user name system in which each user is assigned 100 different types of
>>>>>>>permissions. Is it better to create a table with one row for each user and 101 columns (1 for
>>>>>>>userid and 100 for permission values) or 3 columns (1 for userid, 1 for permission id, one for
>>>>>>>permission value) and then 100 rows for each user or something else?
>>>>>>
>>>>>>Two tables. First one has userid and password. Second one has userid and a permission value.
>>>>>>
>>>>>
>>>>>
>>>>>Right but in the 2nd table, is it better to have 3 columns and 100 rows for each user or 101
>>>>>columns and one row for each user?
>>>>
>>>>Ok, I misread the question. Three columns. Userid, permission code and permission value.
>>>>
>>>>101 columns is inflexible. How much code would you need to change if you had to add another
>>>>permission code?
>>>>
>>>>And you probably won't have 100 permission codes for every person, will you?
>>>>
>>>
>>>
>>>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.
>
>
> Thanks but I don't see how further normalization of either table structure would result in the
> fastest retrieval of a users permissions. Correct me if I am wrong but after reading up on it my
> understanding is the only way to further normalize either table structure would be to create 100
> permissions tables (1 table for each permission) each with 2 columns (1 for userid and one for
> permission value) resulting in a single row for each user in each table and doing a query that joins
> values from all 100 tables. This seems like it would be slower than querying a single table for a
> single row - even if it does have 101 columns OR querying a single table for 100 rows - but only 3
> columns.
>
>

Yes, but *understanding* normalization will help you.

I suggested you use three columns - userid, code and value. You don't
seem to like that answer - at least you want to argue with it.

So I suggest you try to *understand* normalization. Then the answer
should become more obvious as to *why* this way could be better.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]