If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

(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!!

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! ;D

There is no right or wrong answer to this as you are only building a small system with limited users. From a true modelling point of view then Scenario 2 is correct, but as you say that then makes your solution more complicated for a novice to use.

Scenario 1 will work and be simple to manage (apart from all the blank fields)

So it is really up to you which you would be happiest with.

Personally I would go with Scenario 2 - you will soon get the hang of how to piece it all together for your queries

> There is no right or wrong answer to this as you are only building a small
> system with limited users.
>
> From a true modelling point of view then Scenario 2 is correct, but as you say
>that then makes your solution more complicated for a novice to use.

Okay, good, so at least I was on track!

> Scenario 1 will work and be simple to manage (apart from all the blank fields)

Valid point.

> So it is really up to you which you would be happiest with.
>
> Personally I would go with Scenario 2 - you will soon get the hang of how
> to piece it all together for your queries

Again, that is good to know that I wasn't off my rocker!

So, how would you do Scenario #2?

(Confession: I have spent most of my time using MS Access for projects like this. It is a decent quick-n-dirty DB tool, but one key thing it lacks is that Access's entire paradigm is built around "bound forms" where you are typing directly into the DB! This makes for shorter dev times, but actually create a thousand times more work in the long run. Point is I have limited experience developing in REAL databases...)

Let's say I have...

tblExpense
tblFuel
tblElectric
tblNaturalGas

which all contain "Expense-level" data, but where tblExpense is my "super-type" and the others are "sub-type" tables.

Citldba was really nice to you when saying "there is no right or wrong answer" but the naked truth is that there is a right and wrong answer... and this is all wrong.

1- As stated you want to build a "generic solution" ideally PHP/MySQL
2- Your notation looks like SQL Server standard
3- You are asking in an Oracle forum
4- You are "modeling" -or did you mean "prototyping" - in MS-Access
5- Your concepts of data modeling are all over the place -not necessarily good places.

I'll point just to one random issue here...

Originally Posted by Bob Just

(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!!)

...let me break the news for you, in your scenario a "quantity" of something you are buying is a "quantity" no matter what you are buying.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.