×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

sql code 基本是erWin生成的, 设计是根据我的实际项目. 如果有online store的问题, feel free to ask. 我是做online store and CRM 系统的.

本文发表在 rolia.net 枫下论坛CREATE TABLE T_Order (
Order_ID NUMBER NOT NULL,
Order_Status VARCHAR2(5) NULL
);


ALTER TABLE T_Order
ADD ( PRIMARY KEY (Order_ID) ) ;


CREATE TABLE T_Book (
Book_ID NUMBER NOT NULL,
Book_Name VARCHAR2(200) NULL,
Book_Desc VARCHAR2(500) NULL,
Book_Price NUMBER(7,2) NULL
);


ALTER TABLE T_Book
ADD ( PRIMARY KEY (Book_ID) ) ;


CREATE TABLE T_OrderItem (
OrderItem_ID NUMBER NOT NULL,
Order_ID NUMBER NULL,
Book_ID NUMBER NULL,
Item_Quantity NUMBER NULL,
Item_Status VARCHAR2(5) NULL
);


ALTER TABLE T_OrderItem
ADD ( PRIMARY KEY (OrderItem_ID) ) ;


ALTER TABLE T_OrderItem
ADD ( FOREIGN KEY (Order_ID)
REFERENCES T_Order
ON DELETE CASCADE ) ;


ALTER TABLE T_OrderItem
ADD ( FOREIGN KEY (Book_ID)
REFERENCES T_Book
ON DELETE CASCADE ) ;




create trigger tD_T_Order after DELETE on T_Order for each row
-- DELETE trigger on T_Order
declare numrows INTEGER;
begin
/* T_Order R/2 T_OrderItem ON PARENT DELETE CASCADE */
delete from T_OrderItem
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Order_ID = :old.Order_ID;
end;
/

create trigger tU_T_Order after UPDATE on T_Order for each row
-- UPDATE trigger on T_Order
declare numrows INTEGER;
begin
/* T_Order R/2 T_OrderItem ON PARENT UPDATE CASCADE */
if
/* %JoinPKPK(:%Old,:%New," <> "," or ") */
:old.Order_ID <> :new.Order_ID
then
update T_OrderItem
set
/* %JoinFKPK(T_OrderItem,:%New," = ",",") */
T_OrderItem.Order_ID = :new.Order_ID
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Order_ID = :old.Order_ID;
end if;

end;
/

create trigger tD_T_Book after DELETE on T_Book for each row
-- DELETE trigger on T_Book
declare numrows INTEGER;
begin
/* T_Book R/1 T_OrderItem ON PARENT DELETE CASCADE */
delete from T_OrderItem
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Book_ID = :old.Book_ID;
end;
/

