I am doing some preliminary database modeling for a Line-Of-Business application that I am creating. The application will be a Practice Management System for a medical practice/clinic. I am looking for advice on the best practice this particular situation to ease maintenance and data access in the future.
My situation is as follows:
- This practice offers both products (vitamins, supplements, etc) and services (consultations with practitioners).
- Billing Item is the name given to the items (lines) on a particular invoice for a customer.
- Product billing items have attributes such as name, cost, description, etc.
- Service billing items are related to a particular appointment (and only one), represented by the foreign key
- Each appointment is also related to an AppointmentType table entry (many appointments to one appointment type) which describes the cost, name & description of the appointment (e.g. Its attributes).
In modeling the BillingItem table, in order for it to hold both types of data, it would need to contain redundant columns to hold both types of data (either an
AppointmentID or product details). The downside to this being the loss of the ability to constrain data since I must now allow nulls on all columns.
The alternative is to have two tables, ProductBillingItem & ServiceBillingItem. Each of the tables contains the relevant data (either an
AppointmentID or product details). The downside to this being that for the retrieval of every single invoice, both tables must be queried and then added together in my application.
I have attached a screenshot of my invoice E/R model so far for the sake of reference. (The billing item table obviously has not been modeled to either of the approaches yet)
Which approach is the best practice for this kind of situation?