[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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.
0
jamestieman
Asked:
jamestieman
  • 7
  • 4
  • 3
  • +1
1 Solution
 
radcaesarCommented:
You can't. But as a work around you can (Not Exact one)

http://forums.learnsqlserver.com/SqlServerTopic49.aspx
0
 
hyphenpipeCommented:
Do they need to only view the records or also update and insert records?
0
 
hyphenpipeCommented:
Also is the record to user relationship 1 to 1 or 1 to many?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
jamestiemanAuthor Commented:
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?  
0
 
hyphenpipeCommented:
Do they need to only view the records or also perform updates and insert records?
0
 
jamestiemanAuthor Commented:
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.  
0
 
Chris MangusDatabase AdministratorCommented:
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.
0
 
jamestiemanAuthor Commented:
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.

0
 
jamestiemanAuthor Commented:
The Show/Hide is necessary because, for instance,  places in Phoenix don't need to see Ski Lift Tickets on the form.
0
 
Chris MangusDatabase AdministratorCommented:
Agree with Show/Hide being necessary, but to implement it, it likely belongs in your presentation layer and not in the data layer.
0
 
jamestiemanAuthor Commented:
You still have to store the Boolean show hide value somewhere, right?  Or am I missing something.  This is what ive been struggling with.
0
 
Chris MangusDatabase AdministratorCommented:
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.
0
 
jamestiemanAuthor Commented:
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.
0
 
Chris MangusDatabase AdministratorCommented:
You could implement it but it may not scale out well.  This table will contain 100 rows for each of your 10 users.  Only a thousand rows now but if you added more parent rows and more users it can scale out of control pretty quickly.

The other downside is that each user is going to have to check or uncheck up to 100 items to maintain their selection set.

If these things aren't a major consideration then I think your solution is workable.
0
 
jamestiemanAuthor Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now