Link to home
Start Free TrialLog in
Avatar of Richard Quadling
Richard QuadlingFlag for United Kingdom of Great Britain and Northern Ireland

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))





Avatar of G Godwin
G Godwin
Flag of United States of America image

Can the same filter belong to multiple databases, contracts and or Suppliers?
-G
 
Avatar of Richard Quadling

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.


I believe the fields Suppliers.DBID and Contracts.DBID are keyed to Databases.UniqueID, is this correct?
-G
Yep.
ASKER CERTIFIED SOLUTION
Avatar of G Godwin
G Godwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I get it!

I'm at home at the mo, but will put that in tomorrow.

Thanks.
Any progress?
-G
Thanks for the ping.

And works like a charm!

A perfect answer. DWISOTT
Great!
-G