Okay, I'm back. (Ugh, I ate too much, too late?! Let's hope I can respond to this critical post in an intelligent way...)
Originally Posted by Old Pedant
You missed showing the SUBSECTION_ARTICLE table. <grin/> But I think that's pretty well implied by everything else.
No, I just wasn't sure if I needed that or something else.
I don't think you changed my thinking much.
The truly important table here really is that SUBSECTION_ARTICLE table, no?
The other tables are just a way to navigate from SECTION down to an appropriate collection of SUBSECTIONs.
Yes and No.
On one hand, an Article only maps to a Sub-Section, because a Sub-Section is where the Article Link and Article are ultimately displayed.
But on the other hand, the way a user accesses an Article, is by going to a URL like this...
In this situation, my script will...
1.) Check for a valid "Section"
2.) Check for a valid "Sub-Section"
3.) And/or check that "finance" and "online" are a valid combination in some table (e.g. SECTION_DIMENSION_SUBSECTION) or in some query.
Regardless, I will need the SECTION table properly joined to the DIMENSION table properly joined to the SUBSECTION table to validate part of the URL above.
4.) Lastly, I will need to validate that an Article can be found using the "section_slug" and "dimension_slug" and "article_slug" from the above URL.
While an ARTICLE only directly maps to a SUBSECTION, it is the valid combination of a "Section" and "SubSection" - via a "Dimension" - and "Article" that allows an ARTICLE to be served up.
And that means that everything you have said and done indeed makes sense.
Sure, you *COULD* have done this without the DIMENSIONs. In essence, all you'd do would be to rename your subsections:
DIMENSION Featured Legal + SUBSECTION Tax Law
would convert to
DIMENSIONSUBSECTION Featured Legal Tax Law
Here is the problem....
SECTION -||-------|<- SECTION_DIMENSION ->|---------||- DIMENSION
DIMENSION -||------|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
SUBSECTION ->|------||- ARTICLE
There is no way to produce the "section_slug", "subsection_slug", and "article_slug" in one query from the above Tables and Relationships...
I could create a new SECTION_SUBSECTION table, but that would bypass any Business Rules that I have in the SECTION_DIMENSION and DIMENSION_SUBSECTION junction tables...
I could create a new SECTION_DIMENSION_SUBSECTION table, but that too would by-pass any Business Rules that I have in the SECTION_DIMENSION and DIMENSION_SUBSECTION junction tables...
And I could get rid of the SECTION_DIMENSION and DIMENSION_SUBSECTION tables, and instead create a new SECTION_DIMENSION_SUBSECTION table, but that is getting larger, less manageable, and technically more de-normalized, right?
Plus, that still wouldn't account for the relationship between a SUBSECTION and ARTICLE?!
So maybe then I'd get rid of SECTION_DIMENSION and DIMENSION_SUBSECTION, and the new SECTION_DIMENSION_SUBSECTION table, plus the SUBSECTION_ARTICLE table, and just create a monster SECTION_DIMENSION_SUBSECTION_ARTICLE table, and treat the other tables as "feeder", "lookup" tables?!
But that doesn't seem right...
Or you could have done it with a single table. The one you showed, just above, SECTION_DIMENSION_SUBSECTION_ARTICLE. If you wanted, you truly could implement it like that, perhaps using ENUM values for the Dimension and Sub-section fields.
In other words, remove the "Natural Keys" - the motivation for this thread
- and just use "Derived Keys" to make the table more streamlined?
So to me this isn't really a question about database organization, per se, as just a way for you to make it easier for you to VIEW the organization.
I dunno. I was feeling like I just about had this before I left the library, but now I feel like I'm stuck back where I was when I started this thread...
If I'm wrong, then your answer to the following question would have to be "yes":
Question: Would there ever be a case where there would be an ARTICLE in a given SUBSECTION that would need to be *excluded* because of the SECTION it is in?
If I understand the question...
No, and ARTICLE and SUBSECTION(S) are "tightly-bound" so the SECTION wouldn't directly impact the relationship.
However, an ARTICLE would be excluded from a SUBSECTION if it had nothing to do with the SUBSECTION and thus SECTION.
"how-to-create-a-great-marketing-campaign.php" would not be in any of these DIMENSIONS: Business-Structure, Store-Type, Offering, Accounting, or Featured-Legal
Nor would it be in either of these SECTIONS: Finance, Legal
Because that ARTICLE has no logical relationship to either of those SECTIONS or DIMENSIONS.
However, it would be in a Sub-Section like "advertising" in a Section like "Marketing".
It sure looks to me like the answer to that is "no." In which case I think you are 100% on the right track.
I'll see what you think after reading my comments and concerns above.
Thanks for all of your help so far!!