存储订单地址的最佳策略(Best strategy for storing order's addresses)

存储订单地址的最佳策略(Best strategy for storing order's addresses)

我有一个'战略'问题。

事情是，我们有一张客户地址和客户订单表。 结构是类似的（只是一个例子，忽略提交类型等）：

Address id INT line1 TEXT line2 TEXT state TEXT zip TEXT countryid INT

为了保留数据的历史有效性，我们将这些地址存储在带有订单的文本字段中（以前是通过引用完成的，但这是错误的，因为如果地址更改所有旧订单也会更改送货地址，这是错误的）。 例如：

Orders id INT productid INT quantity INT delivery_address TEXT

delivery address与CONCAT_WS("\n",line1,line2,state,zip,country_name)类似。

一切都很好，很花哨，但客户似乎需要访问历史数据，并能够导出XML格式的文件，并希望将这些行再次正确地分开。 因为有时候没有line2或者state或者zip或者其他什么东西，我们怎么能把这些信息存储起来，然后我们就可以解读每一行的“标签”？

建议存储为JSON编码数组，但这是一种最佳方式吗？ 我想过把它存储为XML ......或者可能创建那些6-10个额外的列，并存储每个订单的地址数据？ 也许你们中的一些人在处理这种东西方面有更多的经验，并且能够指引我朝着正确的方向前进。

提前致谢！

I have a 'strategy' question.

Thing is, we have a table of customers' addresses and customer orders. Structure is something like (just an example, ignore filed types etc.):

Address id INT line1 TEXT line2 TEXT state TEXT zip TEXT countryid INT

To preserve historical validity of the data we are storing those addresses in a text field with orders (previously it was done by reference, but this is wrong because if address changes all old orders change delivery address too, which is wrong). E.g:

Orders id INT productid INT quantity INT delivery_address TEXT

delivery address is something akin to CONCAT_WS("\n",line1,line2,state,zip,country_name)

Everything is nice and dandy, however it seems that customers need an access to historical data and be able to export those in XML format and they want to have those lines split up properly again. Because sometimes there is no line2 or state or zip or whatever, how can we store this information in a way that we can then decipher the 'label' of each line?

Storing as JSON encoded array was suggested but is this a best way? I thought about storing it as XML... or maybe create those 6-10 extra columns and store address data with every order? Perhaps some of you guys have more experience in dealing with this kind of stuff and be able to point me in the right direction.

Thanks in advance!

最满意答案

就我个人而言，我会将地址建模为单个表，每更新一次地址都会生成一个新行，这会被标记为当前地址。

如果没有相关订单，我想你可以允许删除，但是将旧记录标记为非活动状态会更简单。

这将允许您保留订单和地址之间的关系，并在以后轻松查询历史数据。

请参阅维基百科条目以获得缓慢更改的维度

Personally I would model the addresses as a single table, every update to the address would generate a new row, this would be marked as the current address.

I guess you could allow deletes if there are no related orders, however it would be simpiler to mark the old record as inactive.

This will allow you to preserve the relationship between orders & addresses, and to easily query the historic data at a later date.

see the wikipedia entry for slowly changing dimensions