Soon after top tips in regards to this database design situation.
And So I have two tables within the database.
Table 1: Patients Table 2: Litigants
Patients holds characteristics data on the private patient, so particulars pertaining to that person, his/her birthday, names, health conditions, and so forth.. Litigants may be the entity who pays with respect to the individual, so Claimant could be a patient themself, someone else, a company (who will pay for work injuries), private health care provider, government physiques and so forth..
Patients and litigants have IDs who're foreign secrets in other tables for example bills, receipts etc...
One Patient might have multiple Litigants (Several entity will pay on his account), each Claimant might have multiple patients.
On further analysis, I have recognized that most of the characteristics of Patients and Litigants overlap like a Patient have enough money themself thus is really a private Claimant.
My thinking would be to merge the 2 tables into one and just refer to it as accounts and also have a claimantType area to recognize the kind of the account, whether it is private, health care, business or government.
What potential practical disadvantages should i bear in mind with this particular change? Aside from the altering another linked tables in database?
EDIT: Just to really make it obvious, There's already a junctional table PatientClaimants which essentially just map the patients towards the litigants. Thanks!
Merging both of these tables In my opinion is wrong.
Someone is definitely an individual. Therefore it can't be a company or perhaps an organisation.
In my opinion here you've:
Address ======= ...... Person ======= AddressId (FK) BusinessEntity ============== AddressId (FK) Patient ======= PersonId (FK) Claimant ======== PersonId (FK) BusinessEntityId (FK)
Here PersonId or BusinessId one of these could be null.
You can either (a) place in an intersection table that relates patient IDs to claimant IDs, or (b) while you talked about, merge them together - but when you have data available that may be problematic.
You might setup a census table that shows common data between patients and litigants and references an abbreviated patient/claimant table - by doing this you don't break your overall structure.