Link to home
Start Free TrialLog in
Avatar of rmmarsh
rmmarshFlag for United States of America

asked on

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

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?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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
Avatar of rmmarsh

ASKER

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
Avatar of rmmarsh

ASKER

Let me try it tomorrow and I'll get back to you... thank you for your time...
Avatar of Norie
Norie

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.
Avatar of rmmarsh

ASKER

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
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
Kent

I know that but that's how rmmarsh has it in their table.
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).
Avatar of rmmarsh

ASKER

Badotz:  do to I still need the "foreign key" in the Orders table?  Other than that, you explanation makes perfect sense.  Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Badotz
Badotz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rmmarsh

ASKER

Thank you... works like a champ!