When we started using StrataFrame for our redevelopment project we intended on starting with a fresh database design. (I know Trent was involved with many of the discussions and made some excellent suggestions). The final decision has been made and we decided to develop against our legacy database for the core components, which is not ideal, but it was a business decision we had to make and the reasons made sense for the size of our install base and the companies future direction.

Some background...The database was converted to MS SQL from Pervasive Btrieve, and the design and structure is not ideal. The tables lack relationships and primary key values and a lot of the field types are Char when they should be DateTime, Boolean etc...  Fortunately the DDT and Business Object mapper enables us to work around these issues quite nicely so we are dealing with the correct types within the .NET code.

The structure is another issue. Right now one of the problems I am trying to solve is how to deal with multiple tables for the same type of data or data that is duplicated in multiple tables. A long time ago Btrieve had maximum sizes for record length or something like that... so once the maximum was reached a new table was created. Example: I have two tables for customer information (One to One relationship).

My question is this... With StrataFrame, is it possible to create a business object structure using our 'ideal' data model, which we hope to migrate to one day, and have this business object maintain the two physical tables, so when we refactor the database we don't have rewrite a lot of code. (we are using the ddt for structure but not using it for deployment of our legacy structures). Keeping in mind we still want to take advantage of all the StrataFrame data binding, broken rules....etc

Is there another approach we could use?  I'm open to any suggestions.

Thanks

Tim

My knowledge of Pervasive is limited so you will have to take this with a grain of salt and maybe adapt it to your way of doing this.

We have been migrating our medical application over from FoxPro tables to SQL tables and had the same problem.  So we created a view within FoxPro that mapped the old fields to the field names that we wanted them to be long-term (in SQL).  We then created a DDT profile that is not exported....yet....and map our BOs to that DDT profile.  We then use OLEDB to talk to the FoxPro tables through our BO...but our BO is actually talking to the VFP view, which then talks to the VFP table with our old field names.  It requires a bit of effort, but it is allowing us to basically change a few flags in our BaseBo that we inherit off of and BOOM....we can talk to SQL Server with very little effort...definitely no changing of the maintenance forms, etc.

It sounds like you should be using SQL Data Transformation Services (DTS) since it is perfect for these kinds of jobs. Then once you transfer, manipulate, and merge your data you can then import to SF DDT.

Just a suggestion...

Trent, the database is already in MS SQL. We are no longer dealing with Pervasive. (I was simply providing background on why we are basically working against a flat-file data model). 

Let me try to clarify. Since I can't develop against a new physical model, I though I would figure out it I could develop against a ideal logical model (via Business Objects or some other layer) and have the business objects or this other layer, maintain the old data model until such time down the road when I can refactor the old physical data to match the logical model.  I believe this logic goes against the current StrataFrame design. You are looking for a one-to-one mapping of business object to DB Table.

Here is an example of what I am trying to explain:

A) Currently I have two customer tables:

Cust-1 - CustomerID (PK), Name, Address 1, Address 2, City, State ...

Cust-2 - CustomerID (PK), SalesRepID, TermsID, ServiceRepID, ContactName ...

Ideally, the information in Cust-2 should be included in Cust-1. (Table Cust-2 should not exist). Also, the address information should be moved out to an separate Address Table because we have Vendor addresses, shipping addresses .. etc

Right now I have to create a Business Object for Cust-1 and another for Cust-2. I then have to include both of these on my maintenance form.

B) Ideally, I want this type of DB structure (Which I cannot do right now but I want the UI to talk to this structure and update the two customer tables above)

Customers - CustomerID, Name, SalesRepID, TermsID, SalesRepID ...

Addresses - AddressObjectID, Address1, Address2, City...

Contacts  - ContactObjectID, ContactName, EmailAddress ...

So I figure I need to create business objects based on this 'ideal' structure (B) but I need to somehow physically maintain the existing DB structures (A). Then, at some point down the road when the application has been rewritten in .NET, I can begin refactoring the database and not have to worry about doing extensive modifications to the UI layer because it's already talking to the proper structure.

Hopefully this explanation adds some clarity to my situation. Perhaps I need some type of additional layer or something else to make this work. I open to any and all suggestions.

Thanks

Tim

This is actually what I was referring to.  Our base tables were not any ideal model....so we created the BOs and mapped them to our ideal model and then started working the problems.  A view was just one of the tools that we used to accomplish this because we could pull from the source table and populate a view with the desired structure using AS clauses.  In your case, you may need to take into account Custom properties...or create a base BO class that your BOs inherit from that has extended logic to convert from one structure to another.

In short, there is no "inherant" logic that does this, but it is possible to create a solution through StrataFrame and BOs to implement this type of behavior.  I hope that makes sense

In theory, I understand the concept you are suggesting... I'm not clear yet on how to make it work. I need time to think about what you have suggested and try a few things out. I'm sure the light bulb will turn on at some point during this exercise.

Thanks

Tim

Sorry if I am not being very clear...you are right though...once you start playing with some things lights to generaly start coming on.  As you play with your options and see some different avenues, please post those ideas out here and we can discuss them further.