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?
