If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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!!)

So, in order to manage all of this content, my website is divided into these "Sections":

Sections:

Code:

- Finance
- Legal
- Management
- Operations
- Marketing

(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...

Entities:

Code:

- SECTION
- DIMENSION
- SUBSECTION
- ARTICLE

Business Rules:

Code:

- 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

So the $10,000 Question is: "How do I join together these Four Entities to get the desired end result I need/want??"

Observations:
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?!

5.) Part of me is tempted to just create a Monster Junction Table like this...

SECTION_DIMENSION_SUBSECTION_ARTICLE

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!!!

Sincerely,

Debbie

P.S. If it helps any, I can provide sample data to help you visualize things better...

I'm not clear that you need all the tables you are describing, but if you think you do, you probably do. So sample data would help make that clearer.

Sure, I'll post some sample data in a moment.

But first, allow me to *try* and explain my "Information-Architecture" thought-process... (Be gentle, because I spent A LOT of time on this, and feel it is right.)

About a month ago I finished QA'ing my website and was ready to "go live". (All I had left to do was drop in my Data/Content.)

But when I went to go do that, TRAGEDY struck?!

You see, over the last 18-24 months I have been hand-coding my website, my creative side has never slept. And what was supposed to be a modest website with 6 Sections/Navigation tabs had turned into one with 40-50 main topics, plus all of the related ones?!

I had no physical way to cram all of that into my website design. (Plus, I'd lose people if they had to drill down 5 levels to get to everything I came up with as far as content?!)

So I spent 2-3 weeks racking my brain, coming up with lists and lists of things, and drawing everything out on paper.

One KEY PROBLEM I faced was that there were all of these different ways to view things...

For example, is it a Car? Or a Sedan? Or gas-powered? Or American-made? Or red? Or fuel-efficient?

Basically what was supposed to be a *simple* website on Small-Business articles had exploded into something like the "NY Times"...

But eventually, some light-bulbs went off in my head, and I came up with this approach...

3.) Instead of creating a really DEEP website, group like things together into a concept called a "Dimension" and spread things out HORIZONTALLY...

4.) When a person clicks on a "Section", they are taken to a "Section Landing Page".

In the main area, is a box with select "Sub-Sections" - each containing links to related Articles. (Dimension = "Featured_<section>")

There would also be a way for the user to click on a "View all <Sub-Section> Articles", and be taken to a "Sub-Section Landing Page" which would provide a listing of Article Summaries related to the chosen "Sub-Section".

5.) In the right margin of the "Section Landing Page", display various boxes of different "Dimensions" containing the related "Sub-Sections".

If a user clicked on one of these secondary or tertiary SubSections, then they would be taken to a "Sub-Section Landing Page" which would provide a listing of Article Summaries related to the chosen "Sub-Section".

6.) This would allow people choosing a "Section" to first and foremost see what I deem to be the most important Articles related to a given Section.

However, it would also allow me to "slice and dice" a Section into many other facets that otherwise would get buried in a DEEP design.

7.) The concept of a "Dimension" exists on the back-end to keep Articles properly organized, and it also exists *subtly* on the website itself.

But as far as the URL and users are concerned, there is only the concept of...

Section > SubSection > Article

Anyways, in the next post, I will give you lots of sample data to help "visualize" things...

This all makes tons of sense. But it's still not clear to me why the DIMENSION has to be anything but a way to *find* the appropriate subsections for the given section. Meaning that it seems kind of like an auxiliary table, not necessarily part of the main structure.

SECTION_DIMENSION_SUBSECTION_ARTICLE(Below I am showing every Section-Dimension-SubSection combination, but also showing one Article that would be mapped to 3 different Section-Dimension-SubSection combinations...)

But it's still not clear to me why the DIMENSION has to be anything but a way to *find* the appropriate subsections for the given section. Meaning that it seems kind of like an auxiliary table, not necessarily part of the main structure.

But I'll await your examples.

I have thought that same thing, HOWEVER, remember these "Business Rules"... (Actually not stated as such, above, but rather as "Observations"?!)

Observations:
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".)

You missed showing the SUBSECTION_ARTICLE table. <grin/> But I think that's pretty well implied by everything 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.

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

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.

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.

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?

It sure looks to me like the answer to that is "no." In which case I think you are 100% on the right track.

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...

Code:

www.debbie.com/finance/online/be-sure-to-charge-sales-tax

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.

My point?

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.

Follow me?

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

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...

Following me?

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

Why?

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.

In the mean time, I am going to try and reproduce your example above in my actual database. (This is sort of a pain, since I already laid out more realistic tables in MySQL. Either I create simple test tables like you used, or I may be bold and see if I can recreate a similar result with my actual table structure and data?!)

You've been an enormous help so far, and I appreciate your patience with me. (It's always easier to learn when people aren't putting you down, which recently happened with someone...)

...
1.) I was somewhat unfamiliar with your SQL. I tend to be anal-retentive, and always use INNER JOINs.
...
Can you explain the difference between our SQL?

No difference. I used what I call "implicit inner joins". It's actually an older style of inner join, before the INNER JOIN and LEFT JOIN and RIGHT JOIN keywords were introduced into SQL. But it still works just fine in all databases. It can only be used with inner joins; you still have to specify LEFT JOIN and RIGHT JOIN for outer joins. Use whichever you prefer.

2.) I feel kind of dumb right now, but for some unknown reason, I thought that I had to *physically* link together SECTION_DIMENSION and DIMENSION_SUBSECTION in order for things to work?

