How do i test for a dynamic set of elements from my code without hard-coding?

Question

The question will best be understood with an example.

I have Hospital One who have 3 main pharmacies called; Central Pharmacy, Ward Pharmacy, and Private Suite.

These pharmacies can be added, deleted, and extended at will. While these go on i would love to know from my code when we are talking about a Central Pharmacy or a Ward Pharmacy without hard-coding the id.

Can someone help me on how i could go about solving this problem?

I'll present a different scenario when this is answered. Thank you everyone in advance.

Edit #1

I think there will be some kind of table design & code design pattern to enable this sort of flexibility.

Answer 1

If you're not hard-coding the id, you need to hard-code something, you just said your code wants to know it works with the central pharmacy, so something about that is hard-coded into the logic here.

Is the id so bad?

Could you instead store behavioral flags in the database, so that instead of saying if (pharmacy.Name == "Central Pharmacy") ... you would say if (pharmacy.IsCentral) ... or similar?

Answer 2

Without hard-coding id's, there are two ways to go about it:

• Add a "Pharmacy Type" to the pharmacies table. Have that indicate whether it is a Central Pharmacy, a Ward Pharmacy or a Private Suite.
• Add a different table with three rows one with the central pharmacy and its id, the second with the ward pharmacy and its id, etc.

With regard to the first option:

If you can have only one Central Pharmacy your code needs to ensure that, by making sure that the type can only be set to "Central" when there is no other pharmacy with that type.

If you need to have a Central Pharmacy at all time, then your code needs to make sure that the Central Pharmacy cannot be deleted and may just "drop" the Central Pharmacy type from the interface for adding deleting and changing other pharmacies.

Please note that when coding such "single instance" constraints, you may need to look at table wide locks or re-reading / re-checking just before and just after the update. In the end having a single non-deletable Central Pharmacy may be a lot simpler to deal with.

Answer 3

My initial table design would be

CREATE TABLE Hospitals (
  HospitalID INTEGER PRIMARY KEY
  , Name VARCHAR(16)
)

CREATE TABLE PharmacyTypes (
  PharmacyTypeID INTEGER PRIMARY KEY
  , Name VARCHAR(16)
)  

CREATE TABLE Pharmacies (
  PharmacyID INTEGER PRIMARY KEY
  , HospitalID INTEGER FOREIGN KEY REFERENCES Hospitals (HospitalID)
  , PharmacyTypeID INTEGER FOREIGN KEY REFERENCES PharmacyTypes (PharmacyTypeID)
  , Name VARCHAR(16)
)

This allows for generic pharmacies to be added as well as the types you mention.

At one point in time, one way or another you will have to hardcode either the PharmacyTypeID of Central Pharmacy (et all) or their names.