Design - Transitioning from conceptual domain to physical data model

When creating the design for new requirements we often design a domain model of key entities and then when were happy with it we migrate this domain model into a logical and then physical data model. Although we should go to the logical model and then transition to the physical data model we often go straight to a physical data model.

We currently perform this process manually however Enterprise Architect ( UML modelling tool ) has got great support for transforming a domain model into a data model using model driven architecture ( MDA ) templates. It simply reads the entities and the multiplicity relationships between them and generates an appropriate data model. Handling many to many relationships isn't handled with the built in templates however you can easily customise the transformation template using an editor e.g. you can specify

%if attTag:"columnLength" != ""%
length=%qt%%attTag:"columnLength"%%qt%
%elseIf classTag:"columnLength" != ""%
length=%qt%%classTag:"columnLength"%%qt%
%endIf%

to use tagged values to denote the length of a character string in the domain model. Although you shouldn't be too concerned with lengths of strings in the domain model, if you want to specify this information in a tagged value you can at this point, and then ensure it's carried over to the data model e.g. to create a varchar(100) field for a UserName attribute of a User entity. Sparx support are currently developing these templates further and the latest version of the template handles many to many relationships by creating a link table in the format JoinTo however you could customise this to fit your own database standards.

It would be great if through automation we could eventually create a data access layer from the conceptual model using the following steps

1. Create the conceptual domain model. A business analyst would ideally create the first draft
2. Add attributes as required to turn this model into a logical data model
3. Transform the logical data model into a physical data model

We actually use LLBLGen ( object relational mapping tool ) to generate a data access / business object layer from the database so in this way we could continue this process and generate the LLBLGen classes by following some additional steps

1. Create the database from the physical data model.
2. Create the LLBLGen classes from the database directly.

Both of these steps can actually be automated through the automation ability within Enterprise Architect and LLBLGen can be automated too to forward engineer the generated code. In this way we could go from a logical model to a generated database and then to an auto generated business object / data access layer automatically.