We help IT Professionals succeed at work.

Storing Data From Front End Page to SQL Server. What is the best approach??????????

Hi,

I have a situation where I am passing lots of data from frontend page to the SQL Server using variable type tables.

My question is should I pass actual character values as selected by users from dropdowns or check boxes to the backend to store into normalized database after using lookups to find their ids and store them into tables
OR
Should I find the Id values beforehand for the string values selected from the dropdowns or for the radio buttons and pass these Ids to store into SQL Server tables.

-----------

During the first insert of the record it adds record to Master tables so it has to populate master table then populate child tables.

Which approach is better?

Your comments and suggestions are appreciated.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

Commented:
usually what's in the drop down and being displayed is the native key or the description, but you store the key for it.

So I'd go with the second one, you also need to check whichever is easier to maintain, troubleshoot and faster.

Commented:
You should store ID as VALUE and string as TEXT in dropdown and radio button.
So whenever page is submitted directly pass dropdown and  radio button Value to SQL Server  which will contain the ID .

Author

Commented:
Hi,

Thanks for your valuable comments. I have the same feeling to use IDs from the front end to pass to the back end.

1---
But, imagine a situation where a record is going to be inserted first time in master table and then subsequent child tables. And IDs for the master records are not known. I need to pass master record string values from textboxes (Sorry forgot to mention it before). Once these are stored then I'll store child records as per the master Ids.

2---
In the meanwhile there are number of titles shown on front end are already populated in the back end master tables. Now, I am passing these titles and selected values from drop downs against these titles as character strings to stored procedure using Table Value Parameters. Once, received in stored procedure I join TableValueParameter table to master table on character values to find out their IDs and then use then use these master IDs to store record in child tables.

You can imagine the kind of situation it is now. For the 1st situation where master ids are not know I have to pass character strings to back end stored procedure to create master record. Can you please suggest is this the valid approach or is there any other option available to do it and I am not aware of it.


And for 2nd situation where master records are stored and Ids can be extracted to store in data in child tables. The approach I am using is capturing all character string values from the front end (And instead of quering database tables to find Ids of these strings  I am) storing them in a sort of denormalized TableValueParameter type table and passing it to stored procedure. Where I am joining received string values to master tables to find out their IDs

For Example.
From front end I am passing

insert into @TVP (ClientName, PortionName, SubPortionName,PortionVisibility,SubPortionVisibility,
G1Permission,G2Permission,G3Permission)
Values ('ExpertExchange','Databases','SQL Server',1,1,1,0,1)

.
.
.
.
.

Now after receiving these values in stored procedure to find out Ids;

I join
ClientName with ClientName in ClientTable and get its id and I do the same for to get all other master ids.
Once I have Ids for
ClientName,PortionName,SubPortionName I use them to store in GroupPermission tables.

Hope I have explained the my question in detail. Now what I want to know is my approach valid?

I really appreciate the time and effort you put in to read my question. Waiting for your valuable comments and suggestions.


Thanks.
Senior Software Developer
Top Expert 2009
Commented:
OK, I've used a dual approach for this issue.  First off I am a HUGE fan of normalization because it reduces database size and improves consistency.  Overall I've found performance gains due to the smaller data footprint.

For existing records to be selected I use the ID and TEXT values in drop downs.  However, in a combo box where you can add new records I normalize on the fly.  In some cases using a special stored procedure that will first look for a normalized ID, then if the ID is NULL insert the new record and retrieve it for the next step.  I avoid using an IF statement because it prevents stored procedures from being pre-compiled for better performance.

IE:

DECLARE @ID int

-- First attempt to find the ID
SELECT @ID=id FROM dbo.ClientTable WHERE name=@inName

-- If the ID is null then insert it
INSERT name=@inName WHERE @ID = NULL

-- If the ID is null and inserted, retrieve it
SELECT @ID = SCOPE_IDENTITY() WHERE @ID = NULL

Ted

Author

Commented:
good