Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Im So Confused about tables

    Ok I just started this project to track orders and allow them to be searched. i am using SQL and created tables for:

    Customers
    ----Name, Address, etc
    Items
    ---each record is a different product
    PO numbers
    ----Just a listing of the POs used

    there are a few more but the main idea is I have some orders where a single PO has several items in it. Do I create one table and have a record for each item in the PO with the same PO inseveral records or what?

    Any insight would be much appreciated

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You need an intermediary table (PurchaseOrderItems) with ItemID and POID to establish a many-to-many relationship between Items and Purchase Orders.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2007
    Posts
    3

    what goes inside

    I created identifiers in each table already, ie PoId. What should the new table look like? Can you please give me an example using random days such as po 123 item xyz and item ABC by customer 1, then the same customer orders 3 other items with a new po number.

    Thank you so much for the quick response!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    id expect to see soemhtign like

    table customer
    customerID
    ....cusotmer details (eg name, locatiojn, delivery address.....)

    products table
    productID
    ...product details (eg description, size, weight etc...)

    orders table
    orderid
    ordernumber 'optional
    customerid 'identifes the customer making htis order
    ...order details (eg order date, delivery date....)

    orderdetails
    orderid } PK
    lineno }
    productID 'identifies the product this customer has ordered
    ...line details (eg quantity orderd, price etc...)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2007
    Posts
    3
    Ok im still not quite getting it... My tables are currently:

    CustomerTable
    ----CustomerId (int, key identifier)
    ----Address
    ----CustomerName

    ProductTable
    -----ProductId (int, key, identifier)
    ------ProductNumber
    ------ProductDescript

    PoTable
    ----PoId (int, key, identifier)
    ----PoNumber

    So I should make a new table and have them layed out like what? My issue is when a single customer has several different items one one PO. should there be seperate records for this such as:

    po 123, customer a, product abc
    po 123, customer a, product xyz
    po 321. customer b, product abc
    po 321, customer b, product bcd

    and so on?

    I would have thought that there has to be a better way to do this, i cant think of how though.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I think you need to define the type of relationship between each entity first.

    Your choices are:
    One to One
    One to Many
    Many to Many

    Also see: http://r937.com/relational.html
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mletendre
    So I should make a new table and have them layed out like what?
    yes you should, like this --

    Customers
    ID primary key
    Address
    Name

    Orders
    ID primary key
    Number
    Customer_ID references Customers (ID)

    Products
    ID primary key
    Number
    Descript

    OrderProducts
    O_ID references Orders (ID)
    P_ID references Products (ID)
    primary key (O_ID,P_ID)
    Qty

    notice the nicer table and column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    Sweden
    Posts
    5

    Thumbs up


    you made a funny!

Posting Permissions

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