Database design question

Question

Database design question

Dave Winchester

SSC Enthusiast

Points: 118
More actions
February 1, 2010 at 9:22 am

#386219

Hi all
I am in the process of redesigning a large corporate intranet solution for a customer (+- 120K employees globally). I have run into a few design questions that I thought you'd all be able to help or comment on. I have read loads of articles but none cover the questions that I am asking myself.
Problem 1: Comments and Ratings
The solution has to incorporate multiple comments and ratings for different entities (these are names from my rough design model) - Just to give you an idea.
Solution A
Comment (CommentID PK...)
Article (ArticleID PK...)
Photo (PhotoID PK...)
ArticleComment (ArticleID PK1/FK1, CommentID PK1/FK2)
PhotoComment (PhotoID PK1/FK1, CommentID PK1/FK2)
ProductComment (ProductID PK1/FK1, CommentID PK1/FK2)
My question: Is this a good design? Or should I have multiple tables with the same stucture for different entity Ratings and Comments i.e...
Solution B
ArticleComment (CommentID PK, ArticleID FK1, Body...)
PhotoComment (CommentID PK, PhotoID FK1, Body...)
ProductComment (CommentID PK, ProductID FK1, Body...)
Solution C
ObjectType ex. Article, Event, Group etc...)
Comment (CommentID PK, ObjectTypeID FK1, XID, Body...) Note: XID would be the ID of the specific object in questions.
For ex:
Get All Article Comments: SELECT * FROM Comment Where ObjectType = 'Article'
Get a specific Comment: SELECT * FROM Comment Where XID = '1234'
Problem 2: Categories
The other design question I had was concerning Categorisation of content. Each of the following entities would need a category:
- Article
- Event
- Group
- Gallery etc...
Solution A
Should I have individual tables i.e. ArticleCategory, EventCategory etc... with the same structure?
or
Solution B
Would this be better i.e. CategoryType, Category (CategoryTypeID FK1) and then every table references this one Category table.
So sorry that this is complex and long winded but I had nowhere else to ask. Many thanks in advance.
Regards
David

Viewing 15 posts - 1 through 15 (of 21 total)

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

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

It's best to try to avoid the whole arc relationship if you can. This is when you have a thing, Comment, that can be related to multiple other things, but each of them exclusively. Meaning, one Comment can't be mapped to both an Article and a Photo. So any of the suggested solutions where you have a Comment table which maps in any way to the other three tables... it's just going to lead to problems. Probably, I'd go with three Comments tables and just have them mapped to the three other entities directly. Then, if you need a common way to search on all Comments, create a view that does a UNION between the tables.

"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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

I good question, but we cannot really help you to answer it without more information. First we need to know a bit about your requirements and specifications, and you haven't actually told us anything about them yet. Secondly, the notation that you are using is a wee bit sparse and ambiguous. For instance when you wrote:

Dave Winchester (2/1/2010)
...
Comment (CommentID PK...)
...

I cannot tell if this is supposed to be describing a table or a column. If it's supposed to be a table description (as I suspect), then you need at least one more column described in it for it to make sense to anyone else, as an ID field named after it's own table does not tell us anything about what you intend for this table to actually contain (vs. what is contained in say child tables and merely indexes back to this parent record).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Dave Winchester SSC Enthusiast Points: 118 More actions · Answer 3

Hi

Thanks for the responses. My comments table would look something like this, if I was to implement a table for each entity:

-- Article Comments --

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = N'dbo.ArticleComment' AND TYPE = 'U')

BEGIN

CREATE TABLE dbo.ArticleComment

