Home » RDBMS Server » Performance Tuning » 2 design alternatives, passing natural key as meaningful foreign key (9.2.0.1.0)
2 design alternatives, passing natural key as meaningful foreign key [message #519804] Wed, 17 August 2011 01:51 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I need to get an expert opinion about the following design options. I have the following table:

create table voucher_x
(
voucher_serial number(6),
voucher_date date,
voucher_source_legd number(4),
voucher_source_sublegd number(4),
voucher_destination number(4),
voucher_ammount number(6,2),
primary key (voucher_serial,voucher_date)
);

insert all
 into voucher_x values (1,to_date('01/01/2011', 'mm/dd/yyyy'),1,1,45,123.5)
 into voucher_x values (2,to_date('01/13/2011', 'mm/dd/yyyy'),1,2,56,36.5)
 into voucher_x values (3,to_date('01/23/2011', 'mm/dd/yyyy'),1,3,67,367.5)
 
 into voucher_x values (1,to_date('02/01/2011', 'mm/dd/yyyy'),1,1,56,13.5)
 into voucher_x values (2,to_date('02/13/2011', 'mm/dd/yyyy'),1,5,67,386.5)
 into voucher_x values (3,to_date('02/23/2011', 'mm/dd/yyyy'),2,4,78,67.5) 
 
  into voucher_x values (1,to_date('03/01/2011', 'mm/dd/yyyy'),2,1,23,513.5)
 into voucher_x values (2,to_date('03/13/2011', 'mm/dd/yyyy'),1,5,12,3586.5)
 into voucher_x values (3,to_date('03/23/2011', 'mm/dd/yyyy'),3,1,12,657.5) 
select * from dual; 


1- the only reason why voucher_x has a composite PK is that business needs to repeat the voucher serial for each month.
2- Also voucher_x takes both ledger and sub-ledger accounts' codes to keep all information needed about a transaction in voucher_x table without the need to visit other tables in case an account_serial that presents both the ledger and the sub-ledger codes was passed instead of the two values.

My design options are:
1- Do we keep the composite key as is or I add a serial_number as a PK and make (voucher_serial,voucher_date)
unique? In other words do we depend on the fact that currently business say it will never change and they dont believe they will need to change the coding in the future or not?
2- Do we pass both the ledger and the su-ledger accounts just to keep all information needed in most searches in voucher_x or I pass a code that presents both ledger and sub-ledger as this is the was a refrential database should work?

Many thanks for your time,
Re: 2 design alternatives, passing natural key as meaningful foreign key [message #519806 is a reply to message #519804] Wed, 17 August 2011 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why do you expect the answer should be different from your previous topic "how expensive is unique versus primary key in large table"?

Explain how AskTom thread does not answer your question.

Regards
Michel

[Updated on: Wed, 17 August 2011 02:09]

Report message to a moderator

Re: 2 design alternatives, passing natural key as meaningful foreign key [message #519809 is a reply to message #519806] Wed, 17 August 2011 02:16 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi Michel,

to me the 2 problems are not the same, in the previous topic i was talking about search speed versus oracle index mechanism. here I am talking about the recommended design options in two specific cases accepting that the recommended option might sacrifise the speed in some conditions for better design:
1- is it safe to design according to current business assumption that a PK will not change (1st case)
2- is it right to design according to technical need to keep a PK composite in order to pass it a FK and keep full information in child table and avoid the need to visit external tables for information (2nd case).

For the previous topic, I still have an un-answered question about speed by the way Smile
Thanks for you patience Michel
Re: 2 design alternatives, passing natural key as meaningful foreign key [message #519813 is a reply to message #519809] Wed, 17 August 2011 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For your previous topic, as Ross said: there is NO difference so you have the answer.
And for this one, you have the AskTom thread. You didn't answer my question: "explain why this thread does not give an answer to your current question".

Regards
Michel
Re: 2 design alternatives, passing natural key as meaningful foreign key [message #519816 is a reply to message #519813] Wed, 17 August 2011 02:54 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
For previous topic: I had another question about another speed case after Ross has replied to me.
For this topic: it seems my luck today but AskTom shows: "Sorry I have a large backlog right now, please ask a question later."
The document is great as i told you and i appreciate that you gave me the link instead of a single answer.
Re: 2 design alternatives, passing natural key as meaningful foreign key [message #519819 is a reply to message #519816] Wed, 17 August 2011 03:01 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
For this topic: it seems my luck today but AskTom shows: "Sorry I have a large backlog right now, please ask a question later."

This does not prevent you from neither reading the topic nor post a follow-up with your question at the end of the topic (if you really think the topic does not answer it, don't abuse of Tom's help). The message just tells you you cannot create a new topic/thread.

Regards
Michel
Previous Topic: Query sub-factoring issue
Next Topic: Need help with tuning
Goto Forum:
  


Current Time: Fri Nov 22 01:47:29 CST 2024