Hi guys. I'm playing around evaluating the DDT at the moment.



The DDT does not seem to provide any way of creating triggers. One area where triggers are useful is in the enforcement of cardinality on a relationship. For example, a parent must have one and only one child, or perhaps between one and three children, but no more or less, etc.



Assuming that you use the DDT with your own apps, do you ignore triggers completely and implement things like cardinality in the business objects? For that matter, do you write any SQL at all (such as stored procedures for reports), or do you do it all in the .NET application?



The help file for the DDT is a little bit light on at this stage. For example, there is nothing on deployment packages or how to create and use them. Similarly for the steps required to update an existing database with a new version, something which the DDT is touted to be able to do.



The import function is very limited. I pointed it at an existing SQL Server 2005 database and only got the tables. No foreign key contraints, no unique key constraints, no indexes, no descriptions, no triggers and no stored procedures.



At the moment it is difficult to see what I would use the DDT for. The fact that it lacks any kind of diagram tools is also a big minus. I currently use CASE Studio 2 for designing my database schema, and while it may rely on scripts, it works and is very flexible, including ER diagrams, HTML reports, generation of RI triggers, and support for a multitude of databases.



The DDT has potential, but I think it is currently way too underdeveloped. Maybe you've got big plans for it that we will see in the future?



Regards,

Bruce

No, the DDT does not provide a method for deploying triggers to SQL Server.  If you use triggers extensively then maybe this isn't the choice for you.

Yes, we enforce cardinality through .NET code.  Even when using FoxPro databases, we never deployed triggers.  As for reports, our reports use our business objects, which allows us to retrieve the data through the business objects rather than requiring a stored procedure.  If we ever require a stored procedure, it is written as a CLR stored procedure (within a managed DLL that is deployed to the server).  There's no need for us to split up our business logic and decentralize it and require us to code it in T-SQL when we have .NET.

Yes, the DDT help is lacking information on creating deployment packages.  When upgrading an existing database, the EXACT same process is used as when deploying a new database; if the database exists, it is upgraded, if it does not exist, it is created.

The import function is not designed to be a complete solution.  It is designed to allow you to bring forth legacy databases for a new application.  Once the majority of the data is in the DDT, it is generally massaged before it is deployed to the server.

The DDT is designed as a database Deployment tool, not a database management tool.  It is designed to deploy a structure to SQL Server without requiring the developer to create multiple scripts that must be executed on the server.  If you use diagramming exclusively, then this is not the tool for you.

There are no plans for the DDT to support diagramming, reports, or RI triggers.  Regardless, thank you for your input and review.

Hi Ben,



Interesting. So your approach is to use SQL Server primarily as a repository for the data tables, with some foreign key constraints and some indexes, and this is why these are the primary features that the DDT supports.



The DDT also has provision for stored procedures, either T-SQL or CLR. But your preference seems to be to avoid stored procedures altogether, if possible, and do everything in the application using the business objects. If you have to resort to a stored procedure, using CLR is your preferred choice.



I was taught database design using SQL and stored procedures. In my researches on .NET tools for database development, I am beginning to see that there are alternative approaches. I do like StrataFrame's ability to dynamically generate SQL statements. Some of the other tools I have evaluated take this approach also.



There seems to be a strong polarization in the developer community between stored procedures and dynamically generated SQL. I can see that the latter has some clear advantages. For example, not having to write and maintain dozens or hundreds of SQL stored procedures is a great idea. However throwing out stored procedures completely seems a bit extreme. To my mind some tasks can still benefit from stored procedures. Report generation, for example. Some reports require accessing large amounts of data in the database to produce a relatively short summary. In this case it seems better to compile the report on the database server rather than transfer huge amounts of data across the network.



As with most things in life, one needs to find the right balance. StrataFrame seems to take the dynamic SQL approach, but provides support for stored procedures when required. And of course, SQL Server 2005 provides this new feature of CLR stored procedures, which adds a whole new twist.



I am curious as to your database design technique. Do you design your database schemas directly into the DDT, or do you use other tools as well? Personally I would find it difficult to design a schema without diagramming tools, such as ER diagrams, as this is how I was taught. In addition, the diagrams are a great communication tool, both between developers and with clients. CASE Studio 2 is my current tool of choice because it does everything I need for a reasonable price. I have noted on some web sites that some people may be using Visio, however I am not familiar with this tool. There may even be some tools in Visual Studio 2005 that I haven't encountered yet, being so new to the product.



Thanks for your comments Ben, and for providing a different point of view.



Regards,

Bruce

Hi,

I am very interested on DDT. Any plan to enhance DDT to import/deploy function? I hope DDT able import/deploy "anything" of my database.

Please advice.

We have plans on the import to bring in all relationships, indexes, etc.  As for the deployment, it already handles most everything SQL Server needs including the SQL CLR assemblies.  You might be more specific so that I can better address your question.

Hi,

Thank you for reply.

I would like to have a program (DDT for example) that able to synchronous my database to client during installation and upgrade. It should able to create and upgrade table structure, user defined functions, views, triggers, indexes, constraint (do not remember default constraint name for example), and stored procedure.



Can DDT do this?



Thank you

The only thing that you mentioned that we do not support are the UDFs and the triggers.  All other features are already within the DDT.  In fact, the DDT will even create optimized stored procedures for you by checking a single check box for UPDATES, INSERTS, and DELETES.  This provides quick and easy support for the CRUD settings on the business objects.