I am thinking of making an iou (i owe you) web app where I can keep track of who owes me and what. I am doing this as a side project. Anyways, I have no experience with database schema design. The app design is shown in the img below:
Right now, I know that I need to have a user table with at least the following:
UserTable:
username, usernameId (pk), password, passwordHint, email
Then, I get confused on how to proceed from there. Should I make a new table for every single Person#1, Person#2, etc? Or, should I have another table IouTable with all the "transactions" and use the username as the fk?
IouTable:
usernameId, personName, amount, description, date
So, when, user Yko logs on, it will run this query:
SELECT * FROM IouTable WHERE usernameId = username
(may have gotten it wrong here)
Then, I would take the rows and separate out each person individually and have each transaction for each person on its own page.
Thanks in advance for any help/advice/suggestions!!!