Designing Surrogate vs Natural Keys with Oracle SQL Developer

I’m a fan of both.

When describing an Entity, I like to keep it ‘real.’ My beer or brewery doesn’t have an ‘ID.’

But when I go to manage this data in the real world, I don’t want 3 or 4 columns used to uniquely identify a record in my table. Or tuple if you want to be all fancy.

So can I have it both ways?

Yes.

In your logical design, define your entities as usual. Make sure you define uniques for everything. Define your relationships.

And then Engineer to Relational.

Now, right before you do that, decide which way you want to go.

Let’s go with the natural keys first.

We’re going to have a primary key with 4 columns: Name, City, State, and Country. You could have 2 breweries of the same or similar name in the same city…but I want to keep it simple’ish.

My beer table gets a foreign key, which is those same 4 columns which tie back to the brewery table.

My queries aren’t going to be very fun.

And I could run into trouble. Because things change, especially in the ‘real world.’

Rambling Ric puts it very well actually, The primary key should have nothing to do with reality.

With surrogate keys, you’ll generate what you’re probably more used to seeing in relational tables: an ID column in my BEER table that points back to an ID column in my BREWERY table. I still have my natural keys defined as UNIQUEs…which will probably keep my data safe(r).

You can have more than one relational design.

So if you want, you can share with your developers their options. Just set the property before you engineer it to the relational design.

If you have many relations, you can use the Search & Replace feature to do it all at once instead of one-by-slow.

Now Show Me the FKs in My Logical

This was the original question as it came to me – how can I see my foreign key ‘columns’ in my logical model?

Three things:

1. Use a design notation that supports this
2. Make sure you are showing ‘labels’
3. Make sure you are using natural keys

The default Barker notation style doesn’t show these no matter what you tell the Modeler to to show or display. Thankfully SQLDev lets you pick one or more notation styles with the Displays feature.