Split table to multiple 1:1 relationships? (1 Viewer)

[Noruen]

Hi!

I would be very grateful for your help regarding my issue:

I have Expense Tracking Database. Main Table is list of Expenses with value of transactions:

tblExpenses

ExpenseID (PK)	CategoryID (FK)	Note (text)	Value (Currency)
1	1	Bla bla bla	-256 USD

For a lot of expenses in this main table (but not all - that is important) I want to add STRICTLY ONE-TO-ONE "properties" or "attributes" like:

• It is Work expense and thus belongs to some specific Work Expense Claim
• It is Regular expense and thus belongs to some specific Regular Expense Pack
• It is Grant/Donation (like some institution provided money, but I still want to keep categorization of that expense, so create special category for this is not the solution)
• It is a Loan and thus belongs to specific Loan Label
• etc.
• Yes, it is possible and quite frequent that there may be combinations of all provided examples (like it is Work Expense AND Regular Expense AND Donation)

Solutions I have on my mind:

• The problem I have is, that every property is relatively rare in comparison to overall amount of Expenses in main table. So to put this all to one table using foreign keys will cause a lot of nulls. So this solution is not making me happy
• Possible one-to-many table (like "tblAttributes") with composite primary keys: [ExpenseID] from tblExpenses and second [PropertyID] from "tblAttributeIDs" so that only one unique type can be assigned to one ExpenseID. Also, in this case I have to put all names of Regular Payments, Expense Claims, etc. to one table and I have worries about data validity (I would restrict use for just one type)
• Create one-to-one table for each type and link them to particular Fact tables containing Attribute names (so in provided example it means extra 8 tables).
• Create one-to-one relatioship with special "attribute table" (example below) with multiple columns representing provided Attribute Types. This will reduce number of nulls, but still a lot of nulls will remain:

