Dynamic Field DB Schema Brainstorming

Hello -
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||

Thanks for the resource, good stuff - organized nicely. I was looking specifically to my example, so I didn't plow through an ERD of what's already out there, thankx.

|||A relational database sounds like a completely inapropiate tool to build this application you're looking to build. When you are using a RDBMS, you need to know what data you are modeling. You can't just try to make it up on the fly.
You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||

Sounds like you're both directing me the same way - a relatively small DB schema that holds the forms, the fields, and descriptive text for the fields, etc. The actual field types, values, javascript, etc... should be in a bunch of files that describe each fieldID, most likely in XML.

Thank you for that. We were thinking about compiling a bunch of .JS files for each field, and then one large.js file for the form aggregate fields. But XML would make more sense for the fields' descriptive data I believe, no?

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techin...scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW design question - Dimension for currency

Hi everybody,
I struggle with a DW design issue that I hope somebody can provide a
solution for?
I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.
For the purpose of this question, the layout is the following:
Dimension tables:
Time: Transaction date, week, month, etc.
Product: Product Category, Product Line, Product.
Customer: Customer, Customer Segment, etc.
Fact table:
Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.
Requirement:
I am looking for sales by product by customer/product etc. (This is not a
problem). However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).
One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.
Solution so far:
So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.
I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.
So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?
Look forward to hearing from you.
Best Regards,
Bigalexx
Hi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/default...dvisoryService
Outside of the US/Canada:
http://support.microsoft.com/default...rnational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com

DW design question - Dimension for currency

Hi everybody,
I struggle with a DW design issue that I hope somebody can provide a
solution for?
I have a traditional Star Schema, and are building an OLP cube on top. The
development is taken place using SQL Server 2005.
For the purpose of this question, the layout is the following:
Dimension tables:
Time: Transaction date, week, month, etc.
Product: Product Category, Product Line, Product.
Customer: Customer, Customer Segment, etc.
Fact table:
Sales: Transaction date, Customer, Product, Price, Currency, price in
currency, Exchange rate.
Requirement:
I am looking for sales by product by customer/product etc. (This is not a
problem). However, each of the sales transactions is made in a particular
currency. For example is sales transaction 1 in Euro and transaction 2 is in
British Pounds. Both transactions are for Product A. On top of the existing
dimensions, I need to see each of the sales transactions in a given currency
and exchange rate (for example Actual or budgeted exchange rate).
One scenario could be to see the value of sales by product (Product A) in
Euro. In this case will transaction 2 that was made in GBP have to be
converted to EUR, so I can see total sales for product A in Euro.
Solution so far:
So far, have I have added the combination of exchange rate and currency to
the fact table. For example, the transaction in currency "GBP" and "EUR" for
both the exchange rate "Actual" and "Budget2005". This gives 4 entries for
each sales transaction in the fact table. This does NOT work... Selecting on
the dimension tables, for example on the product lead to the value of the 4
transactions to be added together. :-( Where in fact I only want the unique
transaction based on dimension and chosen Currency and Exchange rate.
I have considered making another dimension for Currency and Exchange rate,
but it is not possible to create a relationship to the fact table as the
values Currency and Exchange rate can not make a unique relation to the fact
table.
So far my best bet is only to have one unique entry of the sales transaction
in the original currency in the Fact table. But how do I then make it
possible to see the sales transaction by relevant dimension in the chosen
Currency and Exchange rate?
Look forward to hearing from you.
Best Regards,
BigalexxHi Bigalexx,
Thanks for your post.
This question appears to be consulting in nature. We would also like to
introduce you to the CSS Advisory Services team.
Advisory Services is a remotely delivered, hourly fee-based, consultative
support option that provides a comprehensive result beyond your break-fix
product maintenance needs. It is an hourly fee-based, consultative
support option that provides proactive support beyond your break-fix
product maintenance needs. This support option includes working with the
same technician for assistance with issues like product migration, code
review, or new program development.
For more info in the US and Canada:
http://support.microsoft.com/defaul...AdvisoryService
Outside of the US/Canada:
http://support.microsoft.com/defaul...ernational.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Bigalexx,
>From your question I am guessing you are a beginner....
I have a beginners page on my web site www.peternolan.com with links on
the books to read to learn data modeling for BI systems......if you
are struggling with the multi-country, multi-currency pieces of a model
you would greatly improve your skills by reading some of ralphs books
and looking at the databases on the CD in the back.....
My personal home page is intended as a resource for beginners...so if
you (or others here) have opinions on what else is useful for beginners
I'd be happy for feedback and to put things onto my list of things to
publish...
Good luck!!
Peter
www.peternolan.com