| Author |
Topic |
|
TroyDotNet
Starting Member
9 Posts |
 Posted - 2006-09-26 : 15:53:57
|
| Hello All,I've been tasked to devise a good way to enable multiple clients to indirectly add fields (preferably of various data types) to a table. For example, I have a table, tblInvestors, that contains typical fields such as name, address, phone, etc. This table is used to hold data for various clients' data. Clients need to be able to add user-defined fields, such as weburl (varchar), investment_start_date (date), IsGeneralPartner (bool), acct_balance (money), etc. to this table. There needs to be a way to track the name of the field for displaying on an ASP.Net 2.0 page (e.g. "Email:"), the data type (e.g. varchar), and perhaps a regular expression parser to validate the data.One idea that was proposed to me was to add a new column to the table for each new client field (with NULL inserted in that field for other clients' records). Thus, if 5 clients each add 3 new fields, the current 10-column table grows to 25 columns. Ugh.Another idea was to not use a table like tblInvestors but use a look-up table that stated which clients use which fields, and use a simple table that contains the actual value (along with clientID and lookupFieldID, or similar). For example, if client # 10 wanted a field (#12) to be an email address, the data table would contain field values of 10 (clientID FK), 12 (fieldID FK) and "myaddress@me.com"). I suppose all fields would be stored as varchar and converted to something else in the C# Business Logic Layer.I would be most grateful to hear your proposed solutions.Thanks, and take care. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-26 : 17:40:19
|
| You definitely do not want to even consider the first option!The second option is not uncommon, and would be the way to go. In your client values table you can have a column to indicate data type and then have C# code for dealing with the varchar data. You can also have a few different data type columns in the values table, so say one varchar with long enough length to hold any character data, an int for any integer data, a date time for any dates or times etc. If you do that you still have to figure out which column to put values in so decide whether that is easier than converting to varchar or not.Another option is to make the value binary and then serialize the C# types.Another option if you're using SQL Server 2005 is to use CLR integration and create user-defined types (which essentially use binary serialized data, but make it more friendly to the developer).Part of the decision about storing the data will be based on how you index it, if at all. If you only ever look up values based on the clientID and lookupFieldID then you don't have to index it, but if you're going to start searching that data you'll need to index it (regular or full-text) and then making it all varchar might make the index more useful. |
 |
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-27 : 08:15:53
|
quote:
The second option is not uncommon, and would be the way to go.
I like the option of making all data varchars in the client values table. While not ideal it is certainly doable in a timely fashion.quote:
Another option if you're using SQL Server 2005 is to use CLR integration and create user-defined types (which essentially use binary serialized data, but make it more friendly to the developer).
I find your third option regarding CLR integration & UDTs intriging. I'll tap into Google to see if I come up with any good leads. Have you any suggested links to sites where I can learn more about this option?Thanks for your timely & informative reply.MSSQL Server 2005 ExpressMS VS2005 ProSubversion/TortoiseSVN |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-27 : 10:25:30
|
| I created a ComplexNumber UDF via a tutorial on http://www.codeproject.com/cs/database/UserDefDataTypes.asp and am using it in test queries. Currently, I am perusing the links you provided. Then I will try using it via a C# class to see if the real-world usage works like I expect it to.Thanks again for your expert help. I never would have gone in this direction without your guidance.Troy |
|
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-27 : 11:19:22
|
| I've run into a small glitch due to ignorance. I created a UDF (ComplexNumber from the tutorial) and added a table field of that type. However, when I try to open (view the data within) the currently-empty table (via VS2005 Server Explorer), I receive the following error popup:SQL Execution ErrorExecuted SQL statement: SELECT id, cnumber.ToString() AS cnumber FROM Table1Error Source: .Net SqlClient Data ProviderError Message: Execution of user code in the .Net Framework is disabled. Enable "clr enabled" configuration option.The same error message pops up when I try to perform a " SELECT id FROM Table1 " query (id being an integer).I already used the " sp_configure 'clr enabled', 1 GO RECONFIGURE GO " sequence on the server (before creating the db).Any thoughts? Gracias. |
|
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-27 : 11:38:24
|
| Nevermind--as usual a little more experimenting yielded a solution. Creating the database within MSSQL2005 Management Studio did the trick, though I'm not sure why doing it within VS2005 Server Explorer failed.In any case, all is well in development land. Now, I'll try using the UDF via a C# project. |
|
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-27 : 14:58:01
|
| UDT via CLR integration is pretty neat stuff. I've had fun learning something new.Now for the Jethro question: how does the use of UDTs help solve my original programming dilemma? Essentially, how would the data table be structured?Perhaps your meaning is to make a single UDT that is capable of handling any data type (int, bool, varchar, money etc). I'm not seeing the elusive Big Picture.Thanks for your time. |
|
|
|
TonyAlicea
Starting Member
2 Posts |
Posted - 2006-09-27 : 18:22:00
|
| If you are using SQL Server 2005, I have overviewed yet another possible methodology (using dynamic SQL and the PIVOT statement) on my site:[url]http://www.theabstractionpoint.com/dynamiccolumns.asp[/url]If you are using SQL 2000, this could be adjusted to generate CASE statements rather than the PIVOT statement.Tony Aliceahttp://www.theabstractionpoint.comclarity of mind and creativity in application software development... |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-27 : 19:26:12
|
quote:
how does the use of UDTs help solve my original programming dilemma?
As you figured, I was referring to your original question quote:
I suppose all fields would be stored as varchar and converted to something else in the C# Business Logic Layer.
Using UDTs would be an option rather than storing everything as varchar and converting, you'd store everything as your UDT which may make the converting to something else simpler. |
|
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-28 : 09:52:40
|
| A possible alternative I've come across is the sql_variant data type. I'm still experimenting with its implementation but it shows promise. I'm surprised no one mentioned this possibility (perhaps everyone but me knows its not feasible for my needs). |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-09-28 : 11:20:03
|
| Microsoft has some pretty heavy suggestions regarding CLR UDTs. In essense, the suggest you not use CLR UDTs in cases where you would ever need query a single property of the object. CLR UDT is not meant to make SQL into an OODBMS, by stretch. There are M$ papers on this, I'm too lazy to look them up for you ...EDIT: Anyone out there in SQLTeamLand have a CLR UDT running in production? What are your experiences?I would recommend you create a data model that actually models the real world (with regard to your business case). In the financials verticle there are many standards bodies that provide data models and XSDs that cover a variety of senerios. I'm familiar with ACCORD and NAILBA ... I'm not sure exactly what space you are in, but there are likely standards. I recommend you see what they have to offer.If you refuse my advice, I would look at an XML datatype for your "variable" data; however, at a minimum I would create an XSD so as to maintain some control over what you've got.The CLR UDT stuff is interesting, I've been down that path ... but in my experience it is a dead-end when it comes to real implementation.Jay White |
|
|
|
TroyDotNet
Starting Member
9 Posts |
Posted - 2006-09-29 : 14:30:15
|
| I agree, UDTs don't seem to be the way to go, at least in this instance.I am experimenting with using the sql_variant data type in conjunction with a table (tblInvestorFields) that defines, for each UDF, the datatype, label text, default value, min value, max value, regular expression, description, and is_active flag.I'll post the results here when finished in case someone searches for this topic.Troy |
|
|
|
|
Adding "Dynamic" Table Fields