We have designed a normalized data model that has around 18 tables in it and the tables are inter linked with each with foreign key constraints.
This is a operational data model that will be constantly updated and inserted.
but for reporting purpose(to generate reports), we are planning to move the older records to another dedicated database server. i would like to move the entire database structure to another database server(all 18 tables with foreign key constraints) to another table
and write queries to display reports.
how to achieve this?
just for simplicity i am giving 4 tables here. each one are connected to other with foreign key
order table -
this contains all the order information
OrderID OrderNumber customername
device table -
each order can have multiple devices
DeviceID DeviceName DeviceModel OrderID
each devices can have multiple jobs running
JobID deviceID JobNumber
job status - displays the job status(progress, completed, failed)
JobStatusId JobID jobStatus