Architecture :: Generic Comments Module (Database Design And Implementation)

Feb 10, 2010

I am in the process of redesigning our main product, a knowledge database system for clients to access clinical and specialist advice articles. There is a requirement to add "Social Networking" to this allowing users to connect and post remarks etc...

This is somewhat similar to the Facebook wall scenario - where Articles, Photos, Groups, Products, Events, ActivityFeed can all have comments. I am at a lose as to the database design I would need to implement this. All comments must allow for Moderation/Approval/Spam Reporting etc...

I originally thought a individual table for each Comments entity ex: ArticleComment, ProductComment would work as the comments themselves are not all directly related. Each table having the same exact columns. But this makes life abit awkward as then this creates a separation between something that for all purposes is the same i.e. They are all comments.

The other design idea was to have a single Comment table with an "arc" type relationship to related tables with FK references back to the source.

The other option was to have a single Comment table with an Object Type and XID but this breaks the rules of relation and does not all for referential integrity.

I am at a lose and don't know what to do. I have asked over at SQLCentral.com and all I have done is raised more questions than answers about what to do.

Design 1: This would require exact copies of this table for all the entities.

-- Photo Comments --
CREATE TABLE dbo.PhotoComment
(
CommentID int IDENTITY(1,1) NOT NULL,
PhotoID int NOT NULL,
Body ntext NOT NULL,
ReportedAsSpam bit NOT NULL CONSTRAINT [DF_PhotoComment_ReportedAsSpam] DEFAULT (0),
IsSpam bit NOT NULL CONSTRAINT [DF_PhotoComment_IsSpam] DEFAULT (0),
IsApproved bit NOT NULL CONSTRAINT [DF_PhotoComment_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_PhotoComment_IsDeleted] DEFAULT (0),
CONSTRAINT [PK_PhotoComment] PRIMARY KEY (CommentID),
CONSTRAINT [FK_PhotoComment_PhotoID] FOREIGN KEY (PhotoID) REFERENCES dbo.Photo(PhotoID),
CONSTRAINT [FK_PhotoComment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),
CONSTRAINT [FK_PhotoComment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId)
)

Design 2: The "Arc" relationship

CREATE TABLE dbo.Comment
(
CommentID int IDENTITY(1,1) NOT NULL,
CommentType char(1) NOT NULL CHECK(CommentType IN ('A','I','E','G','U','P')),
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_CommentType] UNIQUE (CommentID, CommentType)
)

-- Article Comments --
CREATE TABLE dbo.ArticleComment
(
ArticleID int NOT NULL,
CommentID int NOT NULL,
CommentType char(1) CONSTRAINT [DF_ArticleComment_CommentType] DEFAULT 'A', CHECK (CommentType = 'A'),
CONSTRAINT [PK_ArticleComment] PRIMARY KEY CLUSTERED (ArticleID, CommentID),
CONSTRAINT [FK_ArticleComment_ArticleID] FOREIGN KEY (ArticleID) REFERENCES dbo.Article(ArticleID),
CONSTRAINT [FK_ArticleComment_CommentID] FOREIGN KEY (CommentID) REFERENCES dbo.Comment(CommentID),
CONSTRAINT [FK_ArticleComment_CommentID_CommentType] FOREIGN KEY (CommentID, CommentType) REFERENCES dbo.Comment(CommentID, CommentType)
)

Design 3: The one that breaks the rules

