Multiple one to many relationships in series

database-designnormalization

I am making a DB for storing reports that have following fields:

Ad_system,
Ad_Campaing,
Ad_Group,
Keyword,
Date,
Impressions,
Clicks,
Conversions

Logic is that, Ad_system can have multiple Ad_Campaings, Ad_Campaing can have multiple Ad_Groups and Ad_Groups can have multiple Keywords, and for every Keyword there is a report. Should It be like this? Because it kind of makes sense to me, but it doesn't look correct. Primary key can also be:

ad.system.name for first table

ad.system.name ad.campaing.name for second table

ad.system.name ad.campaing.name ad.group.name for third table

ad.system.name ad.campaing.name ad.group.name keyword for fourth table

They will always be unique, but then Report will always have all DB columns in it, so it's kind of redundant. Is the approach that I have in the diagram below correct? Or is a better one what I just described? If I am not mistaken both should satisfy 3rd NF.

Best Answer

Both are correct in the sense that both are normalized, based on your question. It seems like there isn't a strong consensus on whether to use natural keys or surrogate keys (the ids). Here are a couple of stackexchange questions on the subject:

Related Solutions

Sql-server – How to normalize a database with lots of many-to-many relationships

You should start by writing all the relationships between those tables. From your description, I could not find out how Partner would be related to Client, Program or User. However, the diagram below is a start based on the information provided. Note that you can't use 1 Link table to link all table from what I understand in this case.

EDIT - New ERD based on new requirements

enter image description here

Database Design – different objects with shared tagging

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.

Best Answer

Related Solutions

Sql-server – How to normalize a database with lots of many-to-many relationships

Database Design – different objects with shared tagging

Related Question