February 13, 2014 at 12:08 pm
I have an existing monitoring system with Resources(servers), Services(multiple services for clients), and a link table to link Resources and Services.
The sample ddl and data below produces the following -
ResourceName
TRX1
TRX2
DB-C1I1
DB-C1I2
ServiceName
C12345_TMGR
C12345_TSA
C65432_TMGR
C65432_TSA
--Sample Links
ResourceNameServiceName
DB-C1I1 -> C12345_TMGR
TRX1 -> C12345_TMGR
DB-C1I1 -> C12345_TSA
DB-C1I2 -> C65432_TMGR
TRX2 -> C65432_TMGR
DB-C1I2 -> C65432_TSA
In my example tables and data I have just 2 transaction servers, 2 database servers, and 2 clients with 2 Services each.
Currently the constraints are in place to ensure only one linkage exists between a given Resource and a given Service.
We are looking to add additional constraints that I'm not sure how to do or if I should recommend be implemented through the application.
1) Every service should be linked to at least 1 resource.
2) A service should be linked to no more than 1 TRX resource.
3) A service should be linked to no more than 1 DB resource.
Currently Resource types are only identified by the name being like '%TRX%' or '%DB%'.
I am considering a Resource Type column and a lookup table.
There are actually many more Resources that contain text such as 'Web', 'APP', 'EAPP', 'NOC' and these do not always have the same naming structure.
If I add a Resource type, is there a way to include this in the linkage table to enforce #2 and #3?
I am not coming up with any ideas on enforcing #1. That's where I'm thinking it may have to be something done in the application when a new Service is added.
Am I missing some easy design concepts or am I not coming up with anything because these are out of the scope of DB design.
Any input is greatly appreciated.
--
Create Table Resources(
ID int Identity(1,1) NOT NULL,
ResourceName varchar(50) NULL,
Rsrc_Property1 int NULL,
Rsrc_Property2 varchar(30) NULL,
CONSTRAINT PK_Resources PRIMARY KEY CLUSTERED
( ID ASC));
Alter Table Resources ADD CONSTRAINT UQ_ResourceName UNIQUE NONCLUSTERED ( ResourceName ASC);
Insert into Resources
Values
('TRX1', 12, 'trx property string1'),
('TRX2', 15, 'trx property string2'),
('DB-C1I1', 25, 'db1 property string'),
('DB-C1I2', 29, 'db2 property string')
Create Table Services(
ID int Identity(1,1) NOT NULL,
ServiceName varchar(128) NOT NULL,
Svc_Property1 int NULL,
Svc_Property2 varchar(30) NULL,
CONSTRAINT PK_Services PRIMARY KEY CLUSTERED
( ID ASC ));
Insert into Services
Values
('C12345_TMGR', 2, 'tmgr property string1'),
('C12345_TSA', 3, 'tsa property string'),
('C65432_TMGR', 11, 'tmgr_property string'),
('C65432_TSA', 13, 'tsa property string')
Create Table Resource_Services(
ID int Identity(1,1) NOT NULL,
ResourceID int NOT NULL,
ServiceID int NOT NULL,
CONSTRAINT PK_Resource_Services PRIMARY KEY CLUSTERED
( ID ASC),
CONSTRAINT UQ_Resource_Services UNIQUE NONCLUSTERED
( ResourceID ASC, ServiceID ASC ));
Alter Table Resource_Services ADD CONSTRAINT FK_Resources FOREIGN KEY(ResourceID)
REFERENCES Resources (ID)
Alter Table Resource_Services CHECK CONSTRAINT FK_Resources
Alter Table Resource_Services ADD CONSTRAINT FK_Services FOREIGN KEY(ServiceID)
REFERENCES Services (ID)
Alter Table Resource_Services CHECK CONSTRAINT FK_Services
Insert into Resource_Services
Values
(1, 1),
(3, 1),
(3, 2),
(2, 3),
(4, 3),
(4, 4)
February 13, 2014 at 2:34 pm
Although there's nothing in the relational model to prevent you from having count constraints on a table, the only table level constraints supported by SQL are foreign key, primary key and unique constraints; CHECK constraints are supported only at row level; so effectively CHECK constraints are useable only for supporting what in teh relational model would be called DOMAIN contraints (for domains representing what is in a row either in a single column or in multiple columns). So your current table structure can't enforce the three constraints you wish to add, because that would require table-level constraints to do counts.
However the answer isn't to move handling this out to the application - that would be an invitation to fate to deliver a data corruption disaster in the not too distant future. One way of handling it is to put 3 new tiny-int columns into the Service table, use them to hold the count of DB resources used by a service, the count of TRX resources used by a service, and the count of other resources used by a service. Then have a check constraint that says each of the first two must be either 0 or 1 (all three start at 0, because the foreign key constraint from the Service_Resource table to the Service table means that no Service_Resource table entry for this service can exist until the Service table entry exists). Maintain the counts by updating them in a trigger on the Service_Resource table, and also in this trigger fail any attempt to update things in such a way that the sum of the three counts ends up as 0 actually enforce that if the pre-updte sum is nonzero then so is the post-update sum.
The reason that this is better than putting it into an app is simple: it is probably inevitable that in future there will be app updates and.or new apps, plus that people will sometimes want to update the database by hand rather than through the app; if data integrity is in the app, each such occassion carries a risk of ending up with invalid data. Doing it with constraints and triggers means that won't happen unless someone deliberately chooses to disable the constraints and/or triggers to allow invalid data into the database.
Tom
February 13, 2014 at 4:10 pm
Tom - Thanks for the idea. I will have to think this over and try going down the path of using count columns in the Service table and Triggers.
I definitely agree that I should maintain integrity in the database, I just wasn't sure how I would do it.
I will try this.
The count column idea actually could work for another business rule that I didn't include in the example.
If anyone else has other ideas, please add your thoughts. It will probably be a while before I implement any change.
Thanks again.
February 17, 2014 at 10:25 am
I'll try and knock up an example for you, but I'm pretty sure I've faked table-level constraints using a function called by a CHECK constraint in the past.
Could #1 be enforced by a not-nullable FK constraint direct from Services to Resources?
It wouldn't matter too much what you put in there (and you wouldn't be reading the column), but it does force you to put a value in?
There might be a way to use a view with check option to achieve this too.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply