11 Replies Latest reply on Jul 27, 2014 8:40 PM by Etbin

    help in analysis

    Eslam_Elbyaly

      hi, i have a problem in my erd ,

      i have tables

      orders (order_id , curstomer_id , date)

      order detail ( which has the details of the order ordered (order_id fk , product_id , quantity , price )

      stock ( it has the products already in my stock )

       

      may be when i sell or purchase some products , a customer may pay only some of the money not all the money , so there is (paid_amount and change) ,

       

      where should i put them ? in which table ? i think i should put them in "Orders" table ? right?

       

      and there is also "payment_methods" like (cach or cheque) , i think i should also put the foreign key for it in the "Orders" table because

      of course there is a table for "Payment_methods" ?

       

       

      thank you

        • 1. Re: help in analysis
          rp0428
          where should i put them ? in which table ? i think i should put them in "Orders" table ? right?

          You should put them in a PAYMENTS table.

           

          That table should have a foreign key to the ORDERS table and should contain appropriate columns for the payments being made:

          1. payment date

          2. payment amount

          3. who paid?

          4. how did they pay? (e.g. payment method)

           

          There may be other info depending on the payment method (e.g. check number, credit card number, etc).

           

          The ORDERS table is for orders, not for payments.

          1 person found this helpful
          • 2. Re: help in analysis
            Frank Kulash

            Hi,

             

            You should put something in the ORDERS table if there is one-to-one relationship between it and an order.

            Is there a one-to-one relationship in this case?  That is, will every order have (at most) 1 payment, and will every payment be applied to (at most) 1 order?  No; the whole reason why you're asking this is that there may be several payments all applied to the same order, and it's unclear if a single payment can be applied to 2 or more orders.

            Creat a new table, as suggested earlier.

            • 3. Re: Re: help in analysis
              Eslam_Elbyaly

              and the "change" of course will be put in the same table (payments) ? right ?

              • 4. Re: Re: help in analysis
                Eslam_Elbyaly

                You should put something in the ORDERS table if there is one-to-one relationship between it and an order.


                i think you missed the table names ?


                Is there a one-to-one relationship in this case?  That is, will every order have (at most) 1 payment, and will every payment be applied to (at most) 1 order?  No; the whole reason why you're asking this is that there may be several payments all applied to the same order,


                how did you say "no" , then said "you're asking this is that there may be several payments all applied to the same order, " ,

                the two statements are not compatible .

                - i believe that the order can cost 1000 $ and the customer can pay only 500$ for this specific order , then after two days for example he will pay 300$ or the whole 500$ ,

                so , i think each order can have more than 1 payment , and each payment can have more than 1 order , because a customer can pay 500$ of the money

                he should pay , and another customer can pay 500$ as well ?


                and it's unclear if a single payment can be applied to 2 or more orders.

                no , it can not be .

                • 5. Re: help in analysis
                  rp0428

                  Ora-01400 wrote:

                   

                  and the "change" of course will be put in the same table (payments) ? right ?

                  What 'change' are you talking about?

                  • 6. Re: help in analysis
                    Frank Kulash

                    Hi,

                    Ora-01400 wrote:

                     

                    You should put something in the ORDERS table if there is one-to-one relationship between it and an order.


                    i think you missed the table names ?

                    ORDERS was the name you used in your first message.  If the table name is something else, then substitute the correct table name where I used ORDERS.

                    Is there a one-to-one relationship in this case?  That is, will every order have (at most) 1 payment, and will every payment be applied to (at most) 1 order?  No; the whole reason why you're asking this is that there may be several payments all applied to the same order,


                    how did you say "no" , then said "you're asking this is that there may be several payments all applied to the same order, " ,

                    the two statements are not compatible .

                    Sorry, I don't follow what you're saying.  The two statements

                    1. "No, every order will not have (at most) 1 payment",  and
                    2. "There may be several payments all applied to the same order"

                    are compatible.  Statement 2 is a specific example of statement 1.


                    • 7. Re: Re: Re: help in analysis
                      Etbin

                      You might use some method to obtain a correspondence between orders and payments as specifying keys to determine which order corresponds to which payment or vice versa usually creates a mess.

                      I think it's called the ballance method:

                      • Orders sorted by payment_due_date (and maybe some priority within the same date - for example: taxes usualy must be payed first)
                      • Payments sorted by payment_date


                      ORDERS: 111111122223333333333333333333333444455555555566677778888888888889999

                      PYMNTS: 11111111122222233333334444444455555555556666667777777888899999

                       

                      the number of digits representing the order/payment is proportional to the respective amount

                       

                      Regards

                       

                      Etbin

                      • 8. Re: Re: help in analysis
                        Eslam_Elbyaly

                        is there a dedicated forum to ask analysis questions rather this forum ? or this is the dedicated one ?

                        • 9. Re: help in analysis
                          rp0428

                          Huh?

                          • 10. Re: help in analysis
                            rp0428
                            is there a dedicated forum to ask analysis questions rather this forum ?

                            There is no such Oracle forum since these forums are for Oracle related questions.

                             

                            There are PLENTY of resources on the internet on data modeling and architecture and some of them are authored by Oracle.

                             

                            Oracle Data Modeling and Relational Database Design

                            Oracle Data Modeling and Relational Database Design NEW | SQL and PL/SQL | Database Application Development | Database |…

                             

                            Tutorial: Data MOdeling for a Small Database

                            Tutorial: Data Modeling for a Small Database

                             

                            There are also many books/articles on 'normalization'. See the wiki for details of the 'five normal forms'

                            Database normalization - Wikipedia, the free encyclopedia

                             

                            Most of it is 'common sense' and the first paragraph of the wiki sums up the goal of 'normalization pretty well:

                            Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

                            So originally you ask this:

                            may be when i sell or purchase some products , a customer may pay only some of the money not all the money , so there is (paid_amount and change) ,

                             

                            where should i put them ? in which table ? i think i should put them in "Orders" table ? right?

                            I replied and told you that a new table was needed. And Frank replied and showed you why your plan to put payments in the ORDERS table was wrong:

                            You should put something in the ORDERS table if there is one-to-one relationship between it and an order.

                            Is there a one-to-one relationship in this case?  That is, will every order have (at most) 1 payment, and will every payment be applied to (at most) 1 order?  No;

                            If you have ONE order but MULTIPLE payments you can't put payment info in the orders table. Common sense.

                             

                            Keep things simple. If you have some new data and aren't sure whether it goes into an existing table or a new one just review what Frank ask you about the one-to-one.

                             

                            Create a new table for the data and link that new table to one, or more, existing tables. You can link payments to orders using ORDER_ID from the ORDERS table. That means your payments table needs to have ORDER_ID as a column. When you add a new payment you need to lookup the ORDER_ID in the ORDERS table and use that value to populate the ORDER_ID column of the payment table.

                             

                            It is a LOT easier to combine tables later if that is appropriate than it is to break them apart. Even if PAYMENTS actually belonged in the ORDERS table you can just create a simple view that combines the data from ORDERS with PAYMENTS and 'pretend' that you have one table.

                             

                            See how that works.

                            1 person found this helpful
                            • 11. Re: Re: help in analysis
                              Etbin

                              Huh?


                              Sorry, my bad. The "graphic" was meant to illustrate something like

                               

                              with

                              orders as

                              (select 1 ord_id,100 amount,date '2014-02-10' payment_due from dual union all

                              select 2,200,date '2014-03-20' from dual union all

                              select 3,3000,date '2014-04-10' from dual union all

                              select 4,150,date '2014-05-20' from dual union all

                              select 5,350,date '2014-05-30' from dual union all

                              select 6,500,date '2014-06-10' from dual union all

                              select 7,200,date '2014-07-10' from dual union all

                              select 8,300,date '2014-07-20' from dual union all

                              select 9,1000,date '2014-07-30' from dual

                              ),

                              payments as

                              (select 1 pay_id,100 amount,date '2014-02-09' payment_date from dual union all

                              select 2,500,date '2014-03-25' from dual union all

                              select 3,1000,date '2014-04-10' from dual union all

                              select 4,500,date '2014-04-20' from dual union all

                              select 5,1000,date '2014-04-30' from dual union all

                              select 6,500,date '2014-05-10' from dual union all

                              select 7,700,date '2014-06-20' from dual

                              )

                              select kind,doc_id,amount,the_date,

                                     case when the_date <= trunc(sysdate)

                                          then sum(case kind when 'P'

                                                             then amount

                                                             else -amount

                                                   end

                                                  ) over (order by the_date,kind desc)

                                     end balance

                                from (select 'O' kind,ord_id doc_id,amount,payment_due the_date

                                        from orders

                                      union all

                                      select 'P',pay_id,amount,payment_date

                                        from payments

                                     )

                              order by the_date

                               

                              KINDDOC_IDAMOUNTTHE_DATEBALANCE
                              P110002/09/2014100
                              O110002/10/20140
                              O220003/20/2014-200
                              P250003/25/2014300
                              P3100004/10/20141300
                              O3300004/10/2014-1700
                              P450004/20/2014-1200
                              P5100004/30/2014-200
                              P650005/10/2014300
                              O415005/20/2014150
                              O535005/30/2014-200
                              O650006/10/2014-700
                              P770006/20/20140
                              O720007/10/2014-200
                              O830007/20/2014-500
                              O9100007/30/2014-

                               

                              Regards

                               

                              Etbin