Need help with Many-to-Many-to-Many
I am revamping my website and how Articles get categorized and stored.
While my new approach makes sense to me, it may take some explaining here?!
My website will ultimately have thousands of Articles on a plethora of topics related to Small-Business. (Think "NY Times" on steroids!!) :D
So, in order to manage all of this content, my website is divided into these "Sections":
(This list could change, but it is unlikely.)
Because of all the topics I will be covering on Small-Businesses, I came up with maybe 100 "Sub-Sections" - which is pretty unwieldy?!
So, to group things together, I created an *intermediary* concept called a "Dimension", which is a grouping of "Sub-Sections"
(**NOTE: A key concept to get here, is that on the Back-End I am breaking down Articles down into 3 groupings: Section > Dimension > SubSection. However, in my URL, I am only displaying 2 groupings: Section > SubSection This maybe makes my database a little tricker...)
In database-speak, here is what I have so far...
- One SECTION can have one or more DIMENSIONS
- One DIMENSION belongs to one or more SECTIONS
- Some DIMENSIONS will only every map to one SECTION
(e.g. Accounting subsection ---> Finance section)
(e.g. Featured_Legal subsection ---> Legal section)
- One DIMENSION can have one or more SUBSECTIONS
- One SUBSECTION will likely only map to one DIMENSION
(But I am allowing for one SUBSECTION to have one or more DIMENSIONS)
- One SUBSECTION will have one or more ARTICLES
- One ARTICLE will have one or more SUBSECTIONS
- An ARTICLE cannot exist outside of a SUBSECTION
SECTION -||-------|<- SECTION_DIMENSION ->|---------||- DIMENSION
DIMENSION -||------|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
??? ->|------||- ARTICLE
So the $10,000 Question is: "How do I join together these Four Entities to get the desired end result I need/want??"
1.) Having SECTION_DIMENSION is good, because it defines which combinations of Sections and Dimensions can exist.
(Remember, this can't be a "free-for-all"... You couldn't have the dimension "Featured_Legal" mapping to "Finance")
2.) Having DIMENSION_SUBSECTION is good, because it defines which combinations of Dimensions and Sub-Sections can exist.
(Again, this can't be a "free-for-all"... You couldn't have the sub-section "Payroll" mapping to the dimension "Business-Structure".)
3.) An ARTICLE ultimately needs to be tied to a SECTION, a DIMENSION, and a SUBSECTION.
(Or, at the very least, some *valid* SECTION and SUBSECTION combination as defined in the junction tables: SECTION_DIMENSION and DIMENSION_SUBSECTION
4.) If I have the junction tables: SECTION_DIMENSION and DIMENSION_SUBSECTION then I need to be careful not to have similar Junction Tables which might *duplicate* these, and then be a real nightmare as far as keeping things in synch?! :eek:
5.) Part of me is tempted to just create a Monster Junction Table like this...
So this is where I am stumped... :( :( :(
I feel like everything I have described is solid, but when it comes to tying all 4 Entities together, that is where I get confused...
Please help me out, o Coding-Forums Database Gurus!!!
P.S. If it helps any, I can provide sample data to help you visualize things better...