SQL Server Performance Forum – Threads Archive

Fields that must be hidden or displayed in web app

Hi, I have a â€˜Users#%92 table in which I have a set of fields that I want the user decides if show them or not in the web application. How can I manage this in the db? Perhaps create another table, for example: Table name: data_visibility
Data_v_id = 250, 251, 252, 253
User_num = 47, 47, 47, 47
Data_name = State, City, Address, Zip_code
ShowHide = 1, 1, 0, 0 So, this means user 47 want to show State and City, and want to hide Address and Zip_code. But then, how can I write the SP so that only returns to the app State and City?
Select @State_cont = State_cont, @City_cont = City_cont, @Address_cont = Address_cont, @Zip_code_cont = Zip_code_cont From Users As us JOIN data_visibility As d_vis
On us.User_id = d_vis.User_num Where ??Â¿Â¿ Thanks

Hi, Your data_visibility table
25047State1
25147City1
25247Address0
25347Zip_code0 The query
SELECT
d_vis.data_name,
d_vis.showhide
FROM Users AS us
JOIN
data_visibility AS d_vis
ON us.User_id = d_vis.User_num
WHERE us.User_id = @User_ID
AND d_vis.ShowHide = 1 the result: State1
City1
you now know which option to set enable or not in your web application.

Hi again, In â€˜data_visibility#%92 table I have also other records that are from other parts of the website, and in this case I only want to return â€˜data visibility#%92 for a specified page. So, I don#%92 t want to return all the records of a user for (d_vis.ShowHide = 1). Instead I would want to return something like this: (I don#%92 t know if it works, I explain my idea to ask your opinion) USE market52
GO
CREATE PROC show_data
@User_num bigint, @Obligatory_field1 smallint output, @Obligatory_field2 varchar(50) output, @Obligatory_field3 int output,.., @State_cont smallint output, @City_cont int output, @Adress_cont varchar(50) output, @Zip_code_cont char(9) output,..
AS Select @Obligatory_field1 = us. Obligatory_field1, @Obligatory_field2 = us. Obligatory_field2, @Obligatory_field3 = Obligatory_field3,… ,
@State_cont = Case
When d_vis.data_num = 1 And d_vis.ShowHide = 1 Then us.State_cont Else Null End, @City_cont = Case
When d_vis.data_num = 2 And d_vis.ShowHide = 1 Then us.City_cont Else Null End, @Adress_cont = Case
When d_vis.data_num = 3 And d_vis.ShowHide = 1 Then us.Address_cont Else Null End, @Zip_code_cont = Case
When d_vis.data_num = 4 And d_vis.ShowHide = 1 Then us.Zip_code_cont Else Null End,.. From Users As us Left Join data_visibility As d_vis
On us.User_id = d_vis.User_num Where us.User_id = @User_Id GO Then in the web application I will write something like this: If @State_cont Is Not Null Then
â€˜Show user State contact information
Else
â€˜Not show this data
End If If @City_cont Is Not Null Then
â€˜Show user City contact information
Else
â€˜Not show this data
End If etc,.. Thanks

Hi, so you have to select some data_num depend on the web page the user is. then you need to know what data_num you have to select depend on the web page, so you need to keep this configuration in any place, for example a table. now you’d have four tables: 1. user (user_num, name, city, state…) 2. data_visibility (data_v_id, user_num, data_num, showhide) 3. data_num (data_num, description..) 4. webpage_datanum (webpage_id, data_num) <pre id="code"> user table: 47JohnZaragozaSpain 48Karen BarcelonaSpain data_visibility table: 2504711 2514721 2524730 data_num table: 1city 2state 3address webpage_datanum table: 0011 0012 0021 0022 0023 SELECT (what you need, using case… o whatever) FROM user a INNER JOIN data_visibility b ON a.user_num = b.user_num INNER JOIN data_num c ON b.data_num = c.data_num INNER JOIN webpage_datanum d ON c.data_num = d.data_num AND d.webpage_id = @webpage_id WHERE a.user_num = @user_num </pre id="code"> this could be a solution <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

This looks very good <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, I think I will implement it. By the way, instead of â€˜webpage_datanum#%92 table as you said, what about this?: â€˜webpage_datanum#%92 table: web_page_id (PK) web_page_description And then â€˜data_visibility#%92 table: Data_v_id (PK) User_num (FK) Data_num (FK) Web_page_num (FK) ShowHide (FK) In this way we eliminate the need of two joins, and only would be: SELECT b.Data_num, b.ShowHide FROM user a INNER JOIN data_visibility b ON a.user_num = b.user_num AND b.Web_page_num = @webpage_id WHERE a.user_num = @user_num What do you think? Thank you

Hi, Yes, it’s better. Good remark.

]]>