CODEWITHSUNDEEP
sqlite
villoro
villoro

Reputation: 1549

How to define some tables in SQLite

I am trying to make a database to manage expenses between people. I want a table where I can save how many euros has spend every person.

I have a table called people which only have two columns, _id and name.

Then I am trying to create a table with _id, total amount and save how many euros has spend everyone. I was thinking in made a column for each person with his _id and put how many euros has spend, but I don't know how to do it.

For example:

Example1

Or maybe I could use some columns to store the id and the amount, like this: (it is the same example)

example2

Thank you in advance!

Upvotes: 0

Views: 36

Answers (1)

Donal Fellows
Donal Fellows

Reputation: 137577

You propose two separate SQL antipatterns there. One is having columns that are named by another table, and the other is having a blah_1, blah_2, … series of columns. In each case they indicate that you're thinking about this wrong; a database is not a spreadsheet.

You would be better off having a table that records the unique mapping between transaction ID, person ID and how much they spent (omitting the _id for clarity):

txID | personID | spend
-----+----------+-------
 1   |  1       | 10
 2   |  1       | 5
 2   |  2       | 10
 3   |  2       | 10
 3   |  3       | 10
 4   |  1       | 4

You'll want to specify that the combination of txID and personID is unique, but not that either column is unique in itself.
Now that's not to say that you've lost the amount that anyone's spent or other basic aggregate info like that. You can then derive the total amount spent in a transaction using a query like:

SELECT SUM(spend) AS "Total amount" FROM spendTable WHERE txID = 2

However, you can also do things like finding out how much someone has spent in total:

SELECT SUM(spend) FROM spendTable WHERE personID = 1

Such a query is entirely sensible, and would be much more difficult with the second table design you proposed. (The first design would be better, except then you can't actually explicitly link with the PEOPLE table via a foreign key relation, which would make things much trickier as soon as you start doing anything more complex.)

Upvotes: 1

Related Questions