Need some design help with defining my primary key using two tables

rmmarsh
rmmarsh used Ask the Experts™
on
I currently have these two tables defined as:
        [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, "
         "SALES_NAME TEXT, "
         "NOTES TEXT)"];
        
        [db executeUpdate:@"CREATE TABLE IF NOT EXISTS Orders ("
         "CUST_ID TEXT PRIMARY KEY NOT NULL, "
         "ORDER_NBR TEXT, "
         "ORDER_DATE TEXT, "
         "ORDER_DEL_INST TEXT, "
         "ORDER_NOTES TEXT, "
         "ORDER_TERMS TEXT, "
         "ORDER_VIA TEXT, "
         "BOLTS TEXT TEXT, "
         "COLOR TEXT TEXT, "
         "STYLE TEXT TEXT, "
         "PRICE DECIMAL, "  //  really want decimal  ???  TODO
         "ITEM_DEL_INST TEXT, "
         "FOREIGN KEY(CUST_ID) REFERENCES CustData(BUS_NAME))"];
   

Open in new window


I need to re-define the primary key in the CustData table to include the Order_Date in the Orders table.  Duplicates are allowed.  (It's a SQLite d/b, if that makes any difference).

How do I do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Marsh,

We've got a terminology problem.  By definition, a primary key is unique so duplicates are not allowed.

Will it suffice if you just use an auto_increment (identity) column in each table?


Kent

Author

Commented:
Hi Kent... now that I think about it... what about having a SELECT statement that will give me every Customer along with every one of their Orders (Order_Date showing)?  That's all I really need...
There doesn't seem to be a link between the two tables.  But if you add CUST_ID (autoincrement or identity) to CustData and poplulate it in the Orders table when you store a row, you're all set.



Kent
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Let me try it tomorrow and I'll get back to you... thank you for your time...
NorieAnalyst Assistant

Commented:
rmmarsh

You don't need a primary key from two tables for that.

If you use the primary key of the customer table as a foreign key in the orders table then you can write a simple SQL statement to return all the orders for each customer.

Author

Commented:
immorie:  can you give me an example of the select statement please?
Hi Marsh,

immorie's suggesting design, not usage.  You may not need primary keys for the question at hand, but business rules suggest that you do need primary keys for each of these tables.  The customer number should be unique within the customers table, and the order number should probably be unique within the orders table.

The table definitions (foreign key from orders to customers) suggest that the customer number is a primary or unique key.


Kent
NorieAnalyst Assistant

Commented:
It's hard to give an example when the structure of the tables isn't right.

In Orders CUST_ID should not be used as the foreign key field, it's the primary key in that table

Add another column to the Orders table, eg BUS_NAME, and use that as the foreign key field.

Then you could use something like this.

 SELECT CustData.BUS_NAME , Orders.ORDER_NBR, Orders.ORDER_DATE
FROM CustData LEFT JOIN Orders ON CustData.BUS_NAME = Orders.BUS_NAME
No, no, no, no, no!!!

The Business Name is data.  It should NOT be used as the primary key.  While it MAY work for this particular application, using the business name as the primary key means that that business must ALWAYS have that name.  A simple name change would no longer be trivial.


Kent
NorieAnalyst Assistant

Commented:
Kent

I know that but that's how rmmarsh has it in their table.
Top Expert 2007

Commented:
A table should have a PK (primary key) with no meaning. It is used only for identification of a single row in a table. Traditionally, the name used is id.

My FK (foreign key) have the following format:

TABLE_NAME + "_" + id

Giving each table an id column makes linking trivial:

SELECT CustData.BUS_NAME , Orders.ORDER_NBR, Orders.ORDER_DATE
FROM CustData LEFT JOIN Orders ON CustData.id = Orders.CustData_id

The PK and FK are immediately recognizable.

Further, should the customer name or number change, you will not have to re-key your tables (an arduous task, and one that is fraught with peril).

Author

Commented:
Badotz:  do to I still need the "foreign key" in the Orders table?  Other than that, you explanation makes perfect sense.  Thank you.
Top Expert 2007
Commented:
Each table will need an id column.

If one customer can have one or more orders, then the orders table needs a FK column for the customer id, named CustData_id. This provides a link between the tables.

Author

Commented:
Thank you... works like a champ!

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