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.
| 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. |
 |
|
|
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 |
|
|
|
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/, |
|
|
|
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. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-07-17 : 14:21:27
|
What is more important:1) Data integrity2) Data retrieval speed3) Ease of inserting data4) CheeseOnce 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 |
|
|
|
|
|
|
|
|
What's new with Dynamic Form Fields?