Last post Jan 22, 2015 10:14 PM by maggiemays
Jan 21, 2015 03:34 PM|maggiemays|LINK
I have a contracts table. The clients want to be able to relate one contract to another
( sometimes more than two contracts are related for some reason or another).
I created a table with two fields that make up one pk - contract, relatedcontract. When the user wants to relate one case to another, this table would be updated with that information.
When contract 'a' is related to case 'b', the contract in the table would be 'a' and the related contract would be 'b'. I would then have to add
another contract 'b' and related it to 'a', so when the contract 'b' is viewed, it shows( in a text box or something) it is related to contract 'a'.
Is there a better way to do this?
Jan 21, 2015 05:02 PM|IbrahimIslam|LINK
How are these contracts related in terms of business logic: Master-Details (1 to *) , Parent-Child (self-referencing) or like Nodes?
Jan 22, 2015 12:03 AM|maggiemays|LINK
Jan 22, 2015 03:55 AM|IbrahimIslam|LINK
There is one table for contracts. They requested that they can relate other 'like ' contracts. When they search for contracts, they will want to see which ones are related to others. The user determines they are related for various reasons. I hope that makes
So, what your contracts might end up look like is a graph and relational databases are not really geared towards it that is why DBMS like Neo4j exists.
But if you want to handle this in your current system then what I suggest what you can do is create another table where only relations of contracts will be maintained like
create table Contract_Relations
Like Document with Id 1 is related to Document with Id 2
Document with Id 2 is related to Document with Id 3
Document with Id 2 is related to Document with Id 4
Document with Id 4 is related to Document with Id 5
With ends up looking like:
1 | 2
2 | 3
2 | 4
4 | 5
Jan 22, 2015 05:41 AM|Nasser Malik|LINK
You are going in right direction so far. What i understand the issue you are facing is if you relate contract B to A and when you show details either A or B it should show related contract
In case of A Related Contract is B and in case of B Related Contract would be A
You need to define Unique Constraint on both columns so that you don't insert duplicate records
CREATE TABLE Contract
Id INT NOT NULL IDENTITY(1,1),
INSERT INTO Contract SELECT 'Contract 1'
INSERT INTO Contract SELECT 'Contract 2'
INSERT INTO Contract SELECT 'Contract 3'
INSERT INTO Contract SELECT 'Contract 4'
INSERT INTO Contract SELECT 'Contract 5'
INSERT INTO Contract SELECT 'Contract 6'
INSERT INTO Contract SELECT 'Contract 7'
INSERT INTO Contract SELECT 'Contract 8'
SELECT * FROM Contract
CREATE TABLE ContractRelation
ContractId1 INT NOT NULL,
ContractId2 INT NOT NULL,
CONSTRAINT uc_PersonID UNIQUE (ContractId1,ContractId2)
INSERT INTO ContractRelation SELECT 1,2
INSERT INTO ContractRelation SELECT 3,4
INSERT INTO ContractRelation SELECT 4,5
INSERT INTO ContractRelation SELECT 4,7
INSERT INTO ContractRelation SELECT 5,8
SELECT * FROM ContractRelation
SELECT DISTINCT ContractId1 FROM ContractRelation WHERE ContractId2 = 4
SELECT DISTINCT ContractId2 FROM ContractRelation WHERE ContractId1 = 4
Out would be
Jan 22, 2015 09:58 AM|maggiemays|LINK
Jan 22, 2015 09:59 AM|maggiemays|LINK
Jan 22, 2015 11:16 AM|limno|LINK
A table can have only one primary key. However you can have composite primary key with more than one column.
Jan 22, 2015 11:18 AM|maggiemays|LINK
I re-read your post and noticed the 'union' statement. That means I only need to add one relationship between contract 'a' and 'b' for example, whereas before I was going to need to add "ba" as well. I need to practice adding,deleting from this table
to see how it works. Thanks for your help.
Jan 22, 2015 01:07 PM|IbrahimIslam|LINK
Thank you for your reply. In your example of contractrelation table, are you making both fields one primary key?
You can make it a Composite Key (2 fields = 1 PK) if you'd like.
Jan 22, 2015 01:19 PM|maggiemays|LINK
I have a question. If I am going to use UNION to get the results I need when looking at the related contracts, I must also have to have some routine that would prevent
a record 'AB' , for example, from being added to the table if there is a record for "BA". Is that correct?
Jan 22, 2015 04:15 PM|maggiemays|LINK
This is what I have for a stored procedure that inserts into the contractrelation table. How does this look?
CREATE PROCEDURE pr_Relatedcontracs_ins
if NOT exists( select distinct ContractID from relRelatedcontracts where contractRelatedID= @contractRelated
select distinct relatedcontractid from relRelatedcontracts where ContractID = @contractSourceID)
INSERT INTO relRelatedcontracts(ContractID,relatedcontractID)VALUES(@contractSourceID,@contractRelatedID)
Jan 22, 2015 06:21 PM|gimimex|LINK
CREATE PROCEDURE pr_Relatedcontracs_ins
merge relRelatedcontracts as r
using (select @contractSourceID as ContractID, @contractRelatedID as ContractRelatedID) as s
on (s.ContractID = r.ContractID and s.ContractRelatedID = r.ContractRelatedID) or
(s.ContractID = r.ContractRelatedID and s.ContractRelatedID = r.ContractID)
when not matched then
insert (ContractID, relatedcontractID) values (s.ContractID, s.ContractRelatedID);
Hope this helps.
Jan 22, 2015 10:14 PM|maggiemays|LINK