Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
It looks like you don't have the most important question answered - How will be data accessed? - For which queries/statements do you want to "tune" your model? - How do you plan to expand the functionality? I think that there is no general best practice - solution deppends on answers of these questions. And it starts to matter even in the simple models like this one. Or you may end up with model that follows some higher principles but really sucks in the most important scenarios - those seen by users of the system. –  Štefan Oravec Mar 2 '13 at 14:49
    
Good point! I'll have to spend some time thinking about this! –  matikin9 Mar 5 '13 at 18:15

4 Answers 4

up vote 6 down vote accepted

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.

share|improve this answer
    
The tri-link table you mention was probably a result of me being mentally lazy... :P After reading the various answers, I'm thinking neither of my initial options make sense. Having separate separate ReportKeywords and RecommendationKeywords tables makes more practical sense. I was considering scalability, in terms of potentially having more objects that needed keywords applied, but realistically there's probably only one more object type that could need keywords. –  matikin9 Mar 5 '13 at 19:36

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.

share|improve this answer
    
I do not disagree that what you are suggesting is a viable option but why can't RI be enforced with OP's design B? (I assume that is what you are saying). –  ypercube Mar 3 '13 at 8:32
    
@ypercube: I think I missed the BaseObjects table on my first read-through, and thought I was seeing a description for a table where object_id can point to an ID in any table. –  FrustratedWithFormsDesigner Mar 4 '13 at 3:52

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.

share|improve this answer
    
I'm using MS SQL Server for this project, but thanks for the info on PostgreSQL. The other points you brought up about deleting and making sure object-keyword pairs each occur only once. Even if I had keys for each object-keyword pair though, wouldn't I still have to check before inserting? As for having a separate keyword id... I read that for SQL Server, having a long-ish string could reduce performance, and I'm probably going to have to allow users to enter "key phrases" rather than just "key words". –  matikin9 Mar 5 '13 at 19:27

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.

share|improve this answer
    
Let me explain why I downvoted: 1. It's not clear if you are in favour of option A, B or a 3rd approach. It seems (to me) that you say that both are more or less OK (with which I disagree because A has several problem that others have outlined with their answers. 2. Are you suggesting making improvements on the design of A (or B)? It's not clear either. It would also be good to have the FKs defined clearly, it's not at all obvious what you are suggesting. In total I like answers that clarify things and options for any future visitor. Please try to edit your answer and I'll reverse my vote. –  ypercube Mar 3 '13 at 8:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.