Results 1 to 3 of 3

Thread: How do I design this SQLite d/b correctly?

  1. #1
    Join Date
    May 2012
    Posts
    2

    How do I design this SQLite d/b correctly?

    I want to create a d/b in SQLite with two tables: Customer and Orders. Each Customer can have many Orders; (I understand that's a one-to-many relationship). Each Order can have many "line items" (a line item is a specific order for an "item"), in addition to the credit card info, terms and shipping info (non-address type info). I need the ability to find all of the orders for any particular customer, but do not have to find the customer based on an order.

    This is what I have so far for the Customer table:
    Code:
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS CustData ("
     "BUS_NAME TEXT PRIMARY KEY NOT NULL, "
     "EMAIL TEXT, "
     "PHONE TEXT, "
     "SHOP_NAME TEXT, "
     "SHOP_ADDR1 TEXT, "
     "SHOP_ADDR2 TEXT, "
     "SHOP_CITY_STATE TEXT, "
     "SHOP_ZIP TEXT, "
     "SHIP_NAME TEXT, "
     "SHIP_ADDR1 TEXT, "
     "SHIP_ADDR2 TEXT, "
     "SHIP_CITY_STATE TEXT, "
     "SHIP_ZIP TEXT, "
     "NOTES TEXT)"];
    This is what I have for the Order table:

    Code:
    [db executeUpdate:@"CREATE TABLE Orders ("
     "CUST_ID TEXT REFERENCES CustData, "
     "ORDER_NBR TEXT, "
     "SALES_NAME TEXT, "
     "CREDIT_CARD TEXT, "
     "EXP_DATE TEXT, "
     "CID TEXT, "
     "NOTES TEXT, "
     "PCS INTEGER, "
     "PATTERN TEXT, "
     "STYLE_NAME TEXT, "
     "PRICE DECIMAL)" ];
    PCS, Pattern, Style_Name and Price are considered the "line items".

    My question is: is the correct way to define these tables with regard to the "line items", or is there a better way?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,931
    You should have OrderDetails table and have one to many relation from Order to OrderDetails.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Thank you... that will work for me...

Tags for this Thread

Posting Permissions

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