douhong9210 2011-03-01 02:58
浏览 62
已采纳

帮助我为iou Web应用程序设计数据库模式

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: enter image description here

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!!!

  • 写回答

4条回答 默认 最新

  • duanshan1511 2011-03-01 03:00
    关注

    Definitely one table for all IOUs.

    Scenario 1: As Yko I want a list of all the persons I owe to and how much in total

    SELECT personName, sum(amount) totalOwed FROM IouTable WHERE usernameId = <Yko>
    

    Scenario 2: As Yko I want to know how much I owe Bob and why.

    SELECT amount, description, date FROM IouTable WHERE usernameId = <Yko> and personName = 'Bob'
    

    Let's look a little closer to your case.

    1. The User table looks quite good. The key candidates include username, usernameId and email. The surrogate usernameId is as good as any for the key.

    2. The IouTable lists all the IOUs. There are a couple of issues with this table.

      • Perhaphs a Yko has loaned exactly €2 from Bob twice the same day and doesn't bother to fill the description. In this case, you don't have a proper way to distinguish the two transactions. You probably should add a transactionId to uniquely identify the transactions

      • Furthermore, Yko might have two friends called Bob. How can Yko separate these two from each other. Should there be a list of friends Yko can loan from?

      • Eventually Yko should pay up. Yko can probably clear out the debt from the table, but then how can we find out later on who has loaned how much to whom? Should there be a column that tells if the dept has been paid?

      • Some friends of Yko might even consider being paid back in increments. Yko might edit the original amount owed but then again we wouldn't know much was needed in the first place. Should there be a column that tells how much of the dept has been paid so far? If Yko want's to monitor the debt more closely, a list of all repayments might be good idea. Then you could count the total amount of debt left by summing up how much has been repaid.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名