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 |
|
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 #519819 is a reply to message #519816] |
Wed, 17 August 2011 03:01 |
|
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
|
|
|
Goto Forum:
Current Time: Fri Nov 22 01:47:29 CST 2024
|