3

With the following entity relationship structure I'm struggling to figure out if the relationship between the LOAN and ITEM entities is valid?

The weak entity of LOAN uses a partial key of 'loan_dateLeant' and the primary keys from CUSTOMER and ITEM to form LOANs primary key. However LOAN has a 'one to many' relationship with ITEM as a loan can consist of more than one item. But surely this means that if more than one item is loaned, then the loan record will have two item_id values for part of its primary key?

alt text

Community
  • 1
  • 1
user271132
  • 157
  • 1
  • 4
  • 9

2 Answers2

3

You're right, that's not valid - you can't use a to-many relationship as a defining part of a primary key. What you might consider doing instead is adding a unique loan_id that you make part of your primary key, instead of the items the loan contains; then a single loan is defined by its customer and loan ID (or customer, date, and loan ID).

If that doesn't work, make date_leant a datetime field, and increase the precision on it until you can't possibly (within the constraints of your system) have two loans occur at the same time - how likely is it that a single customer transacts two separate loans within milliseconds of each other?

Note this doesn't prevent the LOAN entity from taking part in the relationship as the "one"; it just means that you can't define the weak entity using the "many".

Tim
  • 59,527
  • 19
  • 156
  • 165
  • So, if I do the datetime technique, I basically stop using the Item entity in identifying the Loan records? Just use the Loan datetime and the customer ID? I think I'm fine with that. – user271132 Mar 17 '10 at 17:28
  • But how do we know which items are associated with a loan? – user271132 Mar 18 '10 at 10:31
  • You still have the one-to-many relationship between LOAN and ITEM, you just don't use it as part of the primary key. A single loan is identified *uniquely* only by its customer and timestamp. – Tim Mar 18 '10 at 13:29
0

Actually, you can do this.

Remember, the primary key of LOAN will only depend on the date+customer identifier. As long as there is a maximum cardinality of "1" on the "can make" relationship (i.e., a loan is related to only 1-customer), you're fine on the primary key with LOAN.

The ITEM table, will instead take the (single) identifier of the loan (customerID + date) as a foreign key within ITEM. The Primary Key of LOAN will not be affected by this modeling.

F C
  • 143
  • 1
  • 7