We have made substantial changes to our physical DB, now as it is the end of the project I would like to abstract a logical model from this, to allow me to generate schemas for both Oracle and SQL Server.
Can anyone guide me as to the best way to achieve this. I was hoping TOAD data modeller would help but I can't seem to see any options to do what I require?
If you're looking to normalize your physical database, this Wikipedia article on database normalization can help.
As far as which tool should you use, a lot of companies use the ErWin data modeler. I use the tools that come with the MySQL community server to do my data modeling, although you would have to manually create the SQL to build Oracle and SQL Server schema.
If your schema consists of 50 tables or fewer, manual methods should work OK. It's tedious and error prone, but if you're careful you should get it right.
The cadillac of schema reverse engineering tools is Data Architect from Sybase. It's pricey, but it's good.