jamestieman
asked on
Show/Hide Records based on User in SQL Server
Here's the setup. I have a table with 100 records and I have 10 users. Each user can see all 100 records, but needs the ability to narrow or widen the records they actually work using something like a boolean Hide field somewhere. Also, one of the fields in the table is a description field and the users need to be able to have their own version of the description displayed on their screen.
Do they need to only view the records or also update and insert records?
Also is the record to user relationship 1 to 1 or 1 to many?
ASKER
I can't do it with security.
I have thought about copying all the records from the Master table into individual tables created for each user. Then the user table could include 2 more columns, a show/hide and a UserDescription and each user would work using their own table.
The problem is, if the Master table changes in any way, how do you keep the User and Master in sync?
I have thought about copying all the records from the Master table into individual tables created for each user. Then the user table could include 2 more columns, a show/hide and a UserDescription and each user would work using their own table.
The problem is, if the Master table changes in any way, how do you keep the User and Master in sync?
Do they need to only view the records or also perform updates and insert records?
ASKER
They can add new records to the Master table, but can't edit them. Once added others have the option to Show/Hide in their form or change the wording in the description.
Creating individual tables and trying to sync them is going to be a nightmare. You'd have to have lots of rules to describe what to do with change conflicts.
To allow the users to widen or narrow the records they see - I'd say handle that in your GUI.
To allow the users to have thier own descriptions you're going to have to create a child table that will handle many descriptions per row in your parent table.
To allow the users to widen or narrow the records they see - I'd say handle that in your GUI.
To allow the users to have thier own descriptions you're going to have to create a child table that will handle many descriptions per row in your parent table.
ASKER
Master Table of Data to Collect
LineID = 1000
Description = Vending Sales
User Table of Data to Collect
LineID = 1000
Description = Vending Sales
UserDescription = Candy Bars and Pop Sales
Sales Table
SalesID = 1
LineID = 1000
Amount = 456.34
UserID = 10
The records are written into a Sales Table.
At the end of the month, the Master totals on ID 1000 and the description is Vending Sales with the total of all Users. The user's Report would use their UserID and UserDescription for their reports.
LineID = 1000
Description = Vending Sales
User Table of Data to Collect
LineID = 1000
Description = Vending Sales
UserDescription = Candy Bars and Pop Sales
Sales Table
SalesID = 1
LineID = 1000
Amount = 456.34
UserID = 10
The records are written into a Sales Table.
At the end of the month, the Master totals on ID 1000 and the description is Vending Sales with the total of all Users. The user's Report would use their UserID and UserDescription for their reports.
ASKER
The Show/Hide is necessary because, for instance, places in Phoenix don't need to see Ski Lift Tickets on the form.
Agree with Show/Hide being necessary, but to implement it, it likely belongs in your presentation layer and not in the data layer.
ASKER
You still have to store the Boolean show hide value somewhere, right? Or am I missing something. This is what ive been struggling with.
That would involve more than I simple boolean, in my opinion. I would end up being multiple booleans for each of your users.
Instead I would look to the type of query that each user might run to narrow down the data available to them. Use filtering in your front end to achieve this through dynamic queries.
Instead I would look to the type of query that each user might run to narrow down the data available to them. Use filtering in your front end to achieve this through dynamic queries.
ASKER
What if I had a table with these fields: ID, UserID, LineID, ShowHide,Description.
The user could open a form with all 100 Master records, then select the lines they want to show and a new description or accept the Master description and write the results into this table. Then an inner join on the lineID of the Master would return only the records in this table along with any additional fields from the Master. All users would use this same table.
The user could open a form with all 100 Master records, then select the lines they want to show and a new description or accept the Master description and write the results into this table. Then an inner join on the lineID of the Master would return only the records in this table along with any additional fields from the Master. All users would use this same table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have figured out a way to only store the records that have a different description or have the hide option set. A more compllicated query will allow it to scale better.
Thanks for making me think about scalability.
Thanks for making me think about scalability.
http://forums.learnsqlserver.com/SqlServerTopic49.aspx