我该如何改进这个数据库模型？
我正在尝试对 DVD 销售和 DVD 租赁与客户之间的关系进行建模。虽然我不认为我的交易表是正确的。如果有人能让我知道我的设计方向是否正确，那就太好了。
I'm trying to model the relationship of dvd sales and dvd rentals to a customer. Although I dont think my transction table is correct. If anyone can let me know if I'm goin in the right direction with the design that would be great.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是一种设计方法。
我不是数据库专家。我为带有很少表的小型应用程序设计了数据库。我尝试（强调尝试，有时我失败）尽可能更通用，这样当范围发生变化时我就不会被限制在角落里。
在你的图表中，我注意到一些事情，比如你假设只销售 DVD。如果店主开始销售蓝光光盘或便携式游戏机，会发生什么？我的建议是保持设计的通用性。所以我将 DVD 桌子改写为 Item，以便桌子可以容纳任何东西。
我还添加了项目类型表，以便您可以定义它是什么类型的项目。
您的交易应被视为单个订单。客户可以下一个订单，其中可以包含他们想要购买的一组商品以及他们想要租用的少数商品。你不会要求他们刷两次卡吧？相反，请在订单页眉中为客户创建订单，并将其行项目放入订单详细信息表中。订单详细信息表还将指定数量，例如客户想要购买/租赁的物品数量。当他们租赁时，归还日期字段一开始将为空。当他们退回商品时，您可以返回并通过搜索商品 ID 和客户 ID 组合来填写日期。
您可以通过
n多种方式进行设计，但由于信息有限，这就是我能想到的。我并不是说这是最好的设计。我希望这能给您一些想法，可以从这里获取它并根据您的需要对其进行微调。
Here is one way you can design this.
I am not a database expert. I have designed databases for small sized applications with few tables. I try (emphasis on try, sometimes I fail to) to be more generic as possible so I am not confined to a corner when there is a scope change.
In your diagram, I noticed few things like your assumption of selling only DVDs. What happens if the shop owner starts to sell Blu-ray discs or portable game consoles. My suggestion would be to keep the design generic. So I rephrased the DVD table to Item so that the table could hold anything.
I also added Item type table so that you can define what type of item it is.
You transactions should be considered as a single order. A customer can place a single order that can contain a set of items they would like to purchase and also few items that they would like to rent. You wouldn't ask them to swipe the card twice, would you? Instead, create an order for the customer in the Order Header and place their line items in the Order detail table. The order detail table would also specify quantity like how many items the customer would like to purchase/rent. When they rent, the return date field would be null to begin with. When they return the item, you can go back and fill in the date by searching for the item id and customer id combination.
You can design this in
nnumber of ways but with limited info, this is what I could come up with.I am not stating that this is the best design. I hope this gives you some idea to take it from here and fine tune it according to your needs..
许多零售数据库将每种类型的交易（“哈利·波特的出售”、“哈利·波特的出租”）建模为同一个表中的不同行。因此，您将拥有“TransactionType”，然后您可以拥有 Customer->Transaction->LineItem（交易实例）->TransactionType。
Many retail databases model each type of transaction ("Sale of Harry Potter", "Rental of Harry Potter") as different rows in the same table. So you would have "TransactionType", and then you can have Customer->Transaction->LineItem (An instance of a transaction)->TransactionType.
我会将所有交易信息放在一张表中。根据交易类型（P 购买、R 租赁），某些字段可能无法使用，但那又怎样呢？磁盘空间很便宜。通过使用更少的表，代码将更简单并且运行速度更快。
I would put all the transaction info in one table. Some fields may not be used depending on the type of transaction (P Purchase, R Rental) but so what? Disk space is cheap. The code will be simpler and run faster by having fewer tables.