ExpenseID (FK)	WorkExpenseID (FK)	RegularExpenseID (FK)	GrantID (FK)	LoanID (FK
1	1	1	null	null

Thank you for any help! I'm still learning how to play with Access and I'm not a database expert...

David

 

[gemma-the-husky]

Generally speaking you should never need 1 to 1 relationships.

What it should be is
ExpenseID, ExpenseType, Value, Note

so it's a long slim table. ExpenseType is a number (normally) to distinguish between the alternative payments.
If a single expense claim includes multiple analysis records then you get a header and multiple details.

so - the header
ExpenseClaimID, Employee, Date

and then the expense analysis table also includes the ClaimID, for each sub-category - rather than the excel-table-like version you have.
ExpenseID, ExpenseClaimID (FK), ExpenseType, Value, Note

In any event, you probably need a date/reference number of some sort on the ExpenseClaim to stop the same expense being claimed more than once.

 

[MajP]

See @Pat Hartman example

Does this structure make sense? Storing IDs from various lists of items.

Here's a simplified version of part of my DB structure via record, not by fields: Equipment EQ1 EQ2 ... Facility FC1 FC2 ... Unit 0200 0201 ... Vehicle VE1 VE2 ... MaintTaskLog MTL1 MTL2 .... Each of these tables has unique ID's to identify the record as you would expect. In order to...

www.access-programmers.co.uk

 

[The_Doc_Man]

I'll add in my support for the comments by MajP and Dave (Gemma-the-Husky). It is INCREDIBLY rare to actually need a 1:1 relationship. Before I retired, I had occasion to work with a database for the U.S. Navy that had over 240 tables and there was only one situation for which we needed the 1:1 table relationship. That was for security reasons because of data having different security requirements in the table that we isolated from the main personnel table. It almost NEVER happens in a money or costs or expenses table.

The general approaches to the problem involve either the Entry-Attribute-Value method (EAV) OR you learn to live with nulls because of non-applicability situations. Dave and MajP suggested different ways of storing, so I'll simply mention that you can live with nulls if you divide up the way you process each type of expense so that the code / queries for the other types of expense don't even try to touch the nulls. That is, if you need to do Work-Related expenses, do those and have an indicator in the table that shows if something is a work-related expense. Then if you have a Loan, process those separately. Divide and conquer at the query/code level. Don't touch the non-loan records when processing loans. Then who CARES about having nulls?

By the way, having the data properly laid it is actually my preferred method (Dave's and MajP's suggestions) but I thought it would be good to point out that there DOES exist more than one way to approach this kind of problem. I'm merely suggesting that if you can't eliminate the nulls, learn to live with them.

 

[Noruen]

Thanks all of you gentlemens!

Well, I tried to put your proposal to scheme so I can imagine it little bit more, but still I don't think I get it right. What I understand from @gemma-the-husky and @MajP it is this scheme:

tblExpenses (Entity table) ExpenseID (PK) Category Note Value	tblWorkExpenses ExpenseID (FK) ClaimID (FK)	tblClaims ClaimID (PK) ClaimName ...
	tblRegularExpenses ExpenseID (FK) RegularID (FK)	tblRegularNames RegularID (PK) RegularName ...
	etc...	etc...

I'm confused with that ExpenseType @gemma-the-husky stated in his response as I fail to see there the possibility of assignment of multiple Types to one Expense (like it belongs both to Expense Claim and Regular Expense, etc).

Also thanks @The_Doc_Man for explanation about those nulls - I have read article that nulls are evil.

Thanks!

 

[Pat Hartman]

While I agree that 1-1 relationships are very rare, this is actually one but it is implemented as a 1-m. You want to maintain a single table of expenses to make reporting easier

For but those expenses can be related to numerous other entities which would preclude using RI or would force you to add multiple foreign keys to the expense table, only one of which would be filled for any given expense. The example maj referenced creates an "entity" to serve as the nexus of the relationship between expenses and the entities that could generate them.

This example is a little different than the one Maj pointed to since this one needs junction tables to support a m-m.

I have read article that nulls are evil.

Nulls are not evil. Null is the proper value for a field that is "empty" Text, Date, and Numeric data types support null if you define the field as NOT required so it is more flexible than "" which is a ZLS (Zero Length String) and as the name implies, only valid for string data types. In my apps, I NEVER allow ZLS because doing so would make "" a valid value for CustomerName whereas Null would be invalid if I defined CustomerName as required.

 

[gemma-the-husky]

@Noruen

Is this a work thing, or a personal thing? Are you trying to allocate individual cheques, say, to expense categories, or do you get a single claim for expenses covering a number of different items? That's what I was getting at. The answer would change it slightly.

However, in general for each individual item, I would have an allocation code to categorise the type of expense.
It's just if you need to split a single payment into multiple categories, that you have to think about it a bit differently.

This is all normal standard accounting processes - it's a bit different if it's for personal use, as you don't need quite the same rigour.

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

Just with regard to nulls - nulls can indicate a sort of "spreadsheet" thinking. You analyse payments in a spreadsheet with column headers, and by definition the expense goes in one column, and the other columns are blank. This sort of null is not good, and generally reflects a poor design.

So rather than having a table like this

Item No, ExpenseTotal, AnalysisType1,AnalysisType2,AnalysisType3,AnalysisType4,AnalysisType5,AnalysisType6
1,12.00,,12.00,,,,,, (analysis2)
2,14.00,,,,14.00,,,, (analysis4)
3, 20.00,,,,10.00,10.00,,, (split between analysis4 and analysis5)

you have a table like this where the split is accomplished vertically, and you get no nulls.
ItemNo,ExpenseAmount, AnalysisType
1,12.00, 2
2,14.00, 4
3,10.00, 4
3,10.00, 5

The first way, you have to redesign your database every time you want a new analysis heading.
The second way, you just allow for a new analysis type, and there's no redesign necessary.

---------
either way, I struggle to see the one-to-one records.

 

[Noruen]

@gemma-the-husky it is purely personal thing, so no rigorous accounting is needed. I'm using Access as database and then visualize data in Power BI Desktop.

@Pat Hartman yes, tblExpenses where all transactions are stored is crucial for me. In this table I categorize every transaction.

All I really need is Expenses and than those Attributes so I can monitor, for example, Work Expenses. Reason why I need this in 1-M(1) relationship is, that, say, Internet bills are covered by my Employer. Thus it is Work Expense (i put it to the claim), then it is "Donation" as somebody granted me those money for some purpose and also it is Regular Payment as I receive invoice every month. But still I wanted to have those attributes strictly one-to-one because one expense cannot be in more Claims or similarly in more Regular Payments or Donations.

So after this discussion I see the implementation this way:

tblExpenses (Entity table) ExpenseID (PK) Category Note Value	tblAttributes (Nexus/Hub table) ExpenseID (FK) AttributeTypeID (FK) (work, donation, regular, ...) AttributeNameID (FK)	tblAttributeNames AttributeNameID (PK) AttributeName
	tblAttributeTypes AttributeTypeID (PK) AttributeTypeName

Is it right?

Also, regarding tblAttributeNames, in this table I need to have all Attributes together. Do you think it is ok, from database point of view, to give even to those attributes some "index" (or even AttributeTypeID) so I can restrict their use with particular AttributeTypeID in tblAttributes --- or is it too Excel-ish?

Thank you so much guys!

 

[Noruen]

Just one comment on my one-to-one approach, also related to nulls: it was my intention to save some data.

In case I have 8000 transactions so far but only in 1% of them I use particular field it seemed to me to be a good idea to "clean" those data to "table extension" and when needed to recall them using table JOIN.

 

[Pat Hartman]

Sorry, Apparently I was sleep typing when I wrote the last post. Actually rushing to get to the bridge game on time. An ExpenseType table is required. Then a table to list all the expenses. Since the expenses can be generated from multiple sources, using a separate junction table between tblExpenses and each type makes the connection as Noruen suggested.

Since there is always more than a single expense for each entity, the relationship is not actually 1-1 although I get where Maj was coming from. It seems like there should be the ability to add a single FK to the expense table and relate it to all the potential entity types but the db engine does not allow that if you check enforce RI. The engine doesn't know that you are controlling the PKs on the right-side tables so they never overlap. Or alternatively create only a single junction table which in this scenario would stand in for the entity table but this has the same problem. You can't define a FK in one table and have one FK field which might refer to multiple tables depending on which entity the expense relates to AND enforce RI.

The example Maj referred to did not implement a 1-1. It actually implemented a 1-m because the database engine won't let you connect a FK to multiple tables and enforce RI.

You can make the "1-1" but you can't enforce RI.

@Noruen I understand your desire to "clean up" if only 1% of the rows contain a value for that item but I wouldn't do it. You might move the field to the junction table though if the field occurs on a single TransactionType and that type always comes from the same entity. However, if the field can apply to multiple transaction types OR come from multiple entities, leave it where it is.

 

[Noruen]

Sorry, Apparently I was sleep typing when I wrote the last post. Actually rushing to get to the bridge game on time. An ExpenseType table is required. Then a table to list all the expenses. Since the expenses can be generated from multiple sources, using a separate junction table between tblExpenses and each type makes the connection as Noruen suggested.

Since there is always more than a single expense for each entity, the relationship is not actually 1-1 although I get where Maj was coming from. It seems like there should be the ability to add a single FK to the expense table and relate it to all the potential entity types but the db engine does not allow that if you check enforce RI. The engine doesn't know that you are controlling the PKs on the right-side tables so they never overlap. Or alternatively create only a single junction table which in this scenario would stand in for the entity table but this has the same problem. You can't define a FK in one table and have one FK field which might refer to multiple tables depending on which entity the expense relates to AND enforce RI.

The example Maj referred to did not implement a 1-1. It actually implemented a 1-m because the database engine won't let you connect a FK to multiple tables and enforce RI.

You can make the "1-1" but you can't enforce RI.

@Noruen I understand your desire to "clean up" if only 1% of the rows contain a value for that item but I wouldn't do it. You might move the field to the junction table though if the field occurs on a single TransactionType and that type always comes from the same entity. However, if the field can apply to multiple transaction types OR come from multiple entities, leave it where it is.

Thanks a lot for this guidance. I'll try to design it like that!