0

Hello i'm a student doing a course in database where oracle is the RDBMS being used. I have a problem figuring out how to implement a section of my EERD that i'm hoping to get some insight in. I have attached the diagram below.

The case is for a blood bank. There are 2 suppliers to the blood bank:

  1. Donors who give blood(identified by id)
  2. Other blood banks in the area (identified by id)

I want to be able to track who supplied a particular parcel of blood in the inventory. As i try to assess it its leading to a field in the inventory table referencing 2 fields in 2 different tables which i don't think is possible unless its an advance concept that i haven't learned.

Assumptions I used for creating the EERD

  1. A donor my donate multiple parcels of blood
  2. Blood of varying types may be supplied by external blood banks in case of shortages
  3. Customers (Health Centers) request blood which is taken from inventory and recorded as a transaction
  4. Transactions can only reflect one customer
  5. A customer may perform multiple transactions
  6. A parcel of blood can only be reflected on one transaction
  7. A transaction will only reflect one parcel of blood
  8. In order to distinguish between occasional and regular donors a history of donations must be maintained to determine the frequency of donations

View EERD here

Bluecopper
  • 1
  • 1
  • Either you need to treat other bloodbanks as a donor allowing you to share the same field or you need two fields: donorId and bloodBankId. – Mike Parkhill Apr 05 '14 at 18:23
  • I have been thinking the same. The only issue with the first is that donors have first and last names while blood bank only has a name. I was thinking i might be able to have 2 tables being child of that table with id, firstname, lastname and another id, name. When thinking about queries i'd need to put a field that would need to be checked to determine with child table to check for names. – Bluecopper Apr 06 '14 at 05:14
  • And just incase no one ever tried... i create the table making 2 FK on the same field and though it created successfully data would not add because one of the 2 FK constraints would fail – Bluecopper Apr 06 '14 at 06:05
  • After investing some brain power this morning into rethinking the database model i came up with a redesigned model that has avoided the issue i was having and should be a decent design for testing the migration that i need to do – Bluecopper Apr 07 '14 at 02:45

1 Answers1

0

field in the inventory table referencing 2 fields in 2 different tables

While you could create two FKs on top of the same field, this is probably not what you want - such FK would require both parent rows to be present, and you probably need exactly one to be present (and the other absent).

While you could manually (in application code) implement a "FK" that can "choose" its target (aka "generic" or "polymorphic" FK), this is probably not a good idea, for reasons described here.

Instead, just create two separate NULL-able fields and two separate FKs on top of them. Then make sure exactly one of these fields is NULL at any given time (through a CHECK constraint), which will make only one of these FKs "active". More info here.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167