My last programming job was in 2008 and while I did keep up with it for a few years, I'm a bit rusty on the planning stage.

For context, I am working on a checkbook register app for me and my wife to use. Currently, we are using Quicken Home & Business. The reason that I want to write my own is because my wife loathes Quicken (because it takes so long to load the ads and since it's last years version, it keeps prompting her to upgrade and the ads take up a significant portion of the screen) and I have to do double entries, once in my scheduling/invoicing app and once in Quicken. I'm planning to rewrite the scheduling/invoincing app as well and it will work on the same database as the checkbook register app so that I only have to enter transactions once.

OK, here is the issue that I'm going to need to solve before continuing. The entries in the database contain the following fields:

• date
• transaction type (Deposit, Withdrawl, Transfer
• cleared (for reconciling accounts against monthly bank statements)
• payee
• memo
• category
• debit
• credit

The issue I'm considering is that sometimes I need more than one transaction in an entry (such as the times I deposit multiple checks in one transaction or, when I take a credit payment, I need the paid amount and the credit processing fee in one transaction or when I buy personal and business items on one receipt) so that during reconciliation I don't have to sit there and figure which customer checks go with which deposit.

Do I separate out the memo, category, debit/credit columns out into another table? Any better ideas on how to make a split transaction? Sorry, it's been so long since I've done this and I'm at a point now that if I don't get this part figured out then I'll have to go back and scrap a lot of work and do it over.

Entry table
id | account_id | date | transaction_type | payee | cleared |

Transaction table
id | entry_id | memo | category | debit | credit

I've got some fake data hardcoded in there now to work on the backend that looks like this:

        self.entries: List = [
            {
                'date': '5/13/2021',
                'transaction_type': 'DEPOSIT',
                'cleared': False,
                'payee': 'John Q. Customer',
                'memo': '#9393',
                'category': 'Sales (Business): Lawn Care',
                'payment': None,
                'deposit': '249.42',
            },
            {
                'date': '8/23/2021',
                'transaction_type': 'DEPOSIT',
                'cleared': False,
                'payee': 'Larry Q. Customer',
                'memo': '#9393',
                'category': 'Sales (Business): Lawn Care',
                'payment': None,
                'deposit': '419.02',

            },
            {
                'date': '8/30/2021',
                'transaction_type': 'DEPOSIT',
                'cleared': False,
                'payee': 'Barbara Customer',
                'memo': '#11',
                'category': 'Sales (Business): Lawn Care',
                'payment': None,
                'deposit': '61.06',

            },
            {
                'date': '9/22/2021',
                'transaction_type': '1598',
                'cleared': False,
                'payee': 'Etides',
                'memo': '',
                'category': 'Sales Tax',
                'payment': '519.02',
                'deposit': None,

            },
        ]

but as you can see, right now I've only got one transaction per entry.

A good old ERD (Entity Relationship Diagram) on a piece of scratch paper or whiteboard will help, mapping out your logical relationships, you know...?

Boxes and Crowsfeet/arrows between them...? [Accounts] to [Transactions] to [Banks] and so on.

Which are one to one, or one to many, or many to many...?

Data Modeling 101, so to speak.

quote:

Originally posted by 46and2:
A good old ERD (Entity Relationship Diagram) on a piece of scratch paper or whiteboard will help, mapping out your logical relationships, you know...?

Boxes and Crowsfeet/arrows between them...?

Which are one to one, or one to many, or many to many...?

Data Modeling 101, so to speak.

Ugh, it's so hard to remember from 12 years go. I'll work something up.

It's been a few years since I've done any database design, and I'm disinclined to do it now , but I can give you some pointers/hints.

First: You need to learn about Database Normalization, using Normal Forms in DBMS.

Using NF's reduces data duplication and, with the correct column restraints, prevents inaccurate data entry.

Just a beginning, from your sample data:

You would have a table, let us call it "transaction_type", which would have a layout something like:

table transaction_type (
trans_type_code int automatic primary key,
trans_name string
);

The actual data might look like:

1, "Deposit"
2, "Withdrawl"
3, "Transfer"

etc.

Then, in the tables which have transaction type entries you'd use foreign keys from the table above.

Same for payee's, payor's, etc.: A separate table, with each entry having a unique key that's referenced by other tables.

Same for "category," etc. Essentially, repeated data is never repeated.

Note that one can carry normalization too far. Few DB designs ever go past 3rd or 4th NF, because going beyond those starts to create more complexity than they solve. For example: True, you might have multiple addresses in the same zipcode, but a separate table of zipcodes might be going a bit too far unless you had lots of entries. (Normalization reduces database size because keys are generally smaller than the data they represent.)

Another aspect of normalization is you never, ever have a situation such as one column or the other is used. E.g.: I see you have both "payment" and "deposit" columns in your examples. That is a database design faux pas of the 1st order. Never do this. See my example, above.

quote:

Originally posted by mark123:
... but as you can see, right now I've only got one transaction per entry.

I would hope so. Another aspect of data normalization is you never, ever have duplicate columns in a table.

I think you would be well-served by picking up a copy of The Practical SQL Handbook, reading it, doing the exercises, and then designing your DB.

Spend some time (half day, whatever) refreshing on Class Diagrams and ERDs (and Use Cases), then *do* them, and the rest will flow more easily.

Class Diagrams and ERDs are what ought to be leading and certainly preceeding the design of the actual Database. Think about your needs, and write/diagram them out.

Logically, things like a table for [Transactions] seem obvious, but upon review of the documents you make it will help make things clearer by noticing that you have something like four different Banks involved, and that typing those out each time you do a Transaction sucks so you'll definitely want a lookup table with Bank details rather than stuffing all that into the Transaction table as extra columns, and on and on.

There is a point of diminishing returns with all of it, and compromises get made, especially on smaller projects, the answer is always somewhere between one giant monster Table and one with so many Tables it's ridiculous.

Further, what ought to precede your ERDs and Class Diagrams is really a bunch of Use Cases... Roles and Goals, Who wants to do What? Scenarios...

Many/most software projects fail because of shitty or skipped Requirements. Think and document and discuss and review before coding, always and forever.

Use Cases/Scenarios (Who wants to do What) leads to Class Diagrams (the Whats, in more detail) which lead to ERDs (the relationships between the Whats) which leads to the actual Database design, all informed by budget, time, and other constraints.

In a nutshell.

My database reference/design/tutorial library:

Seems like this is for a business?

Consider spending $15 or $25 / month on Patriot Software.

quote:

Originally posted by ensigmatic:

quote:

Originally posted by mark123:
... but as you can see, right now I've only got one transaction per entry.

I would hope so. ...

But this is what I am hoping to solve. I need multiple transactions per entry about 60% of the time. I rarely have one customer payment per deposit and credit transactions have a payment AND a processing fee that must be tracked together but retain their different categories.

Transaction_type can also be Check #{xxxx} so I'm not positive about normalizing that into another table. Otherwise, the Payee and category columns will be foreign keys once I stop using hardcoded fake data.

quote:

Another aspect of normalization is you never, ever have a situation such as one column or the other is used. E.g.: I see you have both "payment" and "deposit" columns in your examples. That is a database design faux pas of the 1st order. Never do this.

I always thought credits and debits are purposely separate in accounting software but I suppose I can separate it using the transaction type to put it in the correct display/report columns.

This message has been edited. Last edited by: mark123,

quote:

Originally posted by Weaseldriver:
Seems like this is for a business?

Consider spending $15 or $25 / month on Patriot Software.

I already have Quicken, I'm trying to avoid entering data in multiple applications.

If I understand what you're asking for, you'll want to break some of that out into separate tables, such as a Transaction table and a Transaction Details table.

This isn't formated very well, but I'd look at something like this:

Transaction Table
------------------------------------
|TransactionID | Int, primary key |
------------------------------------
| Date | |
------------------------------------

Transaction Details Table
----------------------------------------------------------------------------
| TransactionID | Int, foreign key into Transaction table (See above) |
----------------------------------------------------------------------------
| TransactionTypeID | Int, foreign key into TransactionType table |
----------------------------------------------------------------------------
| Cleared | Boolean |
----------------------------------------------------------------------------
| PayeeID | Int, foreign key into Payee table |
----------------------------------------------------------------------------
| CategoryID | Int, foreign key into Category table |
----------------------------------------------------------------------------
| Payment | |
----------------------------------------------------------------------------
| Deposit | |
----------------------------------------------------------------------------




Payee Table
----------------------------------------------------------------------------
| PayeeID | Int, Primary key
----------------------------------------------------------------------------
| Payee | String
----------------------------------------------------------------------------



Transaction Type Table
----------------------------------------------------------------------------
| TransactionTypeID | Int, Primary key |
----------------------------------------------------------------------------
| TransactionType | String | Deposit, Withdrawal, Transfer, …
----------------------------------------------------------------------------

...
<edited to provide rough formatting... >

This message has been edited. Last edited by: ShouldBFishin,

quote:

Originally posted by ShouldBFishin:
If I understand what you're asking for, you'll want to break some of that out into separate tables, such as a Transaction table and a Transaction Details table.

This isn't formated very well, but I'd look at something like this:

Transaction Table
TransactionID Int, primary key
Date




Transaction Details Table
TransactionID Int, foreign key into Transaction table
TransactionTypeID Int, foreign key into TransactionType table
Cleared Boolean
PayeeID Int, foreign key into Payee table
CategoryID Int, foreign key into Category table
Payment
Deposit




Payee Table
PayeeID Int, Primary key
Payee String




Transaction Type Table
TransactionTypeID Int, Primary key
TransactionType String Deposit, Withdrawal, Transfer, …


...

Thanks, I've got that mostly figured. I'm just wondering about multiple transactions per entry. I'll work on a diagram tomorrow. Probably a one to many table for transactions with a foreign key to the entries table.

quote:

Originally posted by mark123:
But this is what I am hoping to solve. I need multiple transactions per entry about 60% of the time. I rarely have one customer payment per deposit and credit transactions have a payment AND a processing fee that must be tracked together but retain their different categories.

You're thinking about it like a human organizes their thoughts, not in terms of how relational database management systems (RDBMS) data is best organized. Database design is often almost the opposite of the way humans organize data in their heads, I guess one could say.

The problem with multiple duplicate columns/per entry is this: How many? Three? Five? Ten? Twenty? What if you allocate three and end up with a record that requires more than three? And what do you suppose happens with the XX% of transactions that require less than what you pre-allocate? (Wasted database and screen space is what happens in the latter.)

Plus: Multiple duplicate columns/record makes being able to slice and dice data for reports, etc., difficult-to-impossible.

This is why I referred you to that book. It's hard to see and understand unless you understand how RDBMS' best deal with data.

See ShouldBFishin's post. That's an insight as to how it should be done.

quote:

Originally posted by ensigmatic:
The problem with multiple duplicate columns/per entry is this: How many? Three? Five? Ten? Twenty? What if you allocate three and end up with a record that requires more than three? And what do you suppose happens with the XX% of transactions that require less than what you pre-allocate? (Wasted database and screen space is what happens in the latter.)

I'm talking multiple rows, not columns. I'll draw it up and then you can critique it.

I am not a programmer,can not help writing a database.

But instead of reinvesting a wheel, I have been working with Gnucash and just moved over to Kmymoney.

These are free programs. Linux base but they have window and apple versions.

I am not an accountant but been keeping track of my bank account.

Good luck
Bill

I doubt that I could help you. I create my own ad hoc database programs for particular purposes. My methods don’t relate well to standard database methods.

quote:

Originally posted by mark123:
Probably a one to many table for transactions with a foreign key to the entries table.

That's what I was thinking (your "entries" table == my "TransactionDetails" table).

quote:

Originally posted by still_bill:
I am not a programmer,can not help writing a database.

But instead of reinvesting a wheel, I have been working with Gnucash and just moved over to Kmymoney.

These are free programs. Linux base but they have window and apple versions.

I am not an accountant but been keeping track of my bank account.

Good luck
Bill

Yeah, I've already got Quicken. It works but it gets tiresome putting every single transaction into multiple applications. I'm attempting to centralize everything.

quote:

Originally posted by ShouldBFishin:

quote:

Originally posted by mark123:
Probably a one to many table for transactions with a foreign key to the entries table.

That's what I was thinking (your "entries" table == my "TransactionDetails" table).

i see where you're coming from. My Entries table is your Transaction table not the TransactionDetails table. Right?

quote:

Originally posted by Pipe Smoker:
I doubt that I could help you. I create my own ad hoc database programs for particular purposes. My methods don’t relate well to standard database methods.

I do that for mail merge and crap like that. Feel free to add your thoughts.

This exercise is going to be a lot like miniature golf. It seems like a good idea when you start, but after a while you're going to ask why on earth you are doing this to yourself. I've done this thing many, many times myself.

I know you said you have QB and don't like it. Instead, spend the time researching programs that will do what you want. Maybe QB isn't the best application for your lawn service. I bet there's a better one out there, you just need to find it.

Writing your own code for this project is going to turn into a massive undertaking. Don't try to re-invent the wheel. My .02.