Do I have to use ID as a PK? [message #674473] |
Mon, 28 January 2019 21:29 |
|
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 #674478 is a reply to message #674473] |
Tue, 29 January 2019 02:00 |
John Watson
Messages: 8944 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 |
|
CatB
Messages: 8 Registered: January 2019
|
Junior Member |
|
|
John Watson wrote on Tue, 29 January 2019 02:00It 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 #674514 is a reply to message #674501] |
Wed, 30 January 2019 09:26 |
Bill B
Messages: 1971 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 #674516 is a reply to message #674515] |
Wed, 30 January 2019 11:02 |
Bill B
Messages: 1971 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 #674519 is a reply to message #674517] |
Wed, 30 January 2019 11:11 |
|
CatB
Messages: 8 Registered: January 2019
|
Junior Member |
|
|
Bill B wrote on Wed, 30 January 2019 11:04The 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 |
cookiemonster
Messages: 13937 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 |
|
CatB
Messages: 8 Registered: January 2019
|
Junior Member |
|
|
cookiemonster wrote on Wed, 30 January 2019 11:33A 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 |
Bill B
Messages: 1971 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
|
|
|