create trigger tU_T_Book after UPDATE on T_Book for each row
-- UPDATE trigger on T_Book
declare numrows INTEGER;
begin
/* T_Book R/1 T_OrderItem ON PARENT UPDATE CASCADE */
if
/* %JoinPKPK(:%Old,:%New," <> "," or ") */
:old.Book_ID <> :new.Book_ID
then
update T_OrderItem
set
/* %JoinFKPK(T_OrderItem,:%New," = ",",") */
T_OrderItem.Book_ID = :new.Book_ID
where
/* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
T_OrderItem.Book_ID = :old.Book_ID;
end if;end;
/


to get all order item detail:
select * from t_order o, t_orderitem oi
where o.order_ID = oi.order_ID

to get one order’s full price:
select sum(oi.item_quantity*b.book_price) from t_order o, t_orderImte oi, t_book b
where o.order_ID = oi.order_ID
and oi.book_ID = b.book_ID更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / Small company, one poor junior programmer has to do the DB design work. need help
    Requirement:

    One online book store :

    One order can hold multile books:

    Order table:
    id Name quantity

    Book table
    id name price

    How to create the relations between this two table, it seem we need to create another third table? How design

    Thanks for your kind help.
    • 跟你们头儿说一下,我给你们作VOLUNTEER,行不?
      book_table
      book_id, --primary key
      book_name,
      unit_price,
      author,
      isbn,
      publisher_id,
      ....

      order_table
      order_id,
      order_date,
      customer_id,
      ...

      order_item_table
      order_id, ---\ primary key
      line_no, ---/
      book_id, --foreign key
      unit_price,
      quantity,
      sub_total,
      ...
      • why the PK in order_item_table is not(order_id,book_id)? and why no PK in order_table?
      • Are u DBA or Programmer?
        Thanks!

        order_item_table
        order_id, ---\ primary key
        line_no, ---/
        book_id, --foreign key
        unit_price,
        quantity,
        sub_total,

        this table seems should like this:
        order_item_table
        item_id---/primary key
        order_id, ---\ foreign key
        line_no, ---/
        book_id, --foreign key
        unit_price,
        quantity,
        sub_total,
        • item_id实际没必要出现
          • why? You mean order_id also can be the promary key for this table, how about one order different type of books
            • 当然是用lineno区分了,直观的将定单分成几行,每行成为1条记录
      • 这么可怜啊! 回锅过好日子吧!
        • I donnt think so, we could try to find any possible opportunities.
          • Using Erwin or powerdesign will help you a lots.
            搞不清楚你们是干什么的, er图工具都不用就想开发, 我看你们加个DBA吧.
            顺便我找工作也多一个机会
            • Can you souple people small company to spend money to buy some ERWIN tools, no money
              • Hi, man! I'm a MCDBA and SCJP, may I have a chance to be a volunteer of your company? I do need CND exp.
              • Do it like in china, download it. I have one
      • 估计人家连电话区号都不愿提供的。
        • 没见人家只是junior吗?
    • Look here:
      Order:
      Order_ID | Date | Customer | ......

      Books:
      Book_ID | Author | Price .......

      BookOrder: (Association table)
      Order_ID | Book_ID | Quantity
      • Does the bookorder needs a primarykey such as bookorder_id.
        • 我给你发了EMAIL,请查收邮件!
    • sql code 基本是erWin生成的, 设计是根据我的实际项目. 如果有online store的问题, feel free to ask. 我是做online store and CRM 系统的.
      本文发表在 rolia.net 枫下论坛CREATE TABLE T_Order (
      Order_ID NUMBER NOT NULL,
      Order_Status VARCHAR2(5) NULL
      );


      ALTER TABLE T_Order
      ADD ( PRIMARY KEY (Order_ID) ) ;


      CREATE TABLE T_Book (
      Book_ID NUMBER NOT NULL,
      Book_Name VARCHAR2(200) NULL,
      Book_Desc VARCHAR2(500) NULL,
      Book_Price NUMBER(7,2) NULL
      );


      ALTER TABLE T_Book
      ADD ( PRIMARY KEY (Book_ID) ) ;


      CREATE TABLE T_OrderItem (
      OrderItem_ID NUMBER NOT NULL,
      Order_ID NUMBER NULL,
      Book_ID NUMBER NULL,
      Item_Quantity NUMBER NULL,
      Item_Status VARCHAR2(5) NULL
      );


      ALTER TABLE T_OrderItem
      ADD ( PRIMARY KEY (OrderItem_ID) ) ;


      ALTER TABLE T_OrderItem
      ADD ( FOREIGN KEY (Order_ID)
      REFERENCES T_Order
      ON DELETE CASCADE ) ;


      ALTER TABLE T_OrderItem
      ADD ( FOREIGN KEY (Book_ID)
      REFERENCES T_Book
      ON DELETE CASCADE ) ;




      create trigger tD_T_Order after DELETE on T_Order for each row
      -- DELETE trigger on T_Order
      declare numrows INTEGER;
      begin
      /* T_Order R/2 T_OrderItem ON PARENT DELETE CASCADE */
      delete from T_OrderItem
      where
      /* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
      T_OrderItem.Order_ID = :old.Order_ID;
      end;
      /

      create trigger tU_T_Order after UPDATE on T_Order for each row
      -- UPDATE trigger on T_Order
      declare numrows INTEGER;
      begin
      /* T_Order R/2 T_OrderItem ON PARENT UPDATE CASCADE */
      if
      /* %JoinPKPK(:%Old,:%New," <> "," or ") */
      :old.Order_ID <> :new.Order_ID
      then
      update T_OrderItem
      set
      /* %JoinFKPK(T_OrderItem,:%New," = ",",") */
      T_OrderItem.Order_ID = :new.Order_ID
      where
      /* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
      T_OrderItem.Order_ID = :old.Order_ID;
      end if;

      end;
      /

      create trigger tD_T_Book after DELETE on T_Book for each row
      -- DELETE trigger on T_Book
      declare numrows INTEGER;
      begin
      /* T_Book R/1 T_OrderItem ON PARENT DELETE CASCADE */
      delete from T_OrderItem
      where
      /* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
      T_OrderItem.Book_ID = :old.Book_ID;
      end;
      /

      create trigger tU_T_Book after UPDATE on T_Book for each row
      -- UPDATE trigger on T_Book
      declare numrows INTEGER;
      begin
      /* T_Book R/1 T_OrderItem ON PARENT UPDATE CASCADE */
      if
      /* %JoinPKPK(:%Old,:%New," <> "," or ") */
      :old.Book_ID <> :new.Book_ID
      then
      update T_OrderItem
      set
      /* %JoinFKPK(T_OrderItem,:%New," = ",",") */
      T_OrderItem.Book_ID = :new.Book_ID
      where
      /* %JoinFKPK(T_OrderItem,:%Old," = "," and") */
      T_OrderItem.Book_ID = :old.Book_ID;
      end if;end;
      /


      to get all order item detail:
      select * from t_order o, t_orderitem oi
      where o.order_ID = oi.order_ID

      to get one order’s full price:
      select sum(oi.item_quantity*b.book_price) from t_order o, t_orderImte oi, t_book b
      where o.order_ID = oi.order_ID
      and oi.book_ID = b.book_ID更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • 这才是我说的软件开发,不懂得快学。。。
      • 很佩服,您真的是online store的设计者吗?
        但是有几个地方看不太明白
        第一就是为什么在T_OrderItem 的Book_id的foriegn Key上有On DELETE CASCADE,因为我们以前的做法都是当有顾客已经下了定单定了某些书以后,这些书的记录就不能再被删除了。
        再有就是当book却货或者已经不卖了的时候,是把它们的记录直接删除吗,为什么没有book的status呢
        还有后面的几个trigger,看得出您这里的Book_id和Order_id都是可以改的啊。但是那两个after delete的有什么用呢,您都在那儿写了on delete cascade了
        我是个经验比较短的developer而已,向您请教了
        • 这还差不多。
          • 查不多是什么?答案是什么?
            • 我觉得没什么正确答案,但这样的做法似乎更实际一些。应该加一些状态到是真的,如果数据记录出了错,可以让DBA去改,使用trigger似乎太冒进了些
        • 数据库是主要保证数据有效存储和读取的,完成的主要是数据层,你的问题主要是业务层的,主要应该有程序代码实现,当然数据库的约束和trigger要配合啦!数据-业务-表现,这样才是好的系统设计
          • 还是有不明白的地方
            大家都是干程序员的,所以技术上的事情可以放开了说,您不必担心我不懂
            我的第一、二个问题就是关于完整性的,你今天加了本书到网站上,顾客订了,你还可以把这本书的记录删掉,连带顾客的定单上的一条删掉吗?我不敢。
            最后的问题不是问trigger是干什么用的,我还看得懂一点点。我只是想问,那两个trigger明明没有用,为什么还要加上去。
            整个的3个问题里没有一个涉及业务层的啊,我只是想学学前辈们的更先进的做法。
            • 我说了, SQL 基本是erWin生成的, 实际上, 我的系统里极少使用trigger, 只有在前台无法控制或前后台都有数据操作时才考虑.
              Trigger 是十分麻烦的东西, 它回导致后台编程, 从而导致前台无法控制的数据操作. 在performance 上损失也比较大, 我的感觉是一种得不偿失的解决方案. 只有不得已时才采用.
              你说的业务层我不太明白, 如果是指Use case部分, 我想没必要在这里解释, 我只是手痒回答个数据设计的问题. 我说了, 我的强项是data modeling, 如何设计一个online store 只是一个简单的重复知识应用, 基本构架无非是catalog, shopping cart, check out. 三部曲, 每一部分可以加无限多的选件.
              如catalog 的cross saling, shopping cart 的auto taxing, checkout 的security. 都是具体的细节, 每个项目都不同, 没有实际例子, 空讨论只是浪费时间.
              • Trigger is a trouble for a online system under tons of transaction.
      • 天哪, 和天书一样啊, :)