Hello Friends, I am working on oracle express edition 11g my expertise is in java se, right now I am working on a database project, I am having difficulty on integrated an item/product information to order summary table how can I create a relation between an order summary and item/product, by creating object array(I am not sure if something like that exsist in oracle/sql, may be I think in the wrong way) I mean each order may have different kinds of products, I have a table named product, how can I integrate those products with ordersummary table? Any ideas, or suggestions? Thanks in advance.
Just speaking general relational organization here. This would apply to any relational database. You would normally have an orders table that contains the order information. Separately, you have an order_items table with a reference to the individual order. This table contains an entry for each item in the order. It would contain the sale price, and information. This is important because your product price may change, so you want the price and info specific to the time of the order. The Order_items table also has a reference to the original product in the products table. This would be the proper normalized relational approach to an order / product system. You would also use foreign keys on the order_items table to the order and product table to prevent corrupt entries in the order_items table.
You can create a 'view' of that particular order. The order number will be the name of the view. And this 'view' will select the details of all the products in the order. The user will get a 'select,update,delete' role on his view. Let me know if the need some more help about view. Thanks SUU