(

CommentID int IDENTITY(1,1) NOT NULL,

ArticleID int NOT NULL,

Body ntext NOT NULL,

ReportedAsSpam bit NOT NULL CONSTRAINT [DF_ArticleComment_IsSpam] DEFAULT (0),

IsSpam bit NOT NULL CONSTRAINT [DF_ArticleComment_IsSpam] DEFAULT (0),

IsApproved bit NOT NULL CONSTRAINT [DF_ArticleComment_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_ArticleComment_IsDeleted] DEFAULT (0),

CONSTRAINT [PK_ArticleComment] PRIMARY KEY (CommentID),

CONSTRAINT [FK_ArticleComment_ArticleID] FOREIGN KEY (ArticleID) REFERENCES dbo.Article(ArticleID),

CONSTRAINT [FK_ArticleComment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),

CONSTRAINT [FK_ArticleComment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId)

)

END

GO

My main question was surrounding the design of many comment tables - I read a book about SQL Database Design and it made it clear that normalisation rules. So I was going for a full normalised structure but as pointed out above that this "Arc" relationship is a bad idea. So I am now lost.

The brief for my project

Is to re-design an existing application that is sold to our customers as a main online clinical help resource. We have around at present 120K users worldwide (about half of our customer base). The redesign is to help sell it further. The main objective is to implement a social netowrking site that supports all of the things I mentioned previously, but also needs to allow for future development.

It also needs to cater for full approval and moderation on all entities. Hence my idea that a central Comments table would therefore allow for a single point of moderation rather than have different pages to deal with it.

The business focuses purely on EAP and Clinical/Physcoological support for staff members of our clients. It also will in the future need to support 8 languages but that is another story.

Please help.

Thanks

David

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Dave Winchester (2/8/2010)
My main question was surrounding the design of many comment tables - I read a book about SQL Database Design and it made it clear that normalisation rules.

Yes. Yes it does. 🙂

So I was going for a full normalised structure but as pointed out above that this "Arc" relationship is a bad idea. So I am now lost.
...
It also needs to cater for full approval and moderation on all entities. Hence my idea that a central Comments table would therefore allow for a single point of moderation rather than have different pages to deal with it.

Yes, it's a bad idea because it's not really good relational design. That's why Grant was trying to steer you away from it.. just because you two things have the same structure does not necessarily mean that they are the same entity-type.

Off the top of my head, I can think of three approaches to address this:

1) One Table, Three Views: You make a single Comments table, with at least the following columns:

CREATE TABLE Comments (

CommentID int IDENTITY(1,1) NOT NULL,

ParentTypeCode char(1) NOT NULL,

ParentID int NOT NULL,

OwnerID int NOT NULL,

CreatedDate datetime NOT NULL,

Then you make (ParentTypeCode, ParentID, OwnerID, CreatedDate) the Primary Key,

and you make (CommentID) a Unique Clustered Index. Finally, you make three Views, that filter it down for each of the three Parent table types.

This essentially the SOP version of your original idea, the Views help with some of the JOIN problems, but it still retains some of the problems that Grant alluded to: it's not really truly relational, You cannot make good FKs for it (well, you might be able to in SQL 2008 with filtered indexes, I am not sure), etc.

2) Three Tables, One View: Basically what Grant suggested, you make three different Comment tables, with the same key fields as above, except the ParentTypeCode column is not necessary, though you probably want to keep it as you'll see below. Then you make one View that binds them with UNION ALL. If you do it right, you should even be able to make a Partitioned View out of it.

The first problem that you will run into is being able to distinguish the different rows based on their source table. Since they are now three different tables, you can no longer be assured that their identity values do not overlap. The simplest solution to this is either to A) use standard View Partioning tricks to distinguish them, or B) just keep the ParentTypeCode column (or do both). That way the ParentTypeCode column of your ArticleComments would all have 'A', your PhotoComments wold all have an 'I' and your ProductComments would all have a 'P" is this field.

There are some other, less obvious limitations with this design, that all center around just how flexible and interchangeable these messages are supposed to be. The real issues start to popup if in turn, you need to start hanging common (or equivalent) child tables off of *these* tables (like say a common revision history). Then this awkward workaround design starts to snowball out of control.

This leads to our third design, the most complicated, but generally seem as the most "truly relational" of the three:

