Agreed.

quote:

Originally posted by 1967Goat:
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.

The advantage to re-inventing the wheel is you end up with a wheel that works for you

I've often thought about developing our own household finances system. The database design part isn't what stops me. What stops me is I utterly hate doing UI design and coding.

So I don't

quote:

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

Correct, the only thing I had in the Transactions table was the ID and the date. There could be more, but based on how I interpreted your info, that's what I could come up with.

I made a quick attempt to format my earlier post...

Hopefully that helps

Download and install Sql Server For Developers (Free). Sql Server Free Download Install takes about 1/2 hour (accept all defaults) then download Sql Server Management Studio (Free) Mangement Studio Download About a 15 minute install.

Create a database (right click databases in management studio and select new in the dialog) then right click diagrams under the database you just created and select new. This will allow you to create tables and visually set up foreign key table references. A really quick way to create a simple and small db. It will be production ready and as long as you don't try to install it on a "production" server it will be enterprise ready.

Using the diagraming tool really helps you see the table normalization.

If you decide to go the Sql Server route here's a script to create 3 tables on any database you create. It consists of 3 tables: Payees, Transactions, and TransactionDetails.

Payees would be optional if you want to add a list of predetermined accounts you transact with. This table is simple as hell, it columns for an auto increcmented id, payee name, and address info. Payee name could be used to fill in the name in the transaction detail table.

Transactions would be the "header" table of a transaction consisting of 4 columns. An auto incremented id, a Reference (user entered like a check number), a date, and a boolean value for cleared.

TransactionDetails would be the child table of the transactions table. It would have an autoincremented id, a transactionid (references id column in transaction table), payeeid (references id from paytable and is optional), payee name (from payee table or user entered), and an amount. Amount can either be positive or negative and would negate a need for a transaction type.

Script:
CREATE TABLE [dbo].[Payees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](2) NULL,
[PostalCode] [varchar](10) NULL,
CONSTRAINT [PK_Payees] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TransactionDetails](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TranactionsId] [int] NOT NULL,
[PayeeId] [int] NOT NULL,
[PayeeName] [varchar](50) NOT NULL,
[Amount] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_TransactionDetails] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Transactions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Reference] [varchar](20) NOT NULL,
[Date] [date] NOT NULL,
[Cleared] [bit] NOT NULL,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transactions] ADD CONSTRAINT [DF_Transactions_Cleared] DEFAULT ((0)) FOR [Cleared]
GO

ALTER TABLE [dbo].[TransactionDetails] WITH CHECK ADD CONSTRAINT [FK_TransactionDetails_Payees] FOREIGN KEY([PayeeId])
REFERENCES [dbo].[Payees] ([Id])
GO

ALTER TABLE [dbo].[TransactionDetails] CHECK CONSTRAINT [FK_TransactionDetails_Payees]
GO
ALTER TABLE [dbo].[TransactionDetails] WITH CHECK ADD CONSTRAINT [FK_TransactionDetails_Transactions] FOREIGN KEY([TranactionsId])
REFERENCES [dbo].[Transactions] ([Id])
GO

ALTER TABLE [dbo].[TransactionDetails] CHECK CONSTRAINT [FK_TransactionDetails_Transactions]
GO

PS: The create tables should work on any ansi standard db but the foreign keys are a bit iffy on anything but Sql Server

quote:

Originally posted by ensigmatic:
I utterly hate doing UI design and coding.

I agree! UI work sucks and takes an incredible amount of thought and work. I find that UI work is not what can X do, but how to make X user friendly (soothing colors, less repitition, fewer mouse clicks, good default values, responsive/usable on a mobile device...).

Thankfully I don't have to do much of that, most of my work is done under the hood.

Just a quickie from diagrams.net. I'm sure I forgot somethings but that's the general idea.


Edit - I added the accounts table

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

quote:

Originally posted by 1967Goat:
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.

Thank you. Nothing makes me get something done like someone telling me I shouldn't.

Trust me, if there was something out there that does what I need, I'd be using it. I've been looking for 13 years. Let me know if you find something.

quote:

Originally posted by ensigmatic:
The advantage to re-inventing the wheel is you end up with a wheel that works for you

Yes, I’ve been forced into my position. There’s really nothing out there that does what I need. I can't imagine that a simple checkbook register will take that much time to complete. Once I've got the database laid out, the rest is just simple CRUD. Then I can add reporting APIs as needed any time after that with a few lines of code.

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

i don't see any reason why you need to have separate tables for the data you're showing.

If you have a separate Payee table that has payee exclusive information like address, account #, etc., then yes.

If you have business and personal categories, then I can see a separate table that just list category / type - business or personal. You can also add a subcategory column. The category value would have to be unique in any case; you can't have the same category name under business and personal.

As far as credit and debit columns, I will note that such columns would apply to standard accounting accounts but Quicken (if you're trying to copy it, it doesn't really do "accounts"). The categories serve as accounting buckets. If you do want to keep separate credit and debit vlues, I would suggest a third column called amount; it would take the positive value of the credit and the negative value of the debit or vice versa. This would enable giving a running balance.