Not sure what you mean by physically. You mean specify FOREIGN KEYs? In theory, yes. And certainly if you want MySQL to enforce the referential integrity (and/or cascade deletes or updates, as you pointed out). But there's no *requirement* that you do so. (And I'm curious: How did you know I didn't do that?)

So that is why I was freaking out, thinking that I would need to create more tables like SECTION_DIMENSION_SUBSECTION and SECTION_DIMENSION_SUBSECTION_ARTICLE.

See, now, I never picked up on your thinking you needed to do that! No, you most certainly don't need to do so, as you can see.

How do you get those nifty Text Tables? (I hope you aren't typing those out by hand?!)

I'm just using the MySQL command line client. I'm not using any database "tool". You can do it, too. Just issue the command

Code:

mysql -uUSERNAME -pPASSWORD DATABASENAME

from the windows DOS command prompt (or from a Linux prompt, if using Linux). That is, bring up a "Command Prompt" window and then type that in, using your actual USERNAME, PASSWORD, and DATABASENAME of course. It *may* not work, depending on how you installed MySQL. If not, you just have to find out where MySQL was installed, move to that directory, and then issue the command.

But of course it means that you now have zero help from any database tool. You have to type in everything yourself.

In the mean time, I am going to try and reproduce your example above in my actual database. (This is sort of a pain, since I already laid out more realistic tables in MySQL. Either I create simple test tables like you used,

Oh, don't do that.

or I may be bold and see if I can recreate a similar result with my actual table structure and data?!

You should be able to. It's just the JOINs that are the important part. Good luck.

You've been an enormous help so far, and I appreciate your patience with me. (It's always easier to learn when people aren't putting you down, which recently happened with someone...)

Well, you clearly had a good idea and ran with it. Even if you missed some points along the way, you pushed your own envelope. As I said in a prior post, I think you really did come up with at least one of the best ways to represent your organization of the data. I only get tired of people who want an instant answer without doing any thinking on their own. You're the opposite of that case. You want to understand. A breath of fresh air.

No difference. I used what I call "implicit inner joins". It's actually an older style of inner join, before the INNER JOIN and LEFT JOIN and RIGHT JOIN keywords were introduced into SQL. But it still works just fine in all databases. It can only be used with inner joins; you still have to specify LEFT JOIN and RIGHT JOIN for outer joins. Use whichever you prefer.

Cool.

Not sure what you mean by physically. You mean specify FOREIGN KEYs? In theory, yes. And certainly if you want MySQL to enforce the referential integrity (and/or cascade deletes or updates, as you pointed out). But there's no *requirement* that you do so. (And I'm curious: How did you know I didn't do that?)

Okay, this is the hurdle I still need to get over.

In phpMyAdmin, for the SECTION_DIMENSION table, I have one FK (section_id) pointing to (Table/Field) section.id, and then another FK (dimension_id) pointing to dimension.id.

Then for the DIMENSION_SUBSECTION table, I have a FK (dimension_id) pointing to dimension.id, and then another FK (subsection_id) pointing to subsection.id.

See, now, I never picked up on your thinking you needed to do that! No, you most certainly don't need to do so, as you can see.

I think you are stating what I just tried to explain above, right?

Originally Posted by doubledee

You've been an enormous help so far, and I appreciate your patience with me. (It's always easier to learn when people aren't putting you down, which recently happened with someone...)

Well, you clearly had a good idea and ran with it. Even if you missed some points along the way, you pushed your own envelope. As I said in a prior post, I think you really did come up with at least one of the best ways to represent your organization of the data.

THANKS!!! That really makes my day!!

Counter to what this other person said - which was basically "Your schema is good for a classroom project, but will never scale in the real world" - I think my design is very scalable.

If tomorrow, I want to add an "IT" section, then my model handles it. Or if I decide to start writing Articles by different sectors, then all I have to do is drop in an "Industry" dimension, and, of course, I can then add individual industries - in this case "Sub-Sections" - until the end of time!

And, considering that I haven't written a single Article yet, this entire task of coming up with a realistic and flexible Data Model was hell!

However, I think I have a fairly simple Data Model that will last me a long, long time. (And by the time I outgrow, I'll maybe have enough $$$ to pay a pro to do this for me?!)

I only get tired of people who want an instant answer without doing any thinking on their own. You're the opposite of that case. You want to understand. A breath of fresh air.

When I can find patient mentors, I am a very eager student!!

I love discussing "theory" while solving problems, and trying to find the best way to do things, while also having alternatives in the background.

This is especially so when it comes to Data Modeling, for which I have always had a special place in my heart! Because, it is the "data" that makes your system and business go round!!

When I create INNER JOINS in my queries, I am used to starting with a Parent Table (e.g. SECTION) and then joining the Child, Junction Table (e.g. SECTION_DIMENSION) and then doing another INNER JOIN back to the other Parent (e.g. DIMENSION).

The point being that the "Junction Table" is what is causing the link between the Parent Tables - thus the name.

However, with your query, you were going from Child, Junction Table (i.e. SECTION_DIMENSION) up to a Parent Table (i.e. DIMENSION) and then back down to another Child, Junction Table (i.e. DIMENSION_SUBSECTION).

Because I am used to INNER JOINS consisting of joining Parent ---> Child <--- Parent, it threw me off?!

So, to be clear, could you please answer this earlier question....

But I believe this is how things actually work...

Because I already have all of the Tables "joined" together like this...