Need some design help with SQLite...

rmmarsh
rmmarsh used Ask the Experts™
on
I'm trying to create a SQLite database consisting of two tables: Customer and Order.   This is what I have so far for the Customer table:

        [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)"];

Open in new window


This what I have for my Order table:

        [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)" ];

Open in new window


My questions are:
Is the "references" statement correct?  I want to have each order reference the key in the Customer table
PCS, pattern, style_name and PRICE will occur many times for each "order".  Is this the correct way to define them in the Order table, or is there a better way?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Systems Development / Support Specialist
Commented:
1. General syntax of foreign key(reference) in SQL Lite is:

CREATE TABLE Orders (
        CUST_ID TEXT 
        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,
       [b][u] FOREIGN KEY(CUST_ID ) REFERENCES CustData(BUS_NAME )[/u][/b]
       )

Open in new window


2. instead of repeating the same data of columns
    take the columns to other table with order id, so that this table can refer to that based on                        order id

Author

Commented:
Thank you... I appreciate it!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial