Using Natural Keys

[CatB]

@ScottPletcher

You should 100% use an id on the first two tables. I've been a full-time DBA for 30+ years, including extensive actual data modeling, and you only want to use an id there, period.

Why?

I have seen a lot of very experienced DBA's argue that natural keys are, well, simply more "natural" and make sense in a lot of cases.

My premise is that these three tables are so insignificant and contrived, why not?

If I was Amazon . com, though, I certainly wouldn't be doing that!

Just trying to learn better from someone with 30+ yrs experience.

@ScottPletcher

The third table doesn't need a separate identity; this is a bad error, but one you see over and over. Instead, use the keys from the other table as the natural key:
ENTITLEMENT ( member_plan_id, feature_id )

Again, why?

The approach I have taken more in recent years is what you saw above...

Every table has a surrogate key using an sequence/autonumber/whatever to ensure uniqueness always. However, I set this with a UK constraint versus a PK.

Then, for tables where it has practical application, and won't come back to bite me, I use a natural key as a pk.

For the junction table, I have a surrgate key but this time make it the pk just so the table has that guaranteed uniqueness again.

@ScottPletcher

It's actually not a pain to join three tables, because you do not have to do it. Create a view that does the joins, and then reference the view. That's much better anyway, as it makes changes a lot easier.

Truth be told, this is for a website and I am using MySQL. (Not sure if this is a Microsoft-only website?)

Do you know if I can do Views in MySQL?

@ScottPletcher

Even if you decide to skip view, it would still be horrible to do a poor table design just to avoid a couple of simple joins.

I know lots of database gurus that wouldn't see it as a mortal sin to use natural keys as the primary keys in my parent tables and then have the natural keys be the co-foreign keys in the junction table.

Again, can you explain why you feel this is a bad idea?

Look at the context...

It's not like I am using Address or Last Name as the natural key!

Thanks.