various different clients of the application. For example, consider an
application that comes with a "standard" order form, that perhaphs consists
of 10 fields that we know ahead of time, orderID, Product Name, Product ID,
etc.
Now consider that the application is delivered via an ASP model, and that
clients we sell the software to, wish to customize the order form to thier
personal liking. In this scenario, they may want to only use say 7 of the
standard fields, but they have 7 additional fields that are specific to
themselves. They may have an internal ID field, or pricing fields that are
specific to themselves.
Every client who subscribes needs to be able to customize the order "form"
accordingly. so if our application is used by 100 clients, we would have
potentially 100 versions of the order form, with totally custom questions.
Of course, I don't think we would want to create 100 versions of the table.
that would be a maintenance nightmare, and be very difficult to program
against.
There is another technique which would use a LOT of meta-data to allow the
dynamic capture of the order data via the form, but this doesn't work at all
when it comes time to report on that data.
My question is, does anyone have previous experience in modeling a database
to deal eligently with these types of requirements? I would greatly
appreciate anyones experience with similiar situations and hear what has and
has not worked.
thanks!Hi Mike
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information provided, you have standard fields ( all the customer
are same) and custom fields. To design the database to save all the
information, could you decribe more clear of what the customer fields might
be?
What I mean is, what is the flexibility level of the customer fields: Could
the customer specify the number of the custom fields? Is the customer could
specified a certain range of fileds with fixed datatype or as many fields
and data type as they want?
If they could just have certain custom fields and known datatype, you could
just have one table with standard fields and all the possible fields. If
the customer choose to enter the data in his specific fileds, the data will
be saved in corresponding fields; If not, the columns will be NULL.
Looking forward for you reply.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong, thanks for your reply.
Let me take a step back and give you the bigger picture. I was using the
Orders table as an example of what we do, however, what we currently offer
is the ability for our clients to define ANY "Form", define all the fields,
what types of fields they are, and also what the input type would be
(dropdownlist, checkbox, radiobutton, etc).
Currently, we are using a strict meta-data format for capturing all the
information necessary to create the "Form" which is generated as a web page.
We have a "formtypes" table that defines a formtypeID, and then a related
"questions" table that holds the specific questions and the definition of
those questions associated with a specific FormType. The question could be
defined as numeric, or date, or text, etc. When a "Form" is submitted, it is
writes one record to a "forms" table that is an instance of that form, which
generates a formid, and then we write each response to a "responses" table,
with the corresoponding formid, and questionid. So every single response
from every single form is actually written to this one table. This has
become much too complex, and a HUGE problem is the inability to be able to
report on this data...since it's entirely custom and we don't know what they
are actually capturing.
We are attempting to look at all the formtypes and questions and create a
Taxonomy ...basically pull out "standard" data that all the clients are
asking and put that data into "real" tables. That will be piece of work.
however, we still need to allow clients to add custom fields on top of what
we offer as standard. The goal is if we can map the data they want to
capture into real fields, we will be able to much easier search on that
data, and also easily report on it. The above data model does well for
capturing purely custom data, however I was hoping for a better format that
would allow us to simplify the capture of this data, and also allow
searching and potentially reporting.
We are redoing are data schema, which is why I'm revisiting this question
and seeing if others have had to deal with this level of complexity. One
potential solution I was thinking about was using the new "Yukon" XML field
type to hold an instance of a submitted form. The form could be serialized
into XML, and could have totally different schemas and still be stored in a
single field. Based on what I read, that data can then be indexed, and
actually searched in combination with relations queries. In addition, I read
something about "promotion", which can let you "promote" specific nodes to
be written into a relational field...which could be a solution for allowing
us to have a mechanism to report on key pieces of data.
We are at the early stages of this work, so all options are open to us. Hope
this gives you a better feel for the complexity of what we are trying to
solve, and I look forward to any suggestions or ideas on the "best
practices" for dealing with this specific type of data modeling problem.
Thanks,
Mike
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:FWwU1cj6DHA.2768@.cpmsftngxa07.phx.gbl...
quote:
> Hi Mike
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information provided, you have standard fields ( all the customer
> are same) and custom fields. To design the database to save all the
> information, could you decribe more clear of what the customer fields
might
quote:
> be?
> What I mean is, what is the flexibility level of the customer fields:
Could
quote:
> the customer specify the number of the custom fields? Is the customer
could
quote:
> specified a certain range of fileds with fixed datatype or as many fields
> and data type as they want?
> If they could just have certain custom fields and known datatype, you
could
quote:
> just have one table with standard fields and all the possible fields. If
> the customer choose to enter the data in his specific fileds, the data
will
quote:|||Hi Mike
> be saved in corresponding fields; If not, the columns will be NULL.
> Looking forward for you reply.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As for saving various types of data in one table, based on my knowledge, it
is hard to do it for the data in SQL Server 2000 database needs regular
storage format. Also, to create one table for one customer would not
practical for the maintenance and developing work would be HUGE. XML would
be a choice, you could use XQuery/XPath expressions on the XML Datatype to
extract and search data and also to get data into a computed column that
can serve as a way to promote information from the XML Datatype instance
into the relational context. Actually, you could ask this question in the
newsgroup in the related newsgroup and our corresponding engineers there
would answer your question about it. The newsgroup would be:
http://support.microsoft.com/newsgr...&NewsGroup=micr
osoft.public.sqlserver.xml
or
news:microsoft.public.sqlserver.xml
Thanks for your post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
No comments:
Post a Comment