CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopererdentity-relationship
user2866313
user2866313

Reputation: 23

Entity Relation Design

I am trying to implement an entity relation for a hospital oracle database system.

I am rather confused if I should seperate the table below or merge them into 1.

 - Supply

ItemNo (PK) , Name, ItemDescription, QuantityInStock, BackOrderLevel, CostPerUnit

 - PharmaceuticalSupply

DrugNo (PK) , Dosage, MethodOfAdmin

Basically in my ERD, I pointed PharmaceuticalSupply to Supply as a subset which inherits the attribute but also have additional attributes. Am I wrong in doing that?

Upvotes: 2

Views: 111

Answers (2)

Bron
Bron

Reputation: 501

Ultimately, this is a design decision that has no right or wrong answer, but keeping them separate can be helpful. For example, there are many types of supplies that are not pharmaceutical. If you merge the tables, you make it possible to enter data that has no real meaning. For example, you can't have a dosage of bandages. The separate table makes it clear that dosage only applies to pharmaceuticals.

Note that there are a few variations on how to manage the PKs and FKs in PharmaceuticalSupply. It could have both an ItemNo and a DrugNo, where ItemNo is a foreign key. In that case, either one could be the primary key, but if DrugNo is the primary key, then ItemNo probably needs to be a unique index. However, unless DrugNo is needed due to some custom format, it might work well to simply use ItemNo as both PK and FK and completely eliminate DrugNo. This results in a "specialization" as the relational database world likes to refer to it.

Upvotes: 1

Michiel Rop
Michiel Rop

Reputation: 99

It depends on your population. It it's a subset, to reduce redundancy add a foreign key to Supply. That way you'll be able to build a join that list all data.

I would still introduce a DrugNo key for indexing. Can an item number appear more than once in the PharmaceuticalSupply table ? If your do then your definitely need the DrugNo key.

  • PharmaceuticalSupply

DrugNo (PK) , ItemNo (FK), Dosage, MethodOfAdmin

Upvotes: 0

Related Questions