3) One Comments table and One Connector Table: You keep a single table for all of the comments (I'll describe it in a bit), but you also add a second "Connector" table in-between the Comments table and its parent table. We'll cal this the "CommentDelegates" table, and it should have at least the followong:

CREATE TABLE CommentDelegates (

DelegateID int IDENTITY(1,1) NOT NULL,

[ ParentTypeCode char(1) NOT NULL ]

)

Yep, that's it, and the ParentTypeCode column is actually optional, you can get by without it (though it's very convenient to have it).

Now you modify your Articles, Photos and Products tables to add a column called "CommentDelegateID" that points to this table and your Comments table's key fields look like this:

CREATE TABLE Comments (

CommentID int IDENTITY(1,1) NOT NULL,

DelegateID int NOT NULL,

OwnerID int NOT NULL,

CreatedDate datetime NOT NULL,

[ ParentTypeCode char(1) NOT NULL, ]

Its Primary Key will be (DelegateID, OwnerID, CreatedDate) and again, the ParentTypeCode is optional. This has the advantages of very high flexibility, ability to define the constraints (FKs, etc.) that you need for RI, a single comments table for things like reporting, Moderation, edit/revision history child tables, etc.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Dave Winchester SSC Enthusiast Points: 118 More actions · Answer 5

Thanks again.

OK - This looks alot more complex than I originally thought.

Given the Arc type relationship and that we would want to avoid it - How would you recommend that you design the following scenario. The only reason I ask is that I am also lead developer on a project to rewrite our core CRM for handling Cases, Account Management, Clinical Provider Management + many more modules.

We have Contacts. Which themselves are employees (internal and contracted), clients (people who phone us for assistance), Contacts for Accounts (ex: John Smith, Regional Manager at BT). All of these also then have addresses.

Originally I had for example:

AddressType (AddressTypeID, Name)

Address (AddressID, Line1, Line2, Line3, TownCity, ZipPostcode, StateProvineCounty, Country)

Contact (ContactID, Salutation, Firstname, LastName...)

Account (AccountID, Name, StartDate, EndDate, EmployeeCount...)

Account Contacts and Addresses:

AccountContact (AccountID, ContactID)

AccountAddress (AccountID, AddressID, AddressTypeID)

Contact Addresses:

ContactAddress (ContactID, AddressID, AddressTypeID)

Would this design now be a bad idea? Because, I thought this was normalised based on my understanding?

I am starting to doubt myself and the way I design now more than anything. Any advice?

Thanks again in advance. Kind regards.

David

Dave Winchester SSC Enthusiast Points: 118 More actions · Answer 6

Hi again Bob

I have been trying to understand your previous post re:Comments

If I was to implement option 3. I am unsure of what to do. I don't understand "DelegateID" and "ParentTypeCode".

I have included some tables here with my naming conventions etc. Please could you have a look at let me know where I am suppose to include the mapping table? i.e. How do I know which Comments relate to say for example ArticleID '23'

Sorry to be a pain but I come from a ASP.NET background as an Analyst Programmer and am relatively new to the more complex database designs.

Thanks again.

Regards

David

/* Example: Article, Photo, Product, UserGroup, Event, UserProfile */

CREATE TABLE dbo.ObjectType

(

ObjectTypeID int IDENTITY(1,1) NOT NULL,

Name nvarchar(50) NOT NULL,

Description nvarchar(256) NULL,

CreatedDate datetime NOT NULL,

CreatedBy uniqueidentifier NOT NULL,

UpdatedDate datetime NOT NULL,

UpdatedBy uniqueidentifier NOT NULL,

IsDeleted bit NOT NULL CONSTRAINT [DF_ObjectType_IsDeleted] DEFAULT (0),

CONSTRAINT [PK_ObjectType] PRIMARY KEY (CommentID),

CONSTRAINT [FK_ObjectType_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),

CONSTRAINT [FK_ObjectType_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId)

)

CREATE TABLE dbo.Article

(

ArticleID int IDENTITY(1,1) NOT NULL,

CategoryID int NOT NULL,

Title nvarchar(100) NOT NULL,

Abstract nvarchar(4000) NOT NULL,

Body ntext NULL,

Keywords nvarchar(256) NULL,

RedirectUrl nvarchar(256) NULL,

ImageUrl nvarchar(256) NULL,

ReleaseDate datetime NOT NULL,

ExpireDate datetime NOT NULL,

OnlyForMembers bit NOT NULL CONSTRAINT [DF_Article_OnlyForMembers] DEFAULT (0),

CommentsEnabled bit NOT NULL CONSTRAINT [DF_Article_CommentsEnabled] DEFAULT (1),

VotingEnabled bit NOT NULL CONSTRAINT [DF_Article_VotingEnabled] DEFAULT (1),

IsApproved bit NOT NULL CONSTRAINT [DF_Article_IsApproved] DEFAULT (0),

ViewCount int NOT NULL CONSTRAINT [DF_Article_ViewCount] DEFAULT (0),

CreatedDate datetime NOT NULL,

CreatedBy uniqueidentifier NOT NULL,

UpdatedDate datetime NOT NULL,

UpdatedBy uniqueidentifier NOT NULL,

IsDeleted bit NOT NULL CONSTRAINT [DF_Article_IsDeleted] DEFAULT (0),

CONSTRAINT [PK_Article] PRIMARY KEY (ArticleID),

CONSTRAINT [FK_Article_CategoryID] FOREIGN KEY (CategoryID) REFERENCES dbo.ArticleCategory(CategoryID),

CONSTRAINT [FK_Article_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),

CONSTRAINT [FK_Article_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId)

)

CREATE TABLE dbo.Comment

(

CommentID int IDENTITY(1,1) NOT NULL,

ObjectTypeID 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_ObjectTypeID] FOREIGN KEY (ObjectTypeID) REFERENCES dbo.ObjectType(ObjectTypeID),

CONSTRAINT [FK_Comment_CreatedBy] FOREIGN KEY (CreatedBy) REFERENCES dbo.aspnet_Users(UserId),

CONSTRAINT [FK_Comment_UpdatedBy] FOREIGN KEY (UpdatedBy) REFERENCES dbo.aspnet_Users(UserId)

)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

