I have different databases representing different countries (UK, Belgium, Holland, Germany, Ireland, etc.)
Each database has a list of suppliers and contracts.
I'm building a database to act as a cache for some of the more static information from the multiple DBs.
I have a table for
Databases (UniqueID, DBName, DateAdded, DateRemoved)
Suppliers (UniqueID, DBID, SupplierName, DateAdded, DateRemoved)
Contracts (UniqueID, DBID, ContractName, DateAdded, DateRemoved)
A supplier or a contract in a specific database is not connected to any other supplier or contract in any other database, even if the name is the same.
Contract RAQ in Ireland is not the same Contract RAQ in Germany.
I now have a new table called Filters which needs to have the following restrictions.
1 - A Filter may or may not belong to a supplier.
2 - A Filter may or may not belong to a contract.
3 - The databases for any supplier and contract must match for a single Filter.
4 - A Filter may exists without a supplier or a contract.
What changes would you recommend to the existing 3 tables (Databases, Contracts, Suppliers), if any?
How should I construct the Filters table?
Are there any additional tables to create?
I need to enforce these rules on the server. Ideally constraints but insert/update triggers will do.
I've got 8 databases, 283 suppliers and 157 contracts.
In terms of Filters, this could be as many as 44,872 ((1 + 283) * (1 + 157))