Hello,
I have to develop a very very simple accountability application, just to save what comes in and goes out, classified by categories, clients and expenditure items.
First time I develop an application with financial purposes and it's for a personal use to have a book with all movements. I could use an existing application (what I already do for a part of my activity), but it's always interesting to try to develop such an application.
How is the best database structure to save incomes and expenditures ?
I wonder if this structure is relevant or not.
activities : id, name
roles : id, name (creditors, debitors)
structures : id, name, role_id (or enum with creditor and debitor)
missions : id, name
movements : id, amount, invoice_date, payment_date, activity_id, structure_id, mission_id
But => the invoice_date, the activity_id and the mission_id can be null.
So I had the idea to separate the movement into two different tables.
incomes : id, amount, invoice_date, payment_date, activity_id, structure_id, mission_id (all fields are not null)
expenditures : id, amount, invoice_date, payment_date, activity_id, structure_id, mission_id (some fields can be null)
So I had another idea to structure the database : put the activity_id, structure_id, mission_id fields in another table named caracteristics.
movements : id, amount, invoice_date, payment_date, structure_id
caracteristics : id, activity_id, mission_id, movement_id
Like this when a movement isn't related to a specific activity and/or mission, I don't need to fill it.
What do you think about all this ?
Thanks a lot for your suggestions.
Vincent
I think this is a very good and common approach I would use, one thing you may want to consider is using foreign keys to link the tables together. For example, you could use the structure_id as a foreign key to link the movements to the structures table. This can help ensure data consistency and integrity.
@azimidev Hmmm ... in the structure I have proposed, I have already a structure_id in the movements table. What do you mean when you suggest me this ?
@vincent15000 I usually just do standard bookkeeping, see: https://laracasts.com/discuss/channels/general-discussion/rental-management-db-schema
@vincent15000 Yeah i can see the structure_id but where did you create foreign key?
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
@azimidev Ok sorry I didn't understand. Sure these are foreign keys, as you can see it's just the table structure without specifying any indexes, foreign keys, ... ;).
@jlrdw Ok thank you, that gives me some other ideas ;).
@vincent15000 Happy you’re starting this!
@webrobert Have you started something like this ? You wanted to ...
@vincent15000 I haven’t started. But I think it will follow the standard accounting ledger, accounts, and then additional tables for the budgets and goals maybe they’re the same. Models, as in model to achieve. Do you have plaid in France? Here it’s super popular. I’m going to use their api to connect everything up.
@webrobert I don't know ... I just had a very quick tour on the website, but I don't really understand what I can do with. What do you want to connect with it ?
In the US anytime you want to link an account to another account they use plaid as a third-party to connect them. They also track the balances. So they api provides a great way to see the current activity and balances across all accounts
@webrobert Oh you mean bank accounts.
I will read some documentation to understand what the purpose is.
I don't really understand what you mean when you say that it allows to connect accounts together.
@vincent15000 here when you login in to your credit card account you connect it to your bank so your can pay it. Or another bank so you can transfer. Plaid is just a connector. It just happens to have access to all the data for all types of accounts.
That's very interesting. I have read on the Plaid documentation that Plaid is present in France since 2019.
Have you looked at Akaunting ?
Its a full accounts system built on Laravel 9 and open source
or you can deploy with their support and hosting https://akaunting.com/
@Snapey Thank you, I will try it. My purpose is also to build a very simple application. But it's perhaps also interesting to use Akaunting.
Please or to participate in this conversation.