I am developing an application that uses a database.
As I have designing the database, I am thinking about the future framework for the system and wanted to engineer the database with the best practices.
I have 3 main functions.
- Employee Management
- Equipment Management
- Configuration Control
I would like to know the pros and cons of creating 3 separate MSSQL databases on the same server and linked them with views versus 1 MSSQL database with different schema(s) for each function. Or is there a third recommended method.
If you are linking across the databases, then it is likely you have keys in various tables whose values will appear in two or three of the databases. This will make you pull your hair out. Foreign key constraints can not be enforced across databases. You will also want to take into account how to restore a database. if you restore one database, to a point in time in the past (say 24 hours ago), what will happen to any data in the other two databases that may depend or refer to the data that was entered in that 24 hour span, and is now lost?
I'll go with that suggestion. Previous company had several instances with multiple databases where developers joined tables across databases. Stupid idea. I saw this once before with another company and it's just flat files and nothing referenced correctly.