What is the best way to model an invoicing system with different forms of invoice items? 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 &amp; 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 &amp; 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?
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)
)