OK, I think that there is a very important conceptual block that you must address first, before you can ever be comfortable with this. Let's start here:

Dave Winchester (2/9/2010)
...Would this design now be a bad idea? Because, I thought this was normalised based on my understanding?
I am starting to doubt myself and the way I design now more than anything. Any advice?

Now, why do you think that this is normalized? Seriously.

I am not trying to be a jerk here, this is a really important question that is at the heart of why you're going off the tracks on these design questions. Relational DB Design is a very formalized process that follows a series of principles called the Rules of Normalization (thats where the whole joke about "Normalization Rules" comes from). So the question that you should be asking yourself is "Which Normalization Rule is it that makes me think that this is normalized design?"

If you can answer that, then we can look at that rule and try to see just what it means, exactly. However, if you cannot answer that question, then that tells you something else also: that this probably has nothing to do with Normalized Design, but is probably some design principle that you are carrying over from some other design methodology (and don't worry, this is actually quite common).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Dave Winchester SSC Enthusiast Points: 118 More actions · Answer 8

mmmmm...ok. Forgive me if this is a little like RDMS 101 but here I go...

I always thought that 1NF design was a table that was a faithful representation of a record and was free of repititive columns or repeating groups of data.

Example:

PK = Primary Key

FK = Foreign Key

Bad Design:

Contact (Name, TelephoneNumber1, TelephoneNumber2, TelephoneNumber3...)

Good Design:

Contact (Name)

TelephoneNumber (Name, TelephoneNumber)

2NF was only if the table was in 1NF and that all the attributes of a table were dependant on a candidate key (in part or whole).

Example:

Bad Design:

Contact (Salutation, Firstname, Surname, Address) - Contact details are repeated for different addresses. Non of the attributes are/or could be a candidate key for the table as the Contact's details would need to be repeated for multiple addresses.

Good Design:

Contact (ContactID PK, Salutation, Firstname, Surname) - 1 Contact record

ContactAddresss (ContactAddressID PK, ContactID FK1, Address) - Many addresses which are independant of each other but are related by a constraints to a single Contact.

Now what if the above example of Contacts and Addresses were different. Some contacts would share addresses ex: a place of work. You would not want to update multiple addresses records should they move offices, so...

Good Design:

Contact (ContactID PK, Salutation, Firstname, Surname) - 1 Contact record

Addresss (AddressID PK, Address) - 1 Address record

ContactAddress (AddressID PK/Fk1, ContactID PK/FK2) - A contact can have many addresses but many Contacts can also share an Address i.e. A place of work, home address (Joint Tenancies for rentals as a example)

3NF was if the table adhered to 1NF and 2NF and that all records of the table were directly dependant on the table's key. Like the above example.

You can see where I am going with this so I will stop there.

Where am I getting it wrong. I always thought that you designed the database that should you need to change a record, you only ever changed it in one place and not in many tables over many records.

Please help and am losing what little hair I have. Thanks again.

Regards

David

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 9

Dave,

Very loosely speaking you probably have the right understanding of normalization - although it would certainly help you to understand it in more formal terms if you don't already. Normalization actually doesn't have anything to say about putting the same rows of data in multiple tables or not. That is however the concern of a different design principle that is often called the "Principle of Orthogonal Design" (POD). POD (roughly speaking) says that it should not be possible for the same fact (tuple or part of a tuple including a proper superkey) to appear in more than one place in the schema. So by that rule, two tables should not have the same set of attributes in common unless all of them are candidate key attributes. Like normalization, POD eliminates a certain type of redundancy and potentially simplifies data access logic.

The optional type of relationship, where a referencing row may exist in one of several tables is in fact a common one in data modelling. It can be implemented declaratively in SQL:

http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx

Dave Winchester SSC Enthusiast Points: 118 More actions · Answer 10

Hi David

Thanks for the explanation. I am hitting google as we speak.

The link you sent does not seem to work - just thought I'd let you know. Thanks again.

Dave

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 11

Sorry, my blog is temporarily offline.

This is my example of a disjoint subtype pattern:

CREATE TABLE Products

(SKU INT NOT NULL PRIMARY KEY,

ProductType CHAR(1) NOT NULL

CHECK (ProductType IN ('B','C','D' /* Book, CD or DVD */)),

Title VARCHAR(50) NOT NULL,

UNIQUE (SKU,ProductType));

CREATE TABLE Books

(SKU INT NOT NULL PRIMARY KEY,

ProductType CHAR(1) DEFAULT 'B' NOT NULL, CHECK (ProductType ='B'),

Pages SMALLINT NOT NULL,

FOREIGN KEY (SKU,ProductType) REFERENCES Products (SKU,ProductType));

CREATE TABLE CDs

(SKU INT NOT NULL PRIMARY KEY,

ProductType CHAR(1) DEFAULT 'C' NOT NULL, CHECK (ProductType ='C'),

Tracks SMALLINT NOT NULL,

FOREIGN KEY (SKU,ProductType) REFERENCES Products (SKU,ProductType));

CREATE TABLE DVDs

(SKU INT NOT NULL PRIMARY KEY,

ProductType CHAR(1) DEFAULT 'D' NOT NULL, CHECK (ProductType ='D'),

Duration SMALLINT NOT NULL,

FOREIGN KEY (SKU,ProductType) REFERENCES Products (SKU,ProductType));

The disjoint requirement is always enforced by the combination of the compound foreign key constraints and check constraints.