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 2012 Forums
 Transact-SQL (2012)
 What's new with Dynamic Form Fields?

Author  Topic 

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2013-07-11 : 12:27:08
So, I do not have much experience with SQL2012, but I have the predicament of I need to have numerous different contracts with numerous different fields on each contract. This is never fun in SQL as it's not a database that is meant to thrive with these scenarios.

My thoughts on options are the following.

1. Create a table that has many generic columns with differant datatypes (field1-field30 varchar, field1-field30 datetime, etc.)

Then create a form mapping on my front end that basically allows me to set for fields to databases fields. This allows for single inserts, and good performance.

2. This option is to create a 3 table dynamic setup (I.e. doctable(Docid), DocumentFields (fieldid,docid,fieldname),documentvalues(fieldid,value).

Never like this approach as it... Sucks and requires multiple inserts and is a pain:)

3. I am not sure what else may be good, but I know sql2012 has some new dynamic based features... What's my best bet on this?

Thanks


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-11 : 12:32:26
As much as I dislike it, an XML column may be a solution for you. Otherwise, there are some other generic solutions that all have their problems (like Option #2) that you mentioned.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2013-07-11 : 15:45:33
I didn't even think of an xml column.

That may be a good approach, what do you think of this setup:

1. doctable(Docid), DocumentFields (fieldid,docid,fieldname) --As only a mapping solution, documentvalues(documentid,xml).

I'll use the front end to query the fields, then generate the xml and do 1 insert into the documentvalues column?

It is not perfect, but it may work (My concern is if I ever want to run analyses of data on the forms, is there any REALISTIC non crippling way to do that with data stored in a xml column?).


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-11 : 16:23:38
Analyzing xml data is feasible, however performance-wise it may not be as efficient as primitive data types (i.e. integer, numeric, varchar ….) organized in a tabular fashion
Here is nice introduction to working with XML data types:
https://www.simple-talk.com/sql/learn-sql-server/working-with-the-xml-data-type-in-sql-server/,

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-11 : 16:38:12
I can see a couple of ways to aproach this. If you wanted to have a (semi) ridged way of defining all the attritubes (fields) on a form. Then doing as you suggested with a Doc -> DocFields tables set would work. You could also go slightly further and define data types and acceptable vales/ranges of data too. Another possible option is to use XML/XSLT to define that. but, again, I'm not 100% sure of your goal or what the front-end needs in order to drive behavior.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-07-17 : 14:21:27
What is more important:
1) Data integrity
2) Data retrieval speed
3) Ease of inserting data
4) Cheese

Once you have that answered, it should point you down the coding path that will best achieve your goal.

Yea, i like cheese. sue me.








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page
   

- Advertisement -