Fields that must be hidden or displayed in web app

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fields that must be hidden or displayed in web app

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-23 : 12:32:41

Hi,

I have a ‘Users’ 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 and where can I put these fields?
Perhaps adding a column for each field the user wants to hide/show, and putting in these new columns 1 or 2 value depending if must be displayed or not? Or making this in a separate table?

The fields I want the user can decide to show or hide are these (in bold):


CREATE TABLE [Users] (
	[User_id] [bigint] IDENTITY (1, 1) NOT NULL ,
            ...
	[State_num] [smallint] NULL ,
	[City_num] [int] NULL ,
            [Phone] [char] (9) COLLATE Modern_Spanish_CI_AS NULL ,
	[Fax] [char] (9) COLLATE Modern_Spanish_CI_AS NULL ,
	[Contact_name] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Contact_last_name] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Address] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL ,
	[Zip_code] [char] (15) COLLATE Modern_Spanish_CI_AS NULL ,
        [Contact_schedule] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Company_or_private] [smallint] NULL ,
	[Company_name] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL ,	
	[Web_page] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL ,
	[Activity_description] [varchar] (500) COLLATE Modern_Spanish_CI_AS NULL ,
     [State_cont] [smallint] NULL ,
	[City_cont] [int] NULL ,
            [Pone_cont1] [char] (9) COLLATE Modern_Spanish_CI_AS NULL ,
	[Pone_cont2] [char] (9) COLLATE Modern_Spanish_CI_AS NULL ,
            [Contact1_name] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Contact2_last_name] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Address_cont] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL ,
	[Zip_code_cont] [char] (15) COLLATE Modern_Spanish_CI_AS NULL ,
	[Contact2_schedule] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[User_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Users_Cities] FOREIGN KEY 
	(
		[City_num]
	) REFERENCES [Cities] (
		[City_id]
	),
	CONSTRAINT [FK_Users_Cities1] FOREIGN KEY 
	(
		[City_cont]
	) REFERENCES [Cities] (
		[City_id]
	),
	CONSTRAINT [FK_Users_States] FOREIGN KEY 
	(
		[State_num]
	) REFERENCES [States] (
		[State_Id]
	),
	CONSTRAINT [FK_Users_States1] FOREIGN KEY 
	(
		[State_cont]
	) REFERENCES [States] (
		[State_Id]
	),
	CONSTRAINT [FK_Users_User_Type] FOREIGN KEY 
	(
		[Company_or_private]
	) REFERENCES [User_Type] (
		[User_type_id]
	)
) ON [PRIMARY]
GO

Ideas/suggestions?

Thank you,
Cesar

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 00:48:21

You could have another table with columns:

TableName
ColumnName
ShowHide

and read all the values from that into your application and then conditionally display the columns according to what the ShowHide value is set to.

Kristen

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-24 : 03:30:01

Hi,

Something like this? 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 ??¿¿

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-24 : 08:18:19

Create the query dynamically, after checking the data_visibility, with the corresponding parameters (Need to query that table), in the SP.

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 08:38:14

"But then, how can I write the SP so that only returns to the app State and City?"

We return the optional data always, but our ASP then does not display it

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-24 : 09:08:34

The WEB APPLICATION should handle what columns to hide or display.

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-24 : 09:54:13

Ok, but how the app can know the user preferences in my example? In that case: Show State and City and hide Address and Zip_code?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-24 : 10:48:47

Application can query the data_visibility table and based on the results, it can decide what to show & what not to show

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 13:42:18

Srinika: Exactly that!

cesark
Posting Yak Master

215 Posts

Posted - 2006-01-25 : 04:09:07

quote:
Application can query the data_visibility table and based on the results, it can decide what to show & what not to show

quote:
Srinika: Exactly that!

What do you think about this system to query 'data_visibility' table so that the web app can decide what to show and what not to show?:

(I don’ 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,..

What do you think?

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources