发票&发票行：如何存储客户地址信息？
您好，我正在开发一个发票应用程序。
因此，总体思路是有两个表：
Invoice (ID, Date, CustomerAddress, CustomerState, CustomerCountry, VAT, Total);
InvoiceLine (Invoice_ID, ID, Concept, Units, PricePerUnit, Total);
正如您所看到的，这个基本设计导致大量重复记录，其中客户端将具有相同的地址、州和国家/地区。
所以替代方案是有一个地址表，然后建立一个关系Address<-Invoice。
不过，我认为发票是不可变的文档，应该按照最初制作的方式存储。有时，客户会更改他们的地址，或说明其是否来自地址目录，这将更改所有以前开具的发票。
那么你的经验是什么？
客户地址如何存储在发票中？在发票表中？地址表？或者其他什么？
您能否提供对此进行更详细讨论的书籍、文章或文档的链接？
Hi I'm developing an invoicing application.
So the general idea is to have two tables:
Invoice (ID, Date, CustomerAddress, CustomerState, CustomerCountry, VAT, Total);
InvoiceLine (Invoice_ID, ID, Concept, Units, PricePerUnit, Total);
As you can see this basic design leads to a lot of repetiton of records where the client will have the same addrres, state and country.
So the alternative is to have an address table and then make a relationship Address<-Invoice.
However I think that an invoice is immutable document and should be stored just the way it was first made. Sometimes customers change their addresses, or states and if it was coming from an Address catalog that will change all the previously made invoices.
So What is your experience?
How is the customer address stored in an invoice? In the Invoice table? an Address Table? or something else?
Can you provide pointers to a book, article or document where this is discussed in further detail?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我强烈建议不要在发票中存储任何客户详细信息。
相反，我会有这样的结构：
客户表，主键为 id
客户地址表（因为每个客户随着时间的推移可能有不同的地址），客户 id 作为外键
发票表，地址字段为客户地址表的外键。
顺便说一句，我会考虑为每个行项目添加一个增值税字段。有些国家/地区对不同的商品类型有不同的增值税税率。
I would strongly recommend against storing any customer details like that in the Invoice.
Instead, I would have a structure like:
Customer table, with a primary key of id
Customer address table (as each customer may have different addresses over time), with the customer id as a foreign key
Invoice table, with an address field that is a foreign key to a customer address table.
BTW, I would consider adding a VAT field per line item. There are countries where there are different VAT rates for different item types.
大多数标准产品/订单数据库都会有，
然后您的订单项目就成为订单和产品之间的多对多关系表。
要获得完整的发票，您需要查询订单表并将其与 OrderItems 表连接起来。 OrderItem 通常具有购买价格等信息，因为产品表中的价格在订单创建后可能会发生变化，并且该信息通常对于存储很有用。
Most standard product/order databases will have
Then your order items become a many-many relationship table between orders and products.
To get a full invoice you'd query the orders table and join it with the OrderItems table. OrderItem usually has purchase price and such because the price in the product table may change after the order is created and that information is often useful to store.
我会考虑使用三个表来完成此操作：
Customer、Invoice和Address，但构造它以便一旦输入地址，它就会从未更新或删除，仅已弃用。您的地址表中可以有IsDeprecated或IsActive布尔字段。然后，当您创建发票时，发票会链接到当时使用的 CustomerID 和 AddressID。当客户更改地址时，您可以使用新的 AddressID 创建一条新记录，并使用布尔字段弃用旧记录。或者，如果您确实想保留良好的记录和/或需要查找此数据，您可以使用AddressActiveStartDate和AddressActiveEndDate，但这会使查询稍微麻烦一些更复杂。这样，您仍然可以存储旧地址，并且仍然链接到客户以供参考，同时还允许客户拥有多个列出的地址（例如，一个用于运输，一个用于计费）。
您可以根据需要添加更多表格，例如
Product、InvoiceLine、State等。I'd consider doing it with three tables:
Customer,InvoiceandAddress, but construct it so that once an address is entered, it is never updated or deleted, only deprecated. You can have anIsDeprecatedorIsActiveboolean field in your address table. Then when you create an invoice, the invoice links to the CustomerID AND to the AddressID being used at the time. When the customer changes their address, you create a new record with a new AddressID and deprecate the old one with the boolean field. Or if you really want to keep good records and/or will ever need to look up this data, you could have anAddressActiveStartDateandAddressActiveEndDate, but this will make queries a little more complicated.That way you still store the old address, with it still being linked to the customer for reference, while also allowing the customer to have more than one listed address (e.g. one for shipping, one for billing).
You can add more tables as necessary, e.g.
Product,InvoiceLine,State, etc.