Richard Quadling
asked on
Storing matrix data.
Hello.
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.
So.
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.
e.g.
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))
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.
So.
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.
e.g.
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))
ASKER
A filter can only provide data for a single supplier and/or a single contract.
We are essentially middle men (with regards to this aspect of the business).
So we want to sometimes report upon ...
All the work for a single supplier
All the work for a single customer
All the work for a single supplier/customer
All the work
and always from 1 DB at a time.
We are essentially middle men (with regards to this aspect of the business).
So we want to sometimes report upon ...
All the work for a single supplier
All the work for a single customer
All the work for a single supplier/customer
All the work
and always from 1 DB at a time.
I believe the fields Suppliers.DBID and Contracts.DBID are keyed to Databases.UniqueID, is this correct?
-G
-G
ASKER
Yep.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get it!
I'm at home at the mo, but will put that in tomorrow.
Thanks.
I'm at home at the mo, but will put that in tomorrow.
Thanks.
Any progress?
-G
-G
ASKER
Thanks for the ping.
And works like a charm!
And works like a charm!
ASKER
A perfect answer. DWISOTT
Great!
-G
-G
-G