3

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 AppointmentID.
  • 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) E/R Model of invoice s

Which approach is the best practice for this kind of situation?

closed as off-topic by Paul White, Mark Storey-Smith, RolandoMySQLDBA, Taryn, Max Vernon Dec 6 '13 at 18:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White, Mark Storey-Smith, RolandoMySQLDBA, Taryn, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Don't forget, you're going to need REFUNDS, ADJUSTMENTS etc. Plus you've completely ignored tax - if you're US based, that's an utter nightmare. – Philᵀᴹ Dec 5 '13 at 19:47
  • ... and then once you get through the financial side of it you still have all of the HIPAA nightmare to untangle. HIS is a big business, why not see if there's something that meets your needs? – swasheck Dec 5 '13 at 19:51
  • I'm not going to lie buddy. If you are attempting to make a data system that deals with healthcare and you're asking basic design questions on the internet it's probably not a good idea to go forward with this project. – Zane Dec 5 '13 at 19:55
  • I'm not from the US, so HIPAA and what not doesn't affect me. In addition, this is not the sort of software that is going to be sold, it is being developed for a family member's practice, and they have fairly specific requirements. Thanks for your input though. – Oliver Frye Dec 6 '13 at 3:08
  • The U.S. is not the only country that has laws in place for healthcare and protection of personal data. You also have to deal with PCI compliance if you are going to have this application process or store any transaction information of a payment. PCI is not U.S. only, Visa and other credit card carries have compliance requirements in most every country. – Shawn Melton Dec 6 '13 at 3:58
1

If you want the "keep it simple" approach, you can in fact put both types of "items" in the same table, while maintaining proper integrity constraints. Generally speaking, this is what it would look like:

  • Put an ItemType column in BillingItem, which is a foreign key to some other table that will hold the two (or more) item types you're dealing with.
  • Add a nullable foreign key column AppointmentID.
  • Add constraints to the ItemType table which stipulates which columns (including the AppointmentID foreign key) must or must not be null based on the ItemType.

But I would probably prefer to do something like this (and have in the past):

  • Create one table with all the shared fields (BillingItem in this case), and a composite primary key with ID and Type.
  • Create tables for each subtype (appointments, physical goods) which have the columns specific to those goods, and have the same primary key columns as BillingItem.
  • Add a constraint to each subtype table forcing a specific value for Type ('A', 'P', etc.)
  • Create a foreign key from the subtype tables' primary keys to the BillingItem table's primary key.
  • Optional: Create a view that flattens/denormalizes the data by UNIONing multiple queries that fetch all the different subtypes and provide nulls for the columns that don't apply. This is just to simplify querying.

Simplified example:

CREATE TABLE BillingItem (
    ID int NOT NULL,
    Type char(1) NOT NULL,
    Cost money NOT NULL,
    CONSTRAINT PK_BillingItem PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Type CHECK (Type IN ('A', 'P')) --Or make Type a foreign key to a lookup table
)

CREATE TABLE BillingItem_Appointment (
    ID int NOT NULL,
    Type char(1) NOT NULL DEFAULT 'A',
    Start datetime NOT NULL,
    AppointmentID int NOT NULL,
    CONSTRAINT PK_BillingItem_Appointment PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Appointment_Type CHECK (Type = 'A'),
    CONSTRAINT FK_BillingItem_Appointment_BillingItem FOREIGN KEY (ID, Type) REFERENCES BillingItem (ID, Type),
    CONSTRAINT FK_BillingItem_Appointment_Appointment FOREIGN KEY (AppointmentID) REFERENCES Appointment (ID)
)

CREATE TABLE BillingItem_Product (
    ID int NOT NULL,
    Type char(1) NOT NULL DEFAULT 'P',
    Name varchar(50) NOT NULL,
    ManufacturerID int NOT NULL,
    CONSTRAINT PK_BillingItem_Product PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Product_Type CHECK (Type = 'P'),
    CONSTRAINT FK_BillingItem_Product_BillingItem FOREIGN KEY (ID, Type) REFERENCES BillingItem (ID, Type)
)
  • Thankyou. I think your second approach is the way to go. – Oliver Frye Dec 6 '13 at 3:18
1

You need to abstract Goods and Services, using Table Inheritance. You can use Single Table Inheritance if you prefer simplicity and performance over strictness.

--abstract
create table products (
  id int primary key
);

--a concrete product
create table goods (
  id int primary key references products(id),
  name varchar(255)
);

--a concrete product
create table services (
  id int primary key references products(id),
  name varchar(255)
);

--invoice_items points to products table
create table invoice_items (
  invoice_id int references invoices(id),  --definition elided
  sequence int,                            --the display order of the invoice item
  product_id int not null references products(id),

  primary key (invoice_id, sequence) 
);

Also, people make partial payments on invoices or pay multiple invoices at once, so payments and invoices should be many-many.

  • Would this work in SQL Server? – Max Vernon Dec 5 '13 at 18:49
  • I'm not aware of table inheritance in SQL Server. Any chance you could provide the details for the OP? – Max Vernon Dec 5 '13 at 18:56
  • 2
    Made it a little more SQL Server-like. Table Inheritance is not a feature of the database but a way of designing tables. – Neil McGuigan Dec 5 '13 at 19:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.