SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 What's new with Dynamic Form Fields?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 07/11/2013 :  12:27:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/11/2013 :  12:32:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 07/11/2013 :  15:45:33  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/11/2013 :  16:23:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/11/2013 :  16:38:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 07/17/2013 :  14:21:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000