Data Modeling Question

[justbob3377]

Hello, I am new here and not sure if this is the correct forum to post this.

 

This is a Data Modeling question.

 

It is for a personal finance system I've been wanting to build for the past 5 years.  My goal is to develop a platform-independent solution, although I'll likely use PHP/MySQL for implementation.

 

The gist of my system is (1) Expense has (m) ExpenseDetails, where "expenses" represent anything I spend money on, including Groceries, Clothing, Fuel, Utilities, Rent, Medical, Travel, etc.

 

Experience shows me that regardless of the "category", most receipts/expenses have these fields...

 

tblExpense (all receipts)

---------------------------

ExpenseID

ExpenseDate

MerchantName

ExpenseTotal

PaymentInfo

 

 

However, a problem arises - from a data modeling standpoint - with "Utilities", "Travel-related", "Insurance" and other types of expenses.

 

For these "categories"/"sub-categories", there are lots of other important fields that should be captured under tblExpense, for example...

 

tblExpense (fuel only)

----------------------

FuelType

TotalGallons

CostPerGallon

 

 

tblExpense (electricity only)

----------------------------

DeliveredTo

RateClass

MeterReading_Start

MeterReading_End

CostPerKWH

 

 

tblExpense (telephone only)

------------------------------

BillingStartDate

BillingEndDate

TotalLocalCharges

TotalLongDistanceCharges

 

 

tblExpense (auto ins only)

----------------------------

BillingStartDate

BillingEndDate

CoverageType

InstallmentFee

LawInforcementSurcharge

 

and the list goes on...

 

(While some people might try to cram this disparate data into generic fields, that would be the WRONG approach.  A "Quantity" of 5 Apples is NOT the same thing as buying 5.27 Gallons of Gas!!)

 

While hard to show in just text, conceptually this is what I believe is know a Super-Type/Sub-Type problem.  And unfortunately, simplier databases like MS Access - which I'm using to prototype this!! - aren't designed to easily handle this Data Construct. 

 

How to handle this predicament?!  ???

 

SCENARIO #1:  If I create on monster tblExpense and include all fields, then I solve the issue of missing any important information that needs to be captured, however, I will ultimately have a large table with lots of empty cells.

 

SCENARIO #2:  I could create a (super-type) tblExpense with a primary key "ExpenseID" and the common fields described earlier.  Then I could create (sub-type) tables, e.g. tblFuel, tblElectric, tblTelephone, and tblAutoInsurance which also have a primary key "ExpenseID" and the additional respective fields in each.  Then I suppose I could somehow manage the PK's and synch everything up?!  ???  (I believe this is one reasonable path to follow, but I would definitely need "hand-holding" to properly implement this!)

 

SCENARIO #3:  One "un-informed" (and rather pompous) MS Access know-it-all I was being lectured by said I needed to create a tblExpense, tblExpenseAttribute, and tblExpenseAttributeType which would form a M-to-M relationship.  After some thought, this might logically work, but I think it goes for "logical eloquence" OVER "implementation practicality".  (Remember, I have to build forms and queries and logic to support my back-end design.  And, to me, it would be very confusing to store things like "FuelType", "MeterReading_Start", "TotalLocalCharges", and "CoverageType" all in one table as his model would demand.

 

SCENARIO #4:  Be a wimp, and build a seperate system for each Expense-Type because what I am capturing is too disparate and therefore should have its own database/home.

 

SCENARIO #5:  Stop being so "anal-retentive" and just capture "ExpenseID", "ExpenseDate", "TotalAmount" and "Category" and be happy!

 

 

===================================================================

**NOTE:  A similar problem exists with tblExpenseDetails!!

 

The data seems to fall into Retail and Non-Retail buckets.

 

With the first, you will find classic classroom "Order Details" type fields...  "OrderItemDescirption", "UnitPrice", "Quantity", etc.

 

With the second group - which is usually a Utility - you won't find ExpenseDetails because you don't buy Gas a gallon-at-a-time, or insurance in seperate parts.  And while there might be Order SubCategories like "TotalLocalService" and "TotalLongDistance", in the end, everything relates back to tblExpense - and not tblExpenseDetail - because you are buying the product/service in totality - usually for the month - if you can follow that?!

===================================================================

 

 

In closing, what seemed like a very straight-forward system to build, is actually much more complicated when you look at the "big picture".  At the same time, this isn't rocket science, and I am CERTAIN that I can build an intelligent, detailed, robust, and scalable system that meets MY NEEDS if I can just get a little help on the Data Modeling portion! 

 

** Hell of a first post, eh?! ** 

 

Sincerely,

 

 

Just Bob