Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have denormalised tables in our reporting database which provides data for creating ad-hoc reports in Report Builder 3.0.

What is the best way to model the data access in Report Builder?

The main criteria for accessing data for us determines by the centre user belongs to.

For example we have a table called Students, which is linked to other tables (e.g.: Enrollments). The Students table contains all student records from 8 different schools.

Each school has got a number of admin staff that should be able to see their own student details. They should are not allowed to see other schools students and the associated records (Enrollments, Courses, Letters, etc).

I'm wondering if I should include the 'School Name' in all tables, which can be used as a filter to only return the records related to that school for a given user? I feel hesitant to repeat this column in every single table. Is there any better way of handling data access level for this scenario?

Thanks.

share|improve this question
    
I think that with a denormalized database, this will be hard to do, so most likely you will end up repeating the School Name, or better instead, the School ID, if there is any, on every table. –  Miguel Garcia Dec 2 '13 at 15:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.