February 10, 2010 at 4:29 am
Dave Winchester (2/10/2010)
Hi GrantThanks for the reply. Your explanation has helped me (mentally) greatly. I understand the Person/Location etc...relationship better now.
With regards to my problem of the Comments tables for my design, I am still unsure. A Comment is individual but an Article Comment or Product Comment...??????
A few of my buddies over at the official asp.net forums and msdn seem to like the arc relationship you said I should avoid. I just don't know now.
Just trying to do a good job but am getting lost. Thanks again.
David
If you go with the arc, there's nothing inherently evil in it. It's not a good design, but the world won't come to an end if you use it. We had the same debate at my company several years ago. A designer put in an arc relationship and the dba supporting the team made a huge deal out of it. After some, rather heated discussion, we went with it, putting in a trigger to help with referential integrity. I still don't like the design, but it's functional. But if we didn't have the trigger in place, it wouldn't be. It's way too easy to get bad data and break referential integrity with that approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2010 at 4:35 am
Grant Fritchey (2/10/2010)
If you go with the arc, there's nothing inherently evil in it. It's not a good design, but the world won't come to an end if you use it. We had the same debate at my company several years ago. A designer put in an arc relationship and the dba supporting the team made a huge deal out of it. After some, rather heated discussion, we went with it, putting in a trigger to help with referential integrity. I still don't like the design, but it's functional. But if we didn't have the trigger in place, it wouldn't be. It's way too easy to get bad data and break referential integrity with that approach.
I don't understand what problem you are referring to. In the example I posted and in the comments example it seems that referential integrity is enforced with a constraint rather than a trigger. It is still an optional relationship, but so are all RI constraints in SQL because it's usually impossible (even with a trigger) to enforce a constraint which is mandatory on "both sides" of the constraint.
February 10, 2010 at 5:11 am
I was thinking of using a stored procedure to do this as well. This was my initial sp not taking into account disjoint subtypes yet.
Would this work, or is this a bad idea?
Thanks - Dave
CREATE PROCEDURE [dbo].[proc_Articles_InsertComment]
(
@ArticleId int,
@Body nvarchar(512),
@IsApproved bit,
@CreatedDate datetime,
@createdby uniqueidentifier,
@UpdatedDate datetime,
@UpdatedBy uniqueidentifier,
@CommentId int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Comment (
Body,
IsApproved,
CreatedDate,
CreatedBy,
UpdatedDate,
UpdatedBy )
VALUES (
@Body,
@IsApproved,
@CreatedDate,
@UpdatedDate,
@UpdatedBy )
SET @CommentId = SCOPE_IdENTITY()
INSERT INTO dbo.ArticleComment (ArticleId, CommentId) VALUES (@ArticleId, @CommentId)
END
GO
February 10, 2010 at 5:12 am
David Portas (2/10/2010)
Grant Fritchey (2/10/2010)
If you go with the arc, there's nothing inherently evil in it. It's not a good design, but the world won't come to an end if you use it. We had the same debate at my company several years ago. A designer put in an arc relationship and the dba supporting the team made a huge deal out of it. After some, rather heated discussion, we went with it, putting in a trigger to help with referential integrity. I still don't like the design, but it's functional. But if we didn't have the trigger in place, it wouldn't be. It's way too easy to get bad data and break referential integrity with that approach.I don't understand what problem you are referring to. In the example I posted and in the comments example it seems that referential integrity is enforced with a constraint rather than a trigger. It is still an optional relationship, but so are all RI constraints in SQL because it's usually impossible (even with a trigger) to enforce a constraint which is mandatory on "both sides" of the constraint.
Without the trigger and in the arc design, it's possible to have a comment be designated for more than one object, which is not good. That's the problem I'm referring to. That's why since the comments are distinct to each type that you're dealing with, I'd break them up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2010 at 5:16 am
Grant Fritchey (2/10/2010)
Without the trigger and in the arc design, it's possible to have a comment be designated for more than one object
Not if you use a compound foreign key with a check consraint so that each comment must refer to a unique object of the correct type. See the Product / Books / CDs example I posted.
February 10, 2010 at 5:23 am
David Portas (2/10/2010)
Grant Fritchey (2/10/2010)
Without the trigger and in the arc design, it's possible to have a comment be designated for more than one objectNot if you use a compound foreign key with a check consraint so that each comment must refer to a unique object of the correct type. See the Product / Books / CDs example I posted.
Yeah, that works too. Slightly different structure than we used, but this is good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2010 at 10:01 am
Hi all
I asked this question over at asp.net and had the suggested solution. I presume this is bad but thought I'd ask. Thanks.
In having discussions I have decided to create a more generic comments module that could be used on any entity type.
KEY:
A = Article
I = Photo
E = Event
G = Group
U = User Profile
P = Product
F = Activity Feed
CREATE TABLE dbo.Comment
(
CommentID int IDENTITY(1,1) NOT NULL,
ObjectType char(1) NOT NULL CHECK(CommentType IN ('A','I','E','G','U','P','F')),
XID int NOT NULL,
Body ntext NOT NULL,
ReportedAsSpam bit NOT NULL CONSTRAINT [DF_Comment_ReportedAsSpam] DEFAULT (0),
IsSpam bit NOT NULL CONSTRAINT [DF_Comment_IsSpam] DEFAULT (0),
IsApproved bit NOT NULL CONSTRAINT [DF_Comment_IsApproved] DEFAULT (1),
CreatedDate datetime NOT NULL,
CreatedBy uniqueidentifier NOT NULL,
UpdatedDate datetime NOT NULL,
UpdatedBy uniqueidentifier NOT NULL,
IsDeleted bit NOT NULL CONSTRAINT [DF_Comment_IsDeleted] DEFAULT (0),
CONSTRAINT [PK_Comment] PRIMARY KEY (CommentID),
CONSTRAINT [FK_Comment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),
CONSTRAINT [FK_Comment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId),
CONSTRAINT [UC_Comment_CommentID_ObjectType_XID] UNIQUE (CommentID, ObjectType, XID)
)
Ex: Get all comments for article ID 23.
SELECT * FROM Comment WHERE ObjectType = 'A' and XID = '23'
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply