So I think I figured my Data Model out last night.
But before discussing it, I'm not entirely happy with my options for Primary and Foreign Keys.
Now don't hate me for asking this, but what if I wanted to do this...
I really like the idea of having a unique "id" for every record of any given table. Doing so makes it extremely easy in both conversation and queries to refer to a record. It also provides a nice "time-stamp" of sorts to show the order that records were created.
At the same time, I see the benefits of using "Natural Keys".
In the Data Model we have been discussing, I am okay with using just "Natural Keys" for my Look-up Tables, but here is where I have a problem with them...
In my "Article" table, I could easily use the "slug" field to serve as my PK, but I think it would be awkward at best, to have a table where the only way to refer to records is by referring to them by these names...
Especially since this table could become tens of thousands of records, it would be so much easier to refer to a record as "ID=8391" versus "SLUG='be-sure-your-1099-contractors-pay-their-own-payroll-taxes.php'"
So here is what I was thinking...
- Create an "id" field, make it an AutoIncrement, and a PK.
- Then add a "Unique Index" (Constraint) to the "slug" field.
- Then when I do joins, I could use the "slug" field - since it is unique - and still get the benefits of a "Natural Key" while also having my "id" field.
Is that a good or bad idea?