As to your question about having more than one transaction in an entry, assuming you may have two separate transactions under the same category (because that's how you split transactions in Quicken), then you need to add another field called "transaction number" so the primary key for the record is Date - Transaction Type - Transaction number.

Another option is to use a system generated key and each transaction will be recorded separately automatically. If you want an intelligent numbering system, i like yyyymmddxx, unless you expect to do more than 99 transactions perday, then it would be 3 Xs instead of just 2. in either case, to split transactions for a given date-payee-category, then you do need to add another field. Otherwise, for a given Date-Payee, categories is how you split the transactions.

quote:

Originally posted by Bytes:
Download and install Sql Server For Developers (Free). Sql Server Free Download Install takes about 1/2 hour (accept all defaults) then download Sql Server Management Studio (Free) Mangement Studio Download About a 15 minute install.

I have a copy of Microsoft SQL Server 2005 if that tells you how long ago I’ve done this before. LOL

What language are you going to write the front end with? I was thinking you might be able to utilize entity framework in Visual Studio. It will build the database for you. But maybe not something you want to tackle if you haven't used it before. I haven't coded in a few years, not sure what the state EF is today.

quote:

Originally posted by xantom:
What language are you going to write the front end with? I was thinking you might be able to utilize entity framework in Visual Studio. It will build the database for you. But maybe not something you want to tackle if you haven't used it before. I haven't coded in a few years, not sure what the state EF is today.

I abandoned ASP.NET MVC back in 2010 when Entity Framework was just gaining traction. It just got too expensive trying to keep up with everything changing and requiring new versions of everything every year. C#, Linq and Visual Studio we’re all amazing but since I wasn’t working in the field anymore, I didn’t think it was practical.

I’m working with Python with FastAPI framework and a Postgres database. I’m probably just going to make it work via web browser for the UI. I may add a Kivy app for remote access.

Thank you for the offer of help in making the DB. I appreciate it.

quote:

Originally posted by mark123:
Just a quickie from diagrams.net. I'm sure I forgot somethings but that's the general idea.


Edit - I did forget something huge. There will be multiple accounts (Business checking, business savings, petty cash, etc.).

Is category a "self referencing" table? i.e. A category can have child categories that can have child categories? If so I think you'll need a ParentId in it.

Would one entry have one account or would each transaction have one account? Easy no matter which scenario is true. You just need to add an account table with a corresponding foreign key.

You probably don't need Total in the entry table. Would that just be sum(transaction..Debit_amount) - sum(transaction..Credit_amount) entry tables child records? In general calculated fields are a no-no.

Looks like you're pretty much there. No doubt a few tweaks will be needed in your development cycles.

quote:

Originally posted by Bytes:
Is category a "self referencing" table? i.e. A category can have child categories that can have child categories? If so I think you'll need a ParentId in it.

Would one entry have one account or would each transaction have one account? Easy no matter which scenario is true. You just need to add an account table with a corresponding foreign key.

You probably don't need Total in the entry table. Would that just be sum(transaction..Debit_amount) - sum(transaction..Credit_amount) entry tables child records? In general calculated fields are a no-no.

Looks like you're pretty much there. No doubt a few tweaks will be needed in your development cycles.

I changed the image above.

The child_of may be a bit unnecessary. I’m probably going to leave it out.

quote:

Originally posted by ensigmatic:
<snip>
I've often thought about developing our own household finances system. The database design part isn't what stops me. What stops me is I utterly hate doing UI design and coding.

So I don't

UI? For my special purpose database programs the “UI” is a text file form or a terminal prompt for input. And text output to terminal. Or to file(s), subsequently examined with emacs, my text editor. Not fancy, but easy to do and works good.

This message has been edited. Last edited by: Pipe Smoker,

Thirty years ago, I wrote a database for billing for a company.

Today there are just to many good programs out there to do it cheap.

If your doing it for fun, go for it.

If you are doing it for business do quickbooks or one of their competitors.

Your time is worth more than $20 a month.

Figure your time at whatever your hourly rate would be.

For a business this project looses money.

quote:

Originally posted by sig2392:
Today there are just to many good programs out there to do it cheap.

All you have to do is show me one.

quote:

Originally posted by ShouldBFishin:

quote:

Originally posted by ensigmatic:
I utterly hate doing UI design and coding.

I agree! UI work sucks and takes an incredible amount of thought and work. I find that UI work is not what can X do, but how to make X user friendly (soothing colors, less repitition, fewer mouse clicks, good default values, responsive/usable on a mobile device...).

Thankfully I don't have to do much of that, most of my work is done under the hood.

I did GUIs for several hundred different projects and find them pretty easy after all that practice plus I have several years experience programming relational databases but I'll agree with others who have said that you'd be better off buying a commercial product.

quote:

Originally posted by Flash-LB:
I did GUIs for several hundred different projects and find them pretty easy after all that practice plus I have several years experience programming relational databases but I'll agree with others who have said that you'd be better off buying a commercial product.

Still waiting for someone to show me an example.

quote:

Originally posted by Pipe Smoker:

quote:

Originally posted by ensigmatic:
<snip>
I've often thought about developing our own household finances system. The database design part isn't what stops me. What stops me is I utterly hate doing UI design and coding.

So I don't

UI? For my special purpose database programs the “UI” is a text file form or a terminal prompt for input. And text output to terminal.

Yeah... I don't think I'm going to be asking my wife to learn SQL