CREATE TABLE dbo.Comment
(
CommentID int IDENTITY(1,1) NOT NULL,
ObjectType char(1) NOT NULL, /* A = Article, P = Photo etc... */
XID int NOT NULL, /* Would be the ID of the main entity being queried. */
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'

View 1 Replies


Similar Messages:

Architecture :: Database Design For Parent-child Module?

Feb 15, 2011

i am making an application which is like chain system..i mean one person can have multiple child.. then his child can have multiple child ..etc like chain system...i dont know to insert into database.. so that when i call any-node.. his child,, and their futher child will show...

View 14 Replies

Architecture :: Function Implementation In 3-tier Architecture?

Apr 28, 2010

I have implemented the 3-tier architecture project; DAL, BAL, and presentation layer are three layers in it. I am going to implement the following scenario:

Presentation layer calls to a function in the BAL layer which returns 2 string variable, and in turn this function from BAL layer calls to a DAL layer function which also returns 2 string variable. How can I implement the above scenario?

View 2 Replies

Architecture :: How To Design Small Web Project And Database

Oct 7, 2010

how to design following small webproject and database .i am totally new in this area.

I need to quickly develop a system so that everyone can access the status of the job going online into the system

Activities

Move the job between work centers when it's completed.

Transfer button. So when design is done we should be able to click the transfer button and move the job from Design to Procurement

Here are the work centers.

plan cerement TST Shipped Each work center should have due dates User will have to update manually due date for each work center and this will be different for each job.

View 1 Replies

Architecture :: How To Configure Database Design When Add A Sub Category

Apr 24, 2010

I have a ordinary Category/Product database relationship Design structure.

Now i wish to add a sub category to this architecture.

for example, Cloths Category needs to be sub divided - Women,Men,Children categories.Also what about other Ctegories such as: Car,this also has sub divisions: Model,Type.

My Products table contains ProductName field together with generic field for Size,Colour and Price fields.

How do you therefore incorporate this sub division into the existing Category/Product relationship model and is there a model example?

View 7 Replies

Architecture :: How To Design A Map With Co-ordinates That's Driven From Data In A Database

Jan 17, 2010

I didn't really know where and how to put this so here we go:

I'm trying to design a map with co-ordinates that's driven from data in a database. I want it to look something like this:

Also, the selected X/Y co-ordinate must be centered for the user to see. How could I produce something like this?

View 5 Replies

Architecture :: Database Design For Internal Email Messaging?

Mar 19, 2010

for years now I have had a problem with trying to design the best database design for an internal email messaging system for a website.

I would have thought that this would be a fairly easy thing to think of at this point in my development history but it still eludes me to no end.

What irratates me even more is that I would think i could find something online giving more detail about it but i have yet to find the right search term to use

View 6 Replies

Iis7 - Design And Implementation For Short URLs On IIS And .NET Stack?

Mar 7, 2011

I'm interested in creating "short URLs" a segment of pages on a site. However, this isn't in the traditional sense of "short URLs" like bit.ly where it will redirect to a different destination URL. I want the short URL to be the final destination.For example, one of these URLs might be http://foo/a/Afjbg, and when you navigate to it, it stays on http://foo/a/Afjbg (IOW, http://foo/a/Afjbg is visible to the user in the address bar).

If it was static content, I would just arrange the pages and folders into these names. But the content I will have on the site will be dynamically driven from a DB, so each page is generated on the fly. So the content looks logically different, but in reality is essentially the same .aspx page with dynamic content.How can this be accomplished on a Microsoft hosting stack? The platform is IIS 7 with ASP.NET 4.

View 3 Replies

Jquery - MVC 2 - Finding Best Implementation Of Status / Update / Generic Message Delivery And JavaScript Modal Display

Oct 5, 2010

For an MVC 2 app that relies on many partial views and almost exclusively uses Ajax for POSTs/GETs, what would be the best way to implement the setting, passing, retrieval and display (using a JavaScript modal) of these messages?

My forms all POST (by way of jQuery $.ajax) to actions that return partial views (html) that are used to update a in the "success:" part of the $.ajax function.

I was hoping for some sort of mechanism in the master view that could "listen" for any messages that any of these partial views might be "delivering"--through their ViewData, for instance.

View 2 Replies

Architecture :: A Proper DI Implementation?

Feb 27, 2011

I'm fairly new to dependency injection but it seems like a proper DI implementation will be fairly complex.

For example, DI requires a centralized class that manages the configuration and resolves the dependencies at runtime.

DI is also based on the concept of using interfaces. For example, a SpecialLogger should use an ILogger interface.

The centralized DI manager class will need to register types - for example, associate ILogger to SpecialLogger.

SpecialLogger will also need to implement the ILogger interface so SpecialLogger can be used through the DI ILogger interface.

Therefore, it seems like a sln using DI will need multiple projects to support DI. Here is an example for logging:

* MyCompany.MyDivision.Framework.DI.Management - this would have the DI manager where dependency types are registered and resolved at runtime
* MyCompany.MyDivision.Framework.Logging - this would have the implementation of a logging class. The main logging class would need to implement ILogger.
* MyCompany.MyDivision.Framework.DI.Interfaces - this would have the ILogger interface.

Interfaces would need to be stored in a separate class library from the DI manager because both the DI manager and SpecialLogger use the ILogger interface. Since the DI manager associates SpecialLogger to ILogger a circular reference would be encountered without a separate class library to store the ILogger interface.

View 1 Replies

C# - Proper Implementation Of N-layer Architecture?

Feb 28, 2011

I have been learning C# for the last year or so and trying to incorporate best practices along the way. Between StackOverflow and other web resources, I thought I was on the right track to properly separating my concerns, but now I am having some doubts and want to make sure I am going down the right path before I convert my entire website over to this new architecture.

The current website is old ASP VBscript and has a existing database that is pretty ugly (no foreign keys and such) so at least for the first version in .NET I do not want to use and have to learn any ORM tools at this time.

I have the following items that are in separate namespaces and setup so that the UI layer can only see the DTOs and Business layers, and the Data layer can only be seen from the Business layer. Here is a simple example:

[Code]....

Am I completely off base? Should I also have the same properties in my BLL and not pass back DTOs to my UI? what is wrong and what is right. Keep in mind I am not a expert yet.

I would like to implement interfaces to my architecture, but I am still learning how to do that.

View 4 Replies

Website Translation Architecture And Implementation?

Mar 18, 2011

I'm conducting a project in which a website should have multi-language support.Now, this website is supposed to serve about 500K+ visitors a day, so it must be super-efficient.

I've created a table of parameters {[ID],[Name]} AND a linkage-table {[objectID],[parameterID],[languageID],[value]}. I think it's the best way to deploy multi-language support while having the privilege to translate different parameters for each language.

As far as I know, server's memory is much faster than a physical HDD. Therefore, I'm planning to store ASP.NET Application State objects for my translation architecture.[URL]

View 3 Replies

Architecture :: Implementation Of User Authentication In C#?

Feb 15, 2011

I am working on a web application project with a layered architectural style having DAL, BLL, Service Layer and Presentation Layer. It's going to be a Web forms application.

My intent is to try using some of the new features of .Net 3.5 or 4.0.

Currently, I am thinking through different approaches for implementing Authentication in this project.

I have a query regarding the design of the application, particularly Authentication.

In which layer should I have Authentication class? BLL? If I implement the Authentication class in BLL, should I be having an app.config in the same class library project to contain the Database connection string and all.

View 7 Replies

Architecture :: Key, Value Pairs Implementation In Class?

Feb 16, 2010

In a project im working on there are many sql tables containing different ID, Name pairs. Those are represented as Enums in classes that need them however that requires casting since tables contain ID only.Also when serialize the Enum contains the ID not value.

View 3 Replies

Architecture :: Search Engine Implementation?

Jul 22, 2010

not sure if I'm posting to the right thread or not.. but here is the questiondoes anyone know how to implement a best pratice on SEO?One of the example is: if the user types "brand new canon camera" then I want the system to find a sequence of string in the database that contains "new", "cannon", "camera". It is ok with or without the word "brand". However if the user types "HP laptop DV1000" and i want the system to find all HP laptop whether new or not but limited to specific series.

View 3 Replies

Architecture :: How To Design A Centralized Business Or Service Authentication Architecture

Sep 22, 2010

i want to create a centralised business or Service authendication architecture in .net. for example, we have a clients like c1, c2, c3, c4, ... etc. everybody logins seperatly as well as grouply. ie, if client "C1" logins [with login authentication] he can access c2 , c3, c4 also without login authendication. So its like a google. if we enters gmail account, we can access orkut, picasa like that.. i need the cetralised architecture.

And, client "c1" seperately asks seperately how will be the authendication architecture.

so give me the single solution for both these two scenarios. how will be the architecture for these two and how is the Data Base (Login) Structure.

View 3 Replies

Architecture :: Entity Model Design Framework And N - Tier Architecture

Dec 25, 2010

recently i've studied on ADO.NET's Entity Model Framework and say 'wow' as ORM is one of the fevourite pattern i practice..but suddenly i've come to an ambiguous situation when i'm going to start. i usually follow the following 3-tier architecture..

1. UI Layer
2. BLL - business logic layer
3. DAL - Data Access Layer
a. DTO / DAO
b. Gateway (contains the sql query/stored procedure and connection with DB)

now when i'm going to use the Entity Model Design,where the DBML/ .edmx File should be placed? Because many a times i'm using the DBML file as DTO because of the mapped objects.. in the same time, sometimes DBML ( .edmx file in .NET 4.0) contains CRUD methods and stored procedured method as well as methods with different selection operations,- which should be in Gateway. so where the .edmx file should be placed !?!! IN DTO namespace !? or in Gateway namespace!

moreover sometimes there is no need for the BLL which breaks the rules of inter-layer-communication (UI > BLL > DAL.Gateway)! what makes me confuse is, what should be the ideal n-tier architecture when i'll use the ADO.NET Entity Model Design Framework

View 4 Replies

Architecture :: What's The Advantages / Disadvantages Of Explicit Interface Implementation

Jul 19, 2010

I am searching for the advantages and disadvantages of the explicit interface implementation

View 1 Replies

Architecture :: Creating A Comments Page For Users To Review And Comment On An Article?

Dec 12, 2010

I am trying to create a comments page for users to review and comment on an article. I have 2 buttons on each comments posted by users; "Like" and "Don't Like". I am stuck with how to track which user voted already for a given comment. I am thinking of two options and would like to know if they are they way to go or is there a better solution.

Option 1: I have created a table in my database with stores, the comment id, the userid of the user who rated it and the value "like" or "don't like". So each time I have to query my table to find out if this user indeed vote for a given comment. The table unfortunately grows exponentially!!

Option 2: I store the commentid, userid, and "like" or "don't like" value in a cookie on the client's machine. I read the cookie and find out if the user has already voted on a comment. This is proving to be VERY quirky with cookie expiration, growing cookie size and also multiple users on the same machine.

In either way, my test case of ~1500 users, and 2 Million comments, this is getting to be HEAVY on both methods. Is there anything better?

View 3 Replies

Architecture :: Design Pattern And Tier Architecture

Jun 12, 2010

I am a newbie to asp.net and work in a firm where the projects are quite small.

I was told by my manager that in a few weeks or so we would be getting a bigger project and I need to be well versed with Design Patterns and N tier arcihtecture.

I would really appreciate if someone could provide me some links and also drop me a few sentences on how this things are useful?

View 4 Replies

Architecture :: Create†architecture†design For WCF Service?

Aug 10, 2010

I am try to create architecture design for WCF service.

We have WCF service that we have to expose to third party so then can request with xml and get back xml response.

The wcf service should do the following:

- Accept the request call with xml

- Check xml against the schema

- Parse the xml

- Authenticate the incoming xml by username and password that will be in xml

- Send back the response

If anybody can let me know what kind of design I can use or is there any pattern available that I can take it and then extend it as per my requirement.

View 2 Replies

Access :: Database Architecture - How†to Design The Data Access?

Feb 12, 2010

I am starting a new project in ASP.NET (With silverlight) - I would like to get expert opinion about how to design the data access.I can use DataAccess Layer with SQL helper, but the challenge is every new field that I add needs to be added in the sql helper.I am trying to see if there is a way to design the system, so that it appears in the front end when a new field is added. I don't want to have in the ASPX files (binding in controls) I want to have ability to change items in code. Essentially I am trying to see the best option to have a database application.

View 1 Replies

Architecture :: Architecture And Design Patterns?

Mar 16, 2011

hat is the difference between Architecture and Design patterns ?

View 8 Replies

Social Networking :: Display Facebook Comments And Allow User To Post Comments To Wall

May 7, 2013

How user post comment on my website  using gmail ,fb,yahoo ,hotmail account ...

how to apply this.. [URL] ....

View 1 Replies

Architecture :: Generic Framework Package?

Dec 22, 2010

I have worked on larger projects in the past, and am working independently on a new project without the aid of the base code I have used in the past. Is there a recommended open source framework that contains the basic essentials (error handling, security, general structure) that can help me jump-start the development process. I will be using VS2010/Sql Server 2008/Windows Server 2008.

View 2 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved