Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Post Unanswered: many to many relationship : help

    i have
    customer table
    CUST_ID as PKey
    C1
    C2

    PRODUCT TABLE PRODUCT_ID as pkey
    values
    P1
    P2

    NOW I EXTARCT SALES FROM MY ERP SYSTEM IN SALES TABLE :

    INV1 C1 P1 A1
    INV1 C1 P2 A2
    INV1 C1 P1 A3
    INV2 C2 P2 A1 and so on

    as you can see the relationship is 1:N in case of CUST AND SALES TABLE BUT PROD AND SALES IT IS N:N (AN INVOICE CAN HAVE MANY PRODUCTS AND A PRODUCT CAN BE IN AN INVOICE MANY TIMES) I THERE FOR CANNOT HAVE A PRIMARY KEY ON THIS TABLE.

    I NEED TO BREAK THIS INTO A THRID TABLE PRODUCT INVOICES BUT HOW ????? AND WHAT WOULD HAPPEN IF I USE THE EXISTING SCHEMA FOR DATAWAREHOUSE APPLICATIONS ?? PLEASE HELP.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Can you please attach your ER diagram?

    One way to break this by including a relationship attribute of time (date::time).
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: many to many relationship : help

    Looks like you need an INVOICES table:

    INV1 C1
    INV2 C2

    Then take the customer out of the SALES table:

    INV1 P1 A1
    INV1 P2 A2
    INV1 P1 A3
    INV2 P2 A1 and so on

    Now you can create a view that provides the denormalised structure you had before:

    create view sales_view as
    select i.invoice_id, i.cust_id, s.product_id, ...
    from invoices i, sales s
    where i.invoice_id = s.invoice_id;

    If you want existing queries to work without changes, you can rename the SALES table to SALES_TABLE and the view SALES_VIEW to SALES.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •