将两个数据库表合并成一个？ [英] Turn two database tables into one?
问题描述
在将关系数据库建模到库存管理系统时，我遇到了一些麻烦。现在，它只有3个简单的表：
I am having a bit of trouble when modelling a relational database to an inventory managament system. For now, it only has 3 simple tables:
-
产品
Product
ID | Name | Price
收款
Receivings
ID | Date | Quantity | Product_ID (FK)
销售
Sales
ID | Date | Quantity | Product_ID (FK)
由于收货和销售相同，我正在考虑一种不同的方法：
As Receivings and Sales are identical, I was considering a different approach:
-
产品
Product
ID | Name | Price
Receivings_Sales（名称无所谓）
Receivings_Sales (the name doesn't matter)
ID | Date | Quantity | Type | Product_ID (FK)
任何人都可以帮助我选择最好的选择，指出这两种方法的优点和缺点？
第一个似乎是合理的，因为我在一个ORM的方式思考。
Can anyone help me choose the best option, pointing out the advantages and disadvantages of either approach? The first one seems reasonable because I am thinking in a ORM way.
谢谢！
推荐答案
我个人喜欢第一个选项，即 Sales 和接收。
Personally I prefer the first option, that is, separate tables for Sales and Receiving.
选项2中的两个最大的缺点是将两个表合并为一个：
The two biggest disadvantage in option number 2 or merging two tables into one are:
1) Inflexibility
2) Unnecessary filtering when use
首先是不灵活。如果你的需求扩展（或者你只是忽略它），那么你将不得不分解你的架构，否则你会得到非规范化的表。例如，假设您的销售现在包括执行销售交易的销售员/人员，因此显然它与'接收'无关。如果你做零售或批发销售，你将如何适应你的合并表？如何折扣或促销？现在，我在这里找出明显的。现在，让我们转到接收。如果我们希望将我们的收货与我们的采购订单绑定？显然，采购订单详情如P.O.数字日期，供应商名称等不会在销售，但显然与接收有关。
First on inflexibility. If your requirements expanded (or you just simply overlooked it) then you will have to break up your schema or you will end up with unnormalized tables. For example let's say your sales would now include the Sales Clerk/Person that did the sales transaction so obviously it has nothing to do with 'Receiving'. And what if you do Retail or Wholesale sales how would you accommodate that in your merged tables? How about discounts or promos? Now, I am identifying the obvious here. Now, let's go to Receiving. What if we want to tie up our receiving to our Purchase Order? Obviously, purchase order details like P.O. Number, P.O. Date, Supplier Name etc would not be under Sales but obviously related more to Receiving.
其次，在使用时进行不必要的过滤。如果你已经合并表，并且只想使用表的 Sales （或接收）部分以通过后端或前端程序过滤掉接收部分。
Secondly, on unnecessary filtering when use. If you have merged tables and you want only to use the Sales (or Receving) portion of the table then you have to filter out the Receiving portion either by your back-end or your front-end program. Whereas if it a separate table you have just to deal with one table at a time.
此外，您提到了 ORM ，第一个选项将最适合这一努力，因为显然一个对象或实体应该区别于其他实体/对象。
Additionally, you mentioned ORM, the first option would best fit to that endeavour because obviously an object or entity for that matter should be distinct from other entity/object.
这篇关于将两个数据库表合并成一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
