Hello All,
Sorry if this is long, but its taken me 2 months to even put this into words properly. I'm looking for advice on using the xCase Professional Version 3.0 for a big project I'm attempting to do mostly myself.

Some Background Info First -
This is a Data Warehousing Application, so alot of my "how it should work" theory is contrary from your typical RDBMS application where everything is always in normalized form and very little data is replicated. So, I am really designing a Data Warehouse *System*, that will be a modular collection of utilities, applications, and user interfaces. Each module will be independent of the others and perform one function or process. The following list is the modules I am designing:
- Transfer(moves delimited text files from many Unix boxes to 1 database server)
- Restock(Updates source tables from text files with Zap and Appends)
- Preprocess(Join tables & replicate data into useable forms)
- Report Processing(what needs done for each report)
- Report Generation(Reports are subdivided and distributed in so many ways its almost unreal)

Currently Restock, Preprocess, and Report Processing are handled in a FPW 2.6 application that does not follow a modular approach. Needless to say I'm trying to learn from my past mistakes. So this new system will use over 200 tables, and generate nearly 100 reports to both local and remote network printers.

My Problems -
Nearly all of my tables and reports already exist. I want to use xCase to pull in my 2.6 tables (Reverse Engineering), modify the tables for new system & make attributes all the same, then export the tables (Forward Engineer) into VFP 5.0a. ;) or maybe even 6.0 till I get done.

Since I'm working with *so many* tables, you would think xCase would be my lifesaver. But the awkward user interface is making me pull my hair out! Twice I have attempted to load in only my source data tables (like 50-60 dbf's) and the result has been a diagram that is NOT easy to work with. I've read the user's guide many times, sent e-mail to Israel, and talked to the vendor at DevCon. But it seems to me I'm either missing something major with creating a new diagram OR I'm trying to put too many tables into one model.

So now I have looked at all my table structures again. At this point I cannot eliminate any tables becuase the new system will be a implemented in phases, and the Restock module will be used with my 2.6 app while development is in progress. But I have managed to see that the new system design makes it easy to group my 200+ tables into the following categories:

System Tables - stores application meta-data to manage Data Warehouse.
Source Tables - where Unix text files get stored.
Reference Tables - stores Lookup Values to manage report processing.
Processed Tables - Preprocess module results, replicated data stored to make report processing faster.
Report Tables - The final product tables stored to make report distribution faster.

These 5 categories really helped me with system analysis and design tasks. Since I dont have any useful ERD diagrams yet, they atleast helped me understand what I'm dealing with. And, I'm hoping these categories will make my next attempt to use xCase a little more productive.

My xCase related Questions-
1.) Does grouping my tables into the above categories make sense to anybody other than me? Maybe I've gone over the edge... as in I'm reaching the point where I'm "over-designing". :)

2.) Can I setup each of my Table Categories as a separate model in xCase to make the user interface easier to deal with? Would this be a wise approach? If so will I still be able to Forward Engineer everything into one VFP DBC?

3.) Is putting 200+ tables into one DBC even a good idea? Maybe I should make a separate DBC for each of my categories? The tables will initially be local to VFP, then migrated to SQL Server.

4.) If I do use separate xCase models will I loose the ability to show all relationships in ERD diagrams? (If my users dont like what a report shows them, they question "where" the data came from. This amounts to alot of my time waisted in researching programs in my 2.6 system)

5.) Unfortunately, This Data Warehouse has no clean primary keys. I uniquely identify records by combining fields from Source Tables with fields from Reference Tables. xCase does not like this on the Reverse Engineering, but it lets me get away with it. Will this lack of primary keys cause me any major problems when I Forward Engineer my revised tables into VFP?

Any other advise on using xCase for this project, or experience with developing a Data Warehouse System would also be greatly appreciated. Much TIA!!