数据库设计-来自多种来源的销售 [英] Database Design - sales from multiple sources

问题描述

我们目前有一个SQL数据库，其中包含一个表，该表保存我们公司的在线销售情况，该表使用其他网站（例如，亚马逊）销售产品。表格模式已设置为保存由我们当前销售商品的网站（例如，站点A）提供的特定销售数据/属性。

We currently have a SQL database with a table that holds online sales for our company, which sells products using other websites (say, Amazon). The table schema has been set up to hold specific sale data/attributes provided by the website our items are currently sold on (say, Site A).

我们正在将销售扩展到其他网站，这些网站提供与商品出售时网站A使用的属性不同的属性（例如，网站A可能提供唯一的销售编号，网站B可能不会提供唯一的销售ID号，但还会提供站点A未提供的其他信息（我们仍然需要捕获）。

We are expanding sales to other websites that provide different attributes than Site A uses when an item is sold (e.g. Site A might provide a unique sales id number, and site B might not provide a unique sales id number, but also provide some other info that Site A doesn't provide that we still need to capture).

问题是我由于架构会有所不同，请在我们销售的每个站点上为销售添加一个单独的表，或者尝试将所有销售合并到一个表中，无论使用哪种平台，如果不涉及某些列，则将某些列留为null特定的平台？又或者是一种混合方法，将两个站点之间不常见的属性仅分离到单独的表中，而主销售表则包含共享的属性（sale_price，sale_date等）？

The question is do I add a separate table for sales on each 'site' that we sell on, as the schema will be different, or try to combine all sales into one table, no matter the platform, leaving some columns null if it doesn't pertain to the particular platform? Or maybe a hybrid approach, separating only the attributes that aren't common among the two sites into separate tables, while a "master" sales table holds attributes that are shared (sale_price, sale_date, etc)?

还有其他一些表在保存内部信息（产品ID，成本等），这些表通过唯一标识符链接到销售表。无论选择哪种方式，我都需要提供一个可以在所有表​​中使用的唯一标识符（例如，自动递增的sale_id），并将其存储在表中以供引用/联接。

There are also other tables in play that hold internal information (product Ids, costs, etc), that are linked to the sales table via a unique identifier. Whichever route I choose, I'd need come up with a unique identifier I could use across all tables (auto incremented sale_id, e.g.), and store that in a table for reference/joins.

欢迎提出任何建议！

推荐答案

销售是销售>>相同数据属于同一表。我绝对不建议您将销售分成几张表，因为这会给随后的所有工作带来很多困难：销售统计信息等等。

A sale is a sale >> same data belongs to the same table. I would definitely not recommend splitting your sales to several tables as this creates lots of difficulty for all that might follow: sales statistics and so on. Try to keep all sales in one table.

如果这是一个非常小的项目，将不同的字段集成到一个表中可能是最好的选择。否则，您可能会尝试为每个销售平台创建配置文件：在这种情况下，请使用Entity-Attribute-Value模型。

If it's a very small project, it might be the best shot to integrate the different fields into one table. Otherwise you might try to create profiles for every sale platform: In this case, use an Entity-Attribute-Value model.

这篇关于数据库设计-来自多种来源的销售的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！