Originally Posted by Old Pedant
Yes, on all counts.
So, it looks like I am "on track", right?
And it looks like if I follow through on your advice from this morning, then I am not only in good shape as far as my Data Model, but also how I will take that and use it with PHP and my mod_rewrites, right?
Although you would normally JOIN back to the SECTIONS and DIMENSIONS tables if you were using IDs as your primary and foreign keys, one of the HUGE advantages of using NATURAL KEYS (remember, that's where we started? <grin/>) is that you don't *HAVE* to do that.
So extend the logic: When joining to section_dimension from other tables, there is *STILL* no reason to join to sections if all you need is the section name.
Once again, this is all a nice fall-out from choosing to use natural keys.
Play with it some more if you aren't convinced.
Actually, I already saw that, and when I was originally creating my monster SECTION_DIMENSIONS_SUBSECTION_ARTICLES table, I was attracted to that!!
So here is a "true confession"...
One big reason I started asking about "Natural Keys" and was leaning towards creating a SECTION_DIMENSION_SUBSECTION_ARTICLE table, was because I don't have a convenient way to input data into my Junction Tables?!
You see, in the past I just used phpMyAdmin. But if I have 4 tables, and my Junction Tables just consist of Integer values, then it would be a royal pain to add Articles to my database.
And being the web-programming weenie that I am, I was fearful that creating a web-form to create Junction Tables would be too complex. (Since we started talking, I got some help on here, and now think building a form won't be quite as bad as I originally thought.)
So THAT was the main motivation for dipping my foot in the "Natural Keys Pool"...
Ironically, since we started talking, I can also see lots of benefits to sticking with "Derived Keys". For example, my Section, Dimension, and SubSection names could be more fluid than hoped. So the stability of an AutoIncrement Integer has value.
I'll have to sleep on it...
But in my mind, the more *important* things is knowing how to do things multiple ways...
That is what will make me stronger and better than my competition!!
And on that note...
Can you please tell me the Pros & Cons of just making my SECTION, DIMENSION, SUBSECTION, and ARTICLE "pseudo" Look-up Tables, and having one monster SECTION_DIMENSION_SUBSECTION_ARTICLE Junction table??
Especially if I used "Natural Keys", everything would be in one table for the reading?!
I guess one downside would be space.
And a larger issue would be that I would lose the ability to enforce Business Rules of which SubSections belong to which Dimensions, and can map to which Sections...
Anyways, hopefully I am almost "over the hurdle" on this one entire thread/topic...
Thanks for all of your help so far!!!