Home » SQL & PL/SQL » SQL & PL/SQL » Parent/Child Table Design Help (Oracle, 12.2.0.1.0, Linux)
Parent/Child Table Design Help [message #686755] Tue, 20 December 2022 08:51 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
I'm looking for some help with designing Parent/Child tables. I'm trying to decide if the Child table should have a foreign key or not.
Or, basically, what’s best practice. I hardly ever create any tables so this is a little bit out of my comfort zone.



create table student_order(
  id         number,
  term       number,
  order_id   number,
  amount     number,
  order_date date default sysdate);

create unique index student_order_pk on student_order
  (id, term, order_id);

alter table student_order add (
  constraint student_order_pk
  primary key
  (id, term, order_id)
  using index student_order_pk
  enable validate);



create table student_order_items
  (id            number,
   term          number,
   order_id      number,
   order_item_id number,
   title         varchar2(50 char),
   quantity      number,
   amount        number);

create unique index student_order_items_pk on student_order_items
(id, term, order_id, order_item_id);

alter table student_order_items add (
  constraint student_order_items_pk
  primary key
  (id, term, order_id, order_item_id)
  using index student_order_items_pk
  enable validate);

alter table student_order_items add (
  constraint student_order_items_fk 
  foreign key (id, term, order_id) 
  references student_order (id, term, order_id)
  enable validate);



OR



create table student_order(
  id         number,
  term       number,
  order_id   number,
  amount     number,
  order_date date default sysdate);

create unique index student_order_pk on student_order
  (id, term, order_id);

alter table student_order add (
  constraint student_order_pk
  primary key
  (id, term, order_id)
  using index student_order_pk
  enable validate);



create table student_order_items
  (order_id      number,
   order_item_id number,
   title         varchar2(50 char),
   quantity      number,
   amount        number);

create unique index student_order_items_pk on student_order_items
(order_id, order_item_id);

alter table student_order_items add (
  constraint student_order_items_pk
  primary key
  (order_id, order_item_id)
  using index student_order_items_pk
  enable validate);

Re: Parent/Child Table Design Help [message #686757 is a reply to message #686755] Wed, 21 December 2022 01:17 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think (not sure) your confusion is because you are trying to use natural keys. If you create and use surrogate keys instead, then it might become clear. If you Google around, you'll find many articles discussing the difference and why (in my opinion) surrogate keys are usually a better solution.
Previous Topic: Months Calculation and update Statement
Next Topic: xml error eurofxref-daily.xml
Goto Forum:
  


Current Time: Fri Mar 29 09:17:32 CDT 2024