Storing Anything and Everything - Any Ideas?
-
Hi,
I am having trouble working out the best way of solving a problem and I was hoping someone here might be able to help!
I am building an ASP.NET web based system in which user's will store information. Different user's will want to store different information, but once each user has decided on their list of fields, each record will be the same format. So - I effectively need to write a web based version of MS Access!!
The problem is - how best to implement this??
I thought of actually creating a new table for each user's required data format - but this would be a nightmare to manage with more than a few users.
Another thought was to have a table storing column definitions, and a separate table containing name/value pairs (field name / value) for all of the data (also storing the ID of the associated 'table' definition). From a database point of view, I'm not sure I really like this approach, but I think it could work for my needs. Each user would probably only need to store about 200 to 500 records - and there'll probably only ever be as many as 100 users. If each record has 10 fields in it - that leaves us with half a million records.
How does MS SQL Server perform with a table containing 500,000 'text' fields?
I know that I could also be making trouble for myself when it comes to retrieving the data - I'd have to do a string manipulation on each field which stores numbers or dates before doing comparisons, for example.
If anyone has any other ideas, I'd very much like to hear them!!
- Chris
-
If list of fields are pre-defined, then data type and length are known. They should not be 500,000 'text' fields.
Table with userid, fieldid (reference those pre-defined fields) and value is all needed. For value in different type, one approach is to put all data type in a row and only fill in the right one.
UserID, FieldID, IntValue, BitValue, VarcharValue, DatetimeValue ...
-
The lists of fields aren't so much pre-defined as decided on once per user, then left alone for that user.
I like the idea about the different data types in different columns - thanks!!
-
Not to be picky, but what are you trying to do? It isn't very clear (at least to me).
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It's basically for a web site where people can log in and create their own databse. It could be for cars, meetings, news items, tooth brushes etc. - we won't know what they want to store until they sign up.
We're then going to build a templated web site which will build HTML pages based on this data.
We need to keep it all nice and general so that it could be used for anything whilst also maintaining a good level of manageability and performance!!
-
Why reinvent the wheel?
I think I have seen on sourceforge.net web based administration tools for SQL Server.
Why not customized so a tool for your needs so that each customer can only access his database? Once they have finished their data model, it's your turn to do the templates.
Would be easier than writing something that generic.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply