Avatar of rmmarsh
rmmarsh
Flag 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?
DatabasesSQL

Avatar of undefined
Last Comment
rmmarsh

8/22/2022 - Mon
Kent Olsen

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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
rmmarsh

ASKER
Let me try it tomorrow and I'll get back to you... thank you for your time...
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.
rmmarsh

ASKER
immorie:  can you give me an example of the select statement please?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kent Olsen

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
Norie

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

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Norie

Kent

I know that but that's how rmmarsh has it in their table.
Badotz

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).
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Badotz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
rmmarsh

ASKER
Thank you... works like a champ!