CODEWITHSUNDEEP
mysqlsql
Viet
Viet

Reputation: 3

SQL Multiple foreign keys pointing to same primary key

Is there going to be a problem with my following design? Basically, I want keep track of a total payment by a customer (eg. $50) that is split between two items (eg. $20 and $30). But I want to be able to look at any item - ITEM(ITEM_ID) and know how much the customer spent on their total bill - PAYMENT(PAYMENT_AMOUNT). To keep it simple I included minimal columns:

CREATE TABLE ITEM (
  ITEM_ID           NUMBER(7,0)         NOT NULL,
  ITEM_SALE_AMT     NUMBER(7,2)         NOT NULL,
  PAYMENT_ID        NUMBER(7,0)         NOT NULL,
  PRIMARY KEY (ITEM_ID),
  FOREIGN KEY (PAYMENT_ID) REFERENCES PAYMENT(PAYMENT_ID)
);

CREATE TABLE PAYMENT (
  PAYMENT_ID        NUMBER(7,0)         NOT NULL,
  PAYMENT_AMOUNT    NUMBER(7,2)         NOT NULL,
  ITEM_1            NUMBER(7,0)         ,
  ITEM_2            NUMBER(7,0)         ,
  PRIMARY KEY (PAYMENT_ID),
  FOREIGN KEY (ITEM_1) REFERENCES ITEM(ITEM_ID),
  FOREIGN KEY (ITEM_2) REFERENCES ITEM(ITEM_ID)
);

Firstly, each item is unique (so you can't have two payments for the same item). Obviously, if the customer pays for many items in one transaction, my PAYMENT table will have a lot of columns (but this has never happened in 30 years, usually 1 and occasionally 2 or 3).

Is there a major flaw I am not seeing or could this be improved?

Upvotes: 0

Views: 1067

Answers (1)

Sean Redmond
Sean Redmond

Reputation: 4114

This is a simple one-to-many relationship. You should take out the ITEM_1 and ITEM_2 columns and associated foreign keys in the PAYMENT table. The items are all linked to their payments via the foreign key in the ITEM table. That's all you need.

Upvotes: 2

Related Questions