Post reply

M-M Relationships

  • August 29, 2007 at 10:22 am

    #118329

    Hi,

     

    How m-m relationships are implemented in real world database ?

    Can anybody give me explanation with some sample query example ?

     

    Regards

    Karthik

     

    karthik

  • August 29, 2007 at 11:33 am

    #730208

    how about something as simple as a customer to product relationship?

    many customers might be related to one or more products, and the products might be related to more than one customer.

    a one to many would more likely be customer to state: one customer might be related to one state, but the state is related to many customers.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 29, 2007 at 1:27 pm

    #730243

    Many to Many  relationships are often implemented by the use of a junction table.  In Lowell's example you could create a Order table with the customerid and ProductId as the columns. 

    For example when Customer 1 orders products 6, 7 and 8 you would insert these rows:

    Insert into Order (CustId, ProductId) values(1, 6)

    Insert

    into Order (CustId, ProductId) values(1, 7)

    Insert

    into Order (CustId, ProductId) values(1, 8)

    When Customer 2 order product 1 you insert

    Insert

    into Order (CustId, ProductId) values(2, 6)

    Then you can use this table to easily find out who order what product or what products were order by who.  Other columns like Order date or quantity of product ordered could be added to make the table more useful  The Northwinds database has a good example using 2 tables Orders and Order Details to separate out the data that relates just to the product ordered (Quantity) and the entire order (Order date)  Have a good look at Northwinds for a more robust and detailed sample.

     

    Francis

  • August 30, 2007 at 4:38 am

    #730376

    Thanks Experts !

     

    karthik

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply