SQL Server Performance

creating tables

Discussion in 'Getting Started' started by lisa_baria03, Oct 14, 2007.

  1. lisa_baria03 New Member

    I created three different tables: order customer product

    I need 2 many-to-many table for
    order and customer
    customer and product

    Create table order
    (order_id Int Not Null,

    )

    Create table customer
    ( customer_id Int Not Null,

    )

    Create table product
    ( product_id Int Not Null,

    )

    canyou give me a hint in how to create the many to many relationship. I'mnot sure if it is the use of foreign key, constraints, check etc. hopethat you can show me one example
  2. Greg Larsen New Member

    Typically many-to-many relationships are done via helper tables. So you would create two new table:
    create table customer_order
    (customer_id int,
    order_id int)
    create table customer_product
    (customer_id int,
    product_id int )
  3. lisa_baria03 New Member

    hmm. so the above is what you called associative tables?
    for the tables would constraints or any checks be needed?
    Thanks.

  4. Greg Larsen New Member

    I should have added you should also create foriegn key references like so:
    create table customer_order
    (customer_id int not null
    references customer(customer_id),
    order_id int not null
    references [order](order_id)
    )
    create table customer_product
    (customer_id int not null
    references customer(customer_id),
    product_id int not null
    references product(product_id)
    )
  5. lisa_baria03 New Member

    thank you greg,
    what i meant was is it would it still be correct to create references in those 3 tables here:
    Create table order
    (order_id Int Not Null,
    references customer(customer_id)
    )

    Create table customer
    ( customer_id Int Not Null,
    references order(order_id)
    )

    Create table product
    ( product_id Int Not Null,

    )
  6. Greg Larsen New Member

    If you made a foreign key constaint on customer that references order as you suggested this would mean a customer could only have 1 order. It would be ok for order to have a foreign key constraint that references customer, because an order normally has only one customer.
  7. lisa_baria03 New Member

    in that case would that resolve the many-to-many relationship between the customer-order and order-product tables.
  8. Greg Larsen New Member

    Not sure exactly what you are asking. Would you please explain a little more?
  9. lisa_baria03 New Member

    would setting the references in these 3 tables be able to resolve the many to many issue? Thanks =)
    Create table order
    (order_id Int Not Null,
    references customer(customer_id)
    )

    Create table customer
    ( customer_id Int Not Null,
    references order(order_id)
    )

    Create table product
    ( product_id Int Not Null,

    )
    By the way, i also sent you an email
  10. Greg Larsen New Member

    If you have a many to many relationship be between two tables the best way to implement that is with a helper/associative table.
  11. lisa_baria03 New Member

    how would i create that?
  12. Adriaan New Member

    If you have orders where there can be more than one customer associated, then you need a OrderCustomer table.
    This table has (as a minimum) two columns: an OrderId column referring to the Order table, and a CustomerId column referring to the Customer table. You also need a unique constraint on the combination of the two.
  13. lisa_baria03 New Member

    can you show me?
  14. Adriaan New Member

    We've given you all the details. If there's anything you do not understand at this level, you need to train yourself on database fundamentals.
  15. anandchatterjee New Member

    Hi Lisa,
    There is a thumb rule to implement many 2 many relationship in RDBMS. You need to implement a third table.
    To create a many to many relation between Product and customer, I am using an intermediate table called Bill.

    Create table Bill
    (
    Bill_id Int Not Null,
    Refer customer_id,
    Refer product_id
    )
    Create table customer
    (
    customer_id Int Not Null
    )
    Create table product
    (
    product_id Int Not Null,
    )
    On the same way you can introduce another table called OrderDetails to implement many 2 many relationship between Order and Customer. I think this reply will help you. If you find any probs please give me shout.
    Arijit
  16. lisa_baria03 New Member

    hi i am also hoping to learn also how to insert constraints, set primary keys, foreign keys and references
    Would you be able to show me?
  17. anandchatterjee New Member

    These all are very much basic areas..Why dont you refer the BOL? Everything are very clearly mentioned over there. If you stuck somwhere please let me know.
  18. satya Moderator

    True, getting the updated copy of books online from Microsoft download site will get you more information.

Share This Page