Database Design - different objects with shared tagging

Question

My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the database for an application I'll be coding.

The situation: I've got Reports in one table and Recommendations in another table. Each Report can have many Recommendations. I also have a separate table for Keywords (to implement tagging). However, I want to have just one set of keywords that gets applied to both Reports and Recommendations so that searching on keywords gives you Reports and Recommendations as results.

Here's the structure I started out with:

Reports
----------
ReportID
ReportName


Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)


Keywords
----------
KeywordID
KeywordName


ObjectKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)
RecommendationID (foreign key)

Instinctively, I feel like this isn't optimal and that I should have my taggable objects inherit from a common parent, and have that comment parent be tagged, which would give the following structure:

BaseObjects
----------
ObjectID (primary key)
ObjectType


Reports
----------
ObjectID_Report (foreign key)
ReportName


Recommendations
----------
ObjectID_Recommendation (foreign key)
RecommendationName
ObjectID_Report (foreign key)


Keywords
----------
KeywordID (primary key)
KeywordName


ObjectKeywords
----------
ObjectID (foreign key)
KeywordID (foreign key)

Should I go with this second structure? Am I missing any important concerns here? Also, if I do go with the second, what should I use as a non-generic name to replace "Object"?

Update:

I'm using SQL Server for this project. It's an internal application with a small number of non-concurrent users, so I don't anticipate a high load. In terms of usage, the keywords will likely be used sparingly. It's pretty much just for statistical reporting purposes. In that sense, whatever solution I go with will probably only affect any developers that will need to maintain this system down the line... but I figured it's good to implement good practices whenever I can. Thanks for all the insight!

Answer 1

The problem with your first example is the tri-link table. Is that going to require one of the foreign keys on either report or recommendations to always be NULL so that keywords link only one way or the other?

In the case of your second example, the joining from the base to the derived tables now may require use of the type selector or LEFT JOINs depending on how you do it.

Given that, why not just make it explicit and eliminate all the NULLs and LEFT JOINs?

Reports
----------
ReportID
ReportName


Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)


Keywords
----------
KeywordID
KeywordName


ReportKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)

RecommendationKeywords
----------
KeywordID (foreign key)
RecommendationID (foreign key)

In this scenario when you add something else which needs to be tagged, you just add the entity table and the linkage table.

Then your search results look like this (see there is still type selection going on and turning them into generics at the object results level if you want a single results list):

SELECT CAST('REPORT' AS VARCHAR(15)) AS ResultType
    ,Reports.ReportID AS ObjectID
    ,Reports.ReportName AS ObjectName
FROM Keywords
INNER JOIN ReportKeywords
    ON ReportKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Reports
    ON Reports.ReportID = ReportKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'
UNION ALL
SELECT 'RECOMMENDATION' AS ResultType
    ,Recommendations.RecommendationID AS ObjectID
    ,Recommendations.RecommendationName AS ObjectName
FROM Keywords
INNER JOIN RecommendationKeywords
    ON RecommendationKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Recommendations
    ON Recommendations.RecommendationID = RecommendationKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'

No matter what, somewhere there is going to be type selection and some kind of branching going on.

If you look at how you would do this in your option 1, it's similar but with either a CASE statement or LEFT JOINs and a COALESCE. As you expand your option 2 with more things being linked, you have to keep adding more LEFT JOINs where things are typically NOT being found (an object that is linked can only have one derived table which is valid).

I don't think there is anything fundamentally wrong with your option 2, and you could actually make it look like this proposal with a use of views.

In your option 1, I have some difficulty seeing why you opted for the tri-link table.

Answer 2

First, note that the ideal solution depends to some extent on which RDBMS you use. I am going to give then both the standard and the PostgreSQL-specific answer.

Normalized, Standard Answer

The standard answer is to have two join tables.

Suppose we have our tables:

CREATE TABLE keywords (
     kword text
);

CREATE TABLE reports (
     id serial not null unique,
     ...
);

CREATE TABLE recommendations (
     id serial not null unique,
     ...
);

CREATE TABLE report_keywords (
     report_id int not null references reports(id),
     keyword text not null references keyword(kword),
     primary key (report_id, keyword)
);

CREATE TABLE recommendation_keywords (
     recommendation_id int not null references recommendation(id),
     keyword text not null references keyword(kword),
     primary key (recommendation_id, keyword)
);

This approach follows all the standard normalization rules, and doesn't break traditional database normalization principles. It should work on any RDBMS.

PostgreSQL-specific Answer, N1NF design

First, a word on why PostgreSQL is different. PostgreSQL supports a number of very useful ways to use indexes over arrays, most notably using what is known as GIN indexes. These can benefit performance quite a bit if used properly here. Because PostgreSQL can "reach into" data types in this way, the basic assumption of atomicity and normalization is somewhat problematic to apply rigidly here. So for this reason, my recommendation would be to break first normal form's atomicity rule and rely on GIN indexes for better performance.

A second note here is that while this gives better performance, it adds some headaches because you will have some manual work to do to get referential integrity to work right. So the tradeoff here is performance for manual work.

CREATE TABLE keyword (
    kword text primary key
);

CREATE FUNCTION check_keywords(in_kwords text[]) RETURNS BOOL LANGUAGE SQL AS $$

WITH kwords AS ( SELECT array_agg(kword) as kwords FROM keyword),
     empty AS (SELECT count(*) = 0 AS test FROM unnest($1)) 
SELECT bool_and(val = ANY(kwords.kwords))
  FROM unnest($1) val
 UNION
SELECT test FROM empty WHERE test;
$$;

CREATE TABLE reports (
     id serial not null unique,
     ...
     keywords text[]   
);

CREATE TABLE recommendations (
     id serial not null unique,
     ...
     keywords text[]  
);

Now we have to add triggers to ensure that keywords are managed properly.

CREATE OR REPLACE FUNCTION trigger_keyword_check() RETURNS TRIGGER
LANGUAGE PLPGSQL AS
$$
BEGIN
    IF check_keywords(new.keywords) THEN RETURN NEW
    ELSE RAISE EXCEPTION 'unknown keyword entered'
    END IF;
END;
$$;

CREATE CONSTRAINT TRIGGER check_keywords AFTER INSERT OR UPDATE TO reports
WHEN (old.keywords <> new.keywords)
FOR EACH ROW EXECUTE PROCEDURE trigger_keyword_check();

CREATE CONSTRAINT TRIGGER check_keywords AFTER INSERT OR UPDATE 
TO recommendations
WHEN (old.keywords <> new.keywords)
FOR EACH ROW EXECUTE PROCEDURE trigger_keyword_check();

Secondly we have to decide what to do when a keyword is removed. As it stands right now, a keyword removed from the keyword table will not cascade to the keywords fields. Maybe this is desirable and maybe not. The simplest thing to do is just restrict the delete always and expect you will manually handle this case if it comes up (use a trigger for safety here). Another option may be to rewrite every keywords value where the keyword exists to remove it. Again a trigger would be the way to do that as well.

The large advantage of this solution is that you can index for very fast lookups by keyword, and you can pull all tags without a join. The disadvantage is that removing a keyword is a pain, and will not perform well even on a good day. This may be acceptable because it is a rare event and could be consigned to a background process but it is a tradeoff worth understanding.

Critiquing your First Solution

The real problem with your first solution is that you have no possible key on ObjectKeywords. Consequently you have a problem where you cannot guarantee that each key word is applied to each object only once.

Your second solution is a bit better. If you don't like the other solutions offered, I would suggest going with it. I would however suggest getting rid of keyword_id and just joining on the keyword text. That eliminates a join without denormalizing.

Answer 3

I would suggest two separate structures:

report_keywords
---------------
  Report ID
  Keyword ID

recommendation_keywords
-----------------------
  recommendation_id
  keyword_id

This way you don't have all possible entity ids in the same table (which is not very scalable, and could be confusing), and you don't have a table with a generic "object id" which you have to disambiguate somewhere else using the base_object table, which will work, but I think overcomplicates the design.

Answer 4

In my experience this is what you can do.

Reports
----------
Report_id (primary_key)
Report_name

Recommendations
----------------
Recommendation_id (primary key)
Recommendation_name
Report_id (foreign key)

Keywords
----------
Keyword_id (primary key)
Keyword

And for the relation between keywords, reports and recommendations you can do one of two options: Option A:

Recommendation_keywords
------------------------
Recommendation_id(foreign_key)
keyword_id (foreign_key)

This allows for a direct relation from Reports to Recommendations, to Keywords and finally to Keywords. Option B:

object_keywords
---------------
Object_id
Object_type
Keyword_id(foreign_key)

The option A is the easier to apply and manage since it will have the constratints of the database to handle the data integrity and won't allow the insertion of invalid data.

The option B although requires a little more work since you will need to code the identification of the relationship. Is more flexible in the long run, if by any chance at some point in the future you need to add keywords to another item other than the report or the recommendation you just need to add the identification and use directly the table.