Design Feedback (in Yukon) - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Design Feedback (in Yukon)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-01 : 07:49:59

writes "I work for an ISV and we are in the process of developing a
brand new product. The product will be developed with .NET
and NHibernate (archiect's decision). The
product requirements call for the ability to create a
custom UI with fields that won't map to any of our standard
database schema. So we need some method for persisting the
data. Another key piece of functionality is the customer
will need to be able to create custom reports off of these
fields.

So the approaches are one of the following:

1.) Dynamically generate DDL - That means creating brand
new table structures, relationships, etc.
Pros: Much easier to report on, and to code(from the
NHibernate standpoint), referential integrity
Cons: How do you manage indexes (including full-text), row
lengths, adding NOT NULL fields, query performance, etc.

2.) XML data type -
Pros: Flexible schema
Cons: Performance? Managing XML schema (CRUD operations),
reporting is more complicated

3.) Relational mapping - Essentially creating a table of
field definitions and defining a many-to-many relationship
between the fields and business entities and storing the
values in the associative table.
Pros: Flexibility in defining fields, somewhat easier to
report on (better than XML)
Cons: harder to support different data types,
relationships, reporting is a little more complicated in
terms of presentation.

I know I'm leaving out a lot of details, but hopefully you
get the gist of what we are trying to accomplish.

I would appreciate any feedback you could provide.

Thanks in advance."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-01 : 07:57:43

Well, I'd definitely recommend against #1, that will get messy very quickly, and it's not the proper way to design a database. Normally I'd suggest #3, but I can see that becoming pretty inconvenient. As much as I dislike XML in databases, #2 will probably cause you the least amount of grief. I'm afraid none of these solutions is really ideal. As long as you're only trying to do this in Yukon/SQL2005 then the XML solution won't be too horrid.

Can you provide some more detail and examples of the kind of data that would be stored, i.e. phone numbers, addresses, something else? Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources