Storing user relations with conditional join on other tables

Question

Post reply

Storing user relations with conditional join on other tables

petervdkerk

SSCrazy

Points: 2155
More actions
November 16, 2015 at 2:54 pm

#313135

I have a site with vendors. Vendors can start relationships/partnerships.
I store these relationships in table [vendorrelations]:
CREATE TABLE [dbo].[vendorrelations](
[id] [int] IDENTITY(1,1) NOT NULL,
[vendorAId] [int] NOT NULL,
[vendorAObjectType] [tinyint] NOT NULL,
[vendorBId] [int] NOT NULL,
[vendorBObjectType] [tinyint] NOT NULL,
[recommendationFromVendorA] [nvarchar](4000) NOT NULL,
[recommendationFromVendorB] [nvarchar](4000) NULL,
[relationstatus] [tinyint] NOT NULL CONSTRAINT [DF_vendorrelations_status] DEFAULT ((0)),
[createdate] [datetime] NOT NULL CONSTRAINT [DF_vendorrelations_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_vendorrelations] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
So a single row contains all details for the relationship, in which relationstatus determines if the relationship is approved by both parties.
A vendor is uniquely defined by the unique combinaton of id and an objecttype. So 2 unique vendors would be:
idobjecttype
31
32
One of the problems I run into now, is that because both partners may insert a relationship request, whenever I want to get all relations for a single vendor I need to check both VendorAId+vendorAObjectType as well as the vendorBId+vendorBObjectType columns.
So like so:
SELECT * FROM vendorrelations vr WHERE (vendorAId = 879 AND vendorAObjectType = 1) OR (vendorBId = 879 AND vendorBObjectType = 1)
Now, on each request I do not only want to request the relationship details for a single vendor, but also add some details on which vendor that relationship is with.
To add to the complexity, based on column vendorAObjectType or vendorBObjectType, I need to check the related data in different tables.
If objecttype=1 I need to check against [locations] if objecttype=2 I need to check against [genobjects]. Both these tables look similar, but in reality on my machine contain a lot more columns. (if it matters: I can not alter the design of [locations] or [genobjects])
CREATE TABLE [dbo].[locations](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](80) NOT NULL,
[friendlyurl] [nvarchar](80) NULL,
CONSTRAINT [PK_locations_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[genobjects](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](80) NOT NULL,
[friendlyurl] [nvarchar](80) NULL,
CONSTRAINT [PK_genobjects_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I now have this when I want to request all partnerships for vendorId 879 with objecttype 1:
SELECT vr.*,obj.title,obj.friendlyurl
FROM vendorrelations vr
LEFT JOIN locations obj on obj.id=vendorAId
WHERE (vendorAId = 879 AND vendorAObjectType = 1) OR (vendorBId = 879 AND vendorBObjectType = 1)
The problem now is that:
A. I need to join a specific table based on the objecttype of the partner of vendorId 879
B. I'm not sure how to check in which column combination (VendorAId+vendorAObjectType or vendorBId+vendorBObjectType) the details for the vendor whose partnerships I wish to check are stored.
So, because of this, is this the best way to store these relationships? If not, how else? If so, how would I extract the data I need?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

"Single row" is not a good representation of a partnership/relation.

First, as you realised, it's not symmetrical, partners are not equal in it by design.

Second, it does not allow more then 2 partners in any group.

See if this approach can give you some resolution:

CREATE TABLE [dbo].[VendorRelation](

[id] [int] IDENTITY(1,1) NOT NULL,

[RelationTypeID] [int] NOT NULL,

[relationstatus] [tinyint] NOT NULL CONSTRAINT [DF_vendorrelations_status] DEFAULT ((0)),

[createdate] [datetime] NOT NULL CONSTRAINT [DF_vendorrelations_createdate] DEFAULT (getdate()),

CONSTRAINT [PK_vendorrelations] PRIMARY KEY CLUSTERED ([id] ASC),

CONSTRAINT [FK_vendorrelations_RelationType] FOREIGN KEY REFERENCES dbo.RelationType ([id])

)

CREATE TABLE [dbo].[VendorInRelation](

[Relationid] [int] NOT NULL,

[vendorId] [int] NOT NULL,

[recommendationFromVendor] [nvarchar](4000) NOT NULL,

[ParticipationStatusID] [tinyint] NOT NULL,

[JoinDate] [datetime] NOT NULL CONSTRAINT [DF_vendorrelations_createdate] DEFAULT (getdate()),

CONSTRAINT [PK_VendorInRelation] PRIMARY KEY CLUSTERED ([Relationid], [vendorId]),

CONSTRAINT [FK_VendorInRelation_Vendor] FOREIGN KEY REFERENCES dbo.Vendor ([id]),

CONSTRAINT [FK_VendorInRelation_ParticipationStatus] FOREIGN KEY REFERENCES dbo.ParticipationStatus ([id])

)

_____________
Code for TallyGenerator

petervdkerk SSCrazy Points: 2155 More actions · Answer 2

Hi, thanks!

I'm not sure what all your columns mean, could you elaborate?

Also, with your example, when vendor A writes a testimonial for vendor B, and later vice versa...how would I know this? A testimonial is always a 1-1 relationship and not related to a group.

Hope you can help!