Re: Data Modeling: User Specific Column Retrieval

S.

Your current structure would be something like

+--------+ +-----+
|Document|>-----|User |

 +--------+      +-----+


 |-DocID         |-UserID

 |-Section1      |-...

To me, the easiest solution would be to normalise the Document table. This would mean to get rid of the repeating columns by creating a DocumentSection table. By adding a relation from User to DocumentSection, your access rules problem seems to be solved.

The resulting structure would be something like

+---------+           +----------------+
|Document |----------<|DocumentSection |
|         |        --<|                |
 +---------+        |  +----------------+


 |-DocID           |    |-DocID (pk)


 |-OtherCols       |    |-DocSecID (pk)


                   |    |-Section
              +------+  |-UserID
              |Users |
              +------+
               |-UserID

If you like, you can add a relationship between Users and Document, for those users who can see all of the document instead of just several sections. But keep in mind that such a relationship would be redundant and therefor requires extra maintanance.

Good luck,

Arjan Bos

<sriniv79_at_my-deja.com> wrote:

> Hi
>
> I have a question for modeling gurus.
>
> I have a table which stores documents, one per record. The document has
> 16 sections, each represented by a column in the table.
>
> I have several users accessing this data, each user represented by a
> userID in Users Table.
>
> Some users can access only some columns in the document table. The
> access rules are table wide generally, but would like to make them row
> specific, if possible.
>
> I need to design a schema, where  I can represent the access rules for
> each user, preferably at row level, table level would be fine for now.
>
> TIA,
> S.
>