1

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.

enter image description here

Michael Green
  • 24,839
  • 13
  • 51
  • 96
user111763
  • 11
  • 1
  • You spelled campaign wrong. Plus, I don't really get why you feel there is redundant data in report when there apparently has to be a report for every keyword. – jera Nov 30 '16 at 13:34
  • If a report may have more than 1 keyword, it would be many to many relationship between report and keyword. – T.Su Dec 01 '16 at 01:25
  • Your model seems to indicate that keywords only appear in one group and one campaign and one system.

    I suspect that's not true, right?

     – Karen Lopez Dec 01 '16 at 23:09

1 Answers1

1

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:

Community
  • 1
Joe Borysko
  • 406
  • 3
  • 7