Home » Developer & Programmer » Designer » Data Modeling Question (General)
| Data Modeling Question [message #332821] |
Wed, 09 July 2008 16:37  |
justbob3377
Messages: 5
Registered: July 2008
Location: Bay Area
|
Junior Member |
|
|
Hi, I am new to Oracle FAQs, and this is my first post.
(I actually do not currently use Oracle, but have attended Oracle User Groups and taken Oracle classes in school.)
My goal in joining is to be surrounded by more sophisticated database users who can help me brush up on my Data Modeling and Database Design skills.
In reality, I am trying to build an Expense database to hold all of my expenses, receipts, invoices, etc. I am using MS Access to create a prototype, and then will likely use PHP for the final version. Regardless, I hope you Oracle types don't mind answering my generic Data Modeling questions!
The main tables are tblExpenses and tblExpenseDetails, and ty problem is this...
All recipts contain these common fields...
tblExpense
------------
ID
ExpenseDate
Merchant
ExpenseTotal
However, for non-retail receipts/bills, there are extra fields that are unique to a given receipt-type...
(Fuel Receipts)
FuelType
TotalGallons
CostPerGallon
(Electric Bill)
Reading_Start
Reading_End
Total_KWH
CostPerKWH
(Telephone)
LocalServiceCost
LocalTaxes&Surcharges
LongDistanceCost
LongDistanceTaxes&Surcharges
(Airfare)
DepartureDate
ReturnDate
TravelFrom
TravelTo
and so on...
If I placed all of these fields in tblExpense it would contain lots of empty fields, and be very inefficient. However, all of these fields ARE Expense fields?!
In generic - non-Oracle - terms, what is the best way to model this?
Do I create a main superordinate tblExpense and then create subordinate tblExpense_Fuel, tblExpense_Electric, tblExpense_Telephone, tblExpense_Airfare which have the same PK but that include the extra fields?
What I do know is that I need to capture and store this disparate data in it's own place. (And I will likely need customized forms for each "Sub Expense Type".)
I do not want to try and cram disparate data (e.g. "Gallons" into "Order Quantity") into the same place.
Hope this post made sense?!
Just Bob
P.S. Attached is a spreadsheet that shows my preliminary fields for tblExpense and tblExpenseDetail.
|
|
|
|
|
|
| Re: Data Modeling Question [message #334132 is a reply to message #332845] |
Tue, 15 July 2008 08:56  |
justbob3377
Messages: 5
Registered: July 2008
Location: Bay Area
|
Junior Member |
|
|
Frank,
First off, thanks for the reply!
Next, I had a chance to step away from things for a week, and have since came up with a more "coherent" decription of my problem and possible alternatives.
I am hoping that you - and others - can jump in on this...
Sincerely,
Just Bob
|
|
|
|
 Problem Re-Stated [message #334136 is a reply to message #334132] |
Tue, 15 July 2008 09:03 |
justbob3377
Messages: 5
Registered: July 2008
Location: Bay Area
|
Junior Member |
|
|
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. (Nothing against Oracle, but I don't have the $$$!!)
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
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 14:37:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
