Home » SQL & PL/SQL » SQL & PL/SQL » Do I have to use ID as a PK? (General)
Do I have to use ID as a PK? [message #674473] Mon, 28 January 2019 21:29 Go to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
Is it a mortal sin if instead of using a derived key (e.g. AutoNumber/Sequence) you use a natural key?

I have three tables, all small, two are parent tables, and one a junction table.

For my purposes, it would be much easier if the pk's in the parent tables and the fk's in the junction table were in plain English and not cryptic ID #'s.

What do you think?
Re: Do I have to use ID as a PK? [message #674474 is a reply to message #674473] Tue, 29 January 2019 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

AskTom: Surrogate versus Natural Keys and Natural Key as Primary Key Vs Surrogate Key.

[Updated on: Tue, 29 January 2019 00:30]

Report message to a moderator

Re: Do I have to use ID as a PK? [message #674478 is a reply to message #674473] Tue, 29 January 2019 02:00 Go to previous messageGo to next message
John Watson
Messages: 8295
Registered: January 2010
Location: Global Village
Senior Member
It is up to you. I'm sure you are aware that the debate on this has been running ever since Ted Codd invented the relational database. Ralph Kimball is a surrogate key man. You will not be fired for doing as he advises.

One reason I favour surrogate keys is rules for confidentiality. Natural keys often contain sensitive data which (a) should be encrypted and (b) should not be replicated unnecessarily, which complicates using them.
Re: Do I have to use ID as a PK? [message #674496 is a reply to message #674478] Tue, 29 January 2019 14:40 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
John Watson wrote on Tue, 29 January 2019 02:00
It is up to you. I'm sure you are aware that the debate on this has been running ever since Ted Codd invented the relational database. Ralph Kimball is a surrogate key man. You will not be fired for doing as he advises.
Yes, I recall the debate.

Do you ever use natural keys?


John Watson wrote on Tue, 29 January 2019 02:00

One reason I favour surrogate keys is rules for confidentiality. Natural keys often contain sensitive data which (a) should be encrypted and (b) should not be replicated unnecessarily, which complicates using them.
That is an interesting point that I never considered before!!

In my case, I have these tables...

MEMBER_PLAN (parent)
- id (pk)
- name (uk)


SITE_FEATURE (parent)
- id (pk)
- name (uk)


ENTITLEMENT
- id (pk)
- member_plan_id (fk)(uk1)
- site_feature_id (fk)(uk1)



These are simple tables that are unlikely to change and since this is for a modest database for a website I run, I don't have the same worries that someone would have at Walmart.com

I would like to do this since it makes accessing the Plan/Feature data easier...

MEMBER_PLAN (parent)
- id (uk)
- name (pk)


SITE_FEATURE (parent)
- id (uk)
- name (pk)


ENTITLEMENT
- id (uk)
- member_plan_id (fk)(pk1)
- site_feature_id (fk)(pk1)


Any thoughts on my particular application?

[Updated on: Tue, 29 January 2019 14:40]

Report message to a moderator

Re: Do I have to use ID as a PK? [message #674497 is a reply to message #674496] Tue, 29 January 2019 14:49 Go to previous messageGo to next message
John Watson
Messages: 8295
Registered: January 2010
Location: Global Village
Senior Member
Quote:
this is for a modest database for a website I run
Come on, man. In that case, it doesn't matter an iota, does it? I thought you were asking a serious question!
Re: Do I have to use ID as a PK? [message #674498 is a reply to message #674474] Tue, 29 January 2019 14:51 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
Michel Cadot wrote on Tue, 29 January 2019 00:29

AskTom: Surrogate versus Natural Keys and Natural Key as Primary Key Vs Surrogate Key.

So which do you prefer?

Do you use both, or are you solely in one camp or the other?

Any comments on my last post with an example of why I am considering natural keys?

Thanks.
Re: Do I have to use ID as a PK? [message #674499 is a reply to message #674498] Tue, 29 January 2019 14:58 Go to previous messageGo to next message
John Watson
Messages: 8295
Registered: January 2010
Location: Global Village
Senior Member
Comment? "Time waster".
Re: Do I have to use ID as a PK? [message #674500 is a reply to message #674497] Tue, 29 January 2019 15:35 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
John Watson wrote on Tue, 29 January 2019 14:49
Quote:
this is for a modest database for a website I run
Come on, man. In that case, it doesn't matter an iota, does it? I thought you were asking a serious question!
Are you being serious or sarcastic?

Almost every question I have ever asked in my life s SERIOUS...

I was stating that I'm not working for the IRS on a database with every U.S. taxpayer.

That doesn't mean I am less inclined to learn the right (or at least "best") way to do things?!

Re: Do I have to use ID as a PK? [message #674501 is a reply to message #674500] Tue, 29 January 2019 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Which metric measures better?
Which metric measures best?
What is unit of measure for best?
As you get closer to best does the measured value get larger or smaller?

Re: Do I have to use ID as a PK? [message #674514 is a reply to message #674501] Wed, 30 January 2019 09:26 Go to previous messageGo to next message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
I have used tables with natural keys and I have tables using sequences. Personally I like numeric keys because they are easier to use. However it's your database and if you would find it easier to use natural keys then go for it. However be aware of the following

If you have a member called "John Smith" and that is your natural key. What happens when a second person is put in your database whos name is "John Smith' You just broke the database. What happens if they change their name to "Henry Smith" what happens to all the keys in the ENTITLEMENT table. They have now invalid keys and your database is broken. That is the main reason that I always use numeric keys generated using a sequence.
Re: Do I have to use ID as a PK? [message #674515 is a reply to message #674514] Wed, 30 January 2019 10:01 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
Bill B wrote on Wed, 30 January 2019 09:26
I have used tables with natural keys and I have tables using sequences. Personally I like numeric keys because they are easier to use. However it's your database and if you would find it easier to use natural keys then go for it. However be aware of the following

If you have a member called "John Smith" and that is your natural key. What happens when a second person is put in your database whos name is "John Smith' You just broke the database. What happens if they change their name to "Henry Smith" what happens to all the keys in the ENTITLEMENT table. They have now invalid keys and your database is broken. That is the main reason that I always use numeric keys generated using a sequence.
Thanks for the reply (and less attitude than some).

I see your point about two John Smith's. In this case, that won't happen since peopl aren't involved.

As far as things changing, doesn't cascading eliminate that issue? If I rename "Premium Digital" to "Platinum Digital", my database will cascade down and update the foreign keys, so it seems like "No harm, no foul".

Maybe using Views is the best approach, because then I can use AutoNumbers/Sequences and get the benefits of them, and also get the "readabilty" I would like in my junction table.

Re: Do I have to use ID as a PK? [message #674516 is a reply to message #674515] Wed, 30 January 2019 11:02 Go to previous messageGo to next message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
Oracle doesn't support "cascade update", only "cascade delete". You can do cascade update by using deferred validation and a number of triggers or in procedure code but it is not native to the concept of a foreign key
Re: Do I have to use ID as a PK? [message #674517 is a reply to message #674516] Wed, 30 January 2019 11:04 Go to previous messageGo to next message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
The view is probably a good idea
Re: Do I have to use ID as a PK? [message #674518 is a reply to message #674516] Wed, 30 January 2019 11:09 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
Bill B wrote on Wed, 30 January 2019 11:02
Oracle doesn't support "cascade update", only "cascade delete". You can do cascade update by using deferred validation and a number of triggers or in procedure code but it is not native to the concept of a foreign key
Oh, I didn't know that.

Guess that is another reason to stop dickering with Natural keys, eh? Smile
Re: Do I have to use ID as a PK? [message #674519 is a reply to message #674517] Wed, 30 January 2019 11:11 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
Bill B wrote on Wed, 30 January 2019 11:04
The view is probably a good idea
Are there any "gotchas" when using Views?

Or can I safely assume that every time I go into a View, that I am seeing accurate, real-time data like I would see if I went into the indvidual tables or I ran a query?

Also, can you make updates from a View, or a View only for "viewing"?

Thanks for the help so far, Bill!

[Updated on: Wed, 30 January 2019 11:11]

Report message to a moderator

Re: Do I have to use ID as a PK? [message #674520 is a reply to message #674519] Wed, 30 January 2019 11:33 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
A view is just a stored query, so any time you query one you see exactly the same data as you would get if you ran the view query.

Some views are automatically updatable, depending on how simple the query is. For more complicated queries you can attach an INSTEAD OF trigger to the view to handle updates.
Re: Do I have to use ID as a PK? [message #674521 is a reply to message #674520] Wed, 30 January 2019 12:09 Go to previous messageGo to next message
CatB
Messages: 8
Registered: January 2019
Junior Member
cookiemonster wrote on Wed, 30 January 2019 11:33
A view is just a stored query, so any time you query one you see exactly the same data as you would get if you ran the view query.

Some views are automatically updatable, depending on how simple the query is. For more complicated queries you can attach an INSTEAD OF trigger to the view to handle updates.
Okay.

So would you say using Views is a good compromise in keeping referential integrity in shape but making data easier to read?

Also, are there any performance benefits if you refer to a View versus running a query from scratch?

For instance, in my situation, if I keep my three tables above in a traditional surrogate setup, create a View that shows me what I need, and then when my application needs the data, access the View versus running the SELECT with INNER JOINS. Does that sound correct?

[Updated on: Wed, 30 January 2019 12:10]

Report message to a moderator

Re: Do I have to use ID as a PK? [message #674522 is a reply to message #674521] Wed, 30 January 2019 15:23 Go to previous message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
It's the same performance as running an actual query. As cookie monster said, a view is just a stored select query. For example a view to display the ENTITLEMENT table could be

create or replace view ENTITLEMENT_V AS
select a.id,b.name member_name,a.member_plan_id,c.name site_name,a.site_feature_id
from member_plan b,site_feature c, entitlement a
where a.member_plan_id = b.id
and a.site_feature_id = c.id;

and to display the data without the id number, simply type

select id,member_name,site_name
from entitlement_v;

[Updated on: Wed, 30 January 2019 15:26]

Report message to a moderator

Previous Topic: MULTISET EXCEPT - COLLECTION
Next Topic: table export to excel
Goto Forum:
  


Current Time: Thu Jul 09 14:44:39 CDT 2020