Storing matrix data.

Richard Quadling
Richard Quadling used Ask the Experts™
on
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))





Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
G GodwinDatabase Administrator

Commented:
Can the same filter belong to multiple databases, contracts and or Suppliers?
-G
 
Richard QuadlingSenior Software Developer

Author

Commented:
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.


G GodwinDatabase Administrator

Commented:
I believe the fields Suppliers.DBID and Contracts.DBID are keyed to Databases.UniqueID, is this correct?
-G
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Richard QuadlingSenior Software Developer

Author

Commented:
Yep.
Database Administrator
Commented:
Based on what I gather so far, I would create Filters table like this:

create table Filters
(UniqueID int identity primary key,
DBID int not null foreign key references Databases (UniqueID),
SupplierID int foreign key references Suppliers (UniqueID),
ContractID int foreign key references Contracts (UniqueID),
FilterName varchar(255),
DateAdded datetime,
DateRemoved datetime,
Etc varchar(255))

and create to scalar functions, one that would return the DBID for a given Supplier and one that would return the DBID for a given Contract:

create function CheckDBIDSup (@supplierID int)
returns int
as
begin
declare @dbid int
set @dbid = (select dbid from Suppliers where uniqueID = @supplierID)
return @dbid
end

create function CheckDBIDCont (@ContractID int)
returns int
as
begin
declare @dbid int
set @dbid = (select dbid from Contracts where uniqueID = @ContractID)
return @dbid
end

Then add check constraints to enforce that the Supplier and Contractor have to be from the same DBID that is declared for the filter.

ALTER TABLE filters ADD CONSTRAINT CK_supplierID
CHECK (dbid = dbo.CheckDBIDSup(supplierID))


ALTER TABLE filters ADD CONSTRAINT CK_ContractID
CHECK (dbid = dbo.CheckDBIDCont(ContractID))

And in the UI, you would want to restrict the choices to those Suppliers and Contracts that are for the same DBID.  The Constraints just make sure no bad data can actually get in.
-G
Richard QuadlingSenior Software Developer

Author

Commented:
I get it!

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

Thanks.
G GodwinDatabase Administrator

Commented:
Any progress?
-G
Richard QuadlingSenior Software Developer

Author

Commented:
Thanks for the ping.

And works like a charm!

Richard QuadlingSenior Software Developer

Author

Commented:
A perfect answer. DWISOTT
G GodwinDatabase Administrator

Commented:
Great!
-G

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial