Can you use Unique Indexed Fields for Joins?

[DoubleDee]

I'm working on a new Data Model, and am leaning towards using "Natural Keys".

This seems to be a good move, except in one area...

I have an "Article" table, and I think it would be awkward to use Article Slug's as the primary keys.

So could I have an "id" field using Auto Increment, and use that as my Primary Key. And then add a Unique Index to my "slug" field, and use that when I want to join to the "Article" table?

For large tables, I just think its nice to have a numerical ID that you can easily refer to if needed...

Sincerely,


Debbie

[r937]

if slug is "unwieldy" then an id makes sense

joining other tables to the article table could be done on either the PK or UK

defining the FK in other tables, though, i would use id, not slug

[DoubleDee]

if slug is "unwieldy" then an id makes sense

I wouldn't say "unwieldy". (An Article Slug should never change once it is created.)

I just want an ID for easy reference.

(It is easier to look for and refer to an Article by "ID=9327" than to search through 10,000 records looking for "SLUG = "does-outsourcing-make-sense-for-small-businesses.php")

joining other tables to the article table could be done on either the PK or UK

defining the FK in other tables, though, i would use id, not slug

Okay, but the other issue I am trying to avoid is this...

If I go the "Natural Keys" way, then I will do that almost entirely for readability.

If I can take a Junction Table - and by using "Natural" Foreign Keys - and get this...

Legal		Featured Legal			Litigation		WhenASoleProprietorGetsSued.php

...then having "Natural Keys" just saved me some work.


But - as above - if I insist on wanting an "ID" using AutIncrement to easily reference each Article, but I follow your advice of using an ID for the Foreign Key, then my Junction Table would degrade to this...

Legal		Featured Legal			Litigation		9327

And then I'd have to do a join to get human-readable results, at which point I might as well scrap using "Natural Keys", and go with "Derived Keys" all the way around?! (I'm probably being extreme to make a point, but I'm sure you follow me.)

Sincerely,


Debbie

[r937]

...I'm sure you follow me.

yes, i do

okay, now consider this... you're preparing a list of articles (say, in response to a clickthrough on category or tag or whatever) and you want not only the slug but the date of last revision (or some other column from the articles table)

you'll have to do the join after all, and in that case, the id is more efficient as an index

[DoubleDee]

yes, i do

okay, now consider this... you're preparing a list of articles (say, in response to a clickthrough on category or tag or whatever) and you want not only the slug but the date of last revision (or some other column from the articles table)

you'll have to do the join after all, and in that case, the id is more efficient as an index

Okay, so I'll counter with an ERD, and you can tell me what you think...

SECTION -||----|<- SECTION_DIMENSION ->|------||- DIMENSION

DIMENSION -||-----|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION

SECTION_DIMENSION -||-------|<- ARTICLE_PLACEMENT

DIMENSION_SUBSECTION -||-------|<- ARTICLE_PLACEMENT

ARTICLE -||-------|<- ARTICLE_PLACEMENT

(BTW, I spent 2 weeks ultimately coming up with this Data Model, it meets all of my fairly complex Business Rules, and it has been tested and works as it should!!)


*IF* I chose to use "Natural Keys", it would save me joins above, and make junction tables like ARTICLE_PLACEMENT useful all by itself, because it would look like below which would be handy in building my Pretty URL...

legal		sole-proprietorship	when-a-sole-proprietor-gets-sued.php

I am currently pouring over my Data Model and debating the whole "Natural Key" versus "Derived Key" thing now.

But if I ever do use "Natural Keys", I still want an AutoIncrement ID in the field in the record, however I don't want to pollute a Junction Table with an "ID" because then it defeats a key purpose of "Natural Keys", which is to improve readability while reducing joins...

Not sure if that helps?


Debbie

[r937]

Okay, so I'll counter with an ERD, and you can tell me what you think...

very nice, i'm sure... but you lost me at dimension

anyhow, this whole surrogate-vs-natural key is really your call

you certainly understand it well enough to make your own choice

[DoubleDee]

very nice, i'm sure... but you lost me at dimension

anyhow, this whole surrogate-vs-natural key is really your call

you certainly understand it well enough to make your own choice

Okay, thanks.


Debbie