我应该如何设计一个包含六个或更多变量的数据表？
情况类似于以下情况：
假设我是一家卖水果的商店，我想记录每种水果的成本。假设客户有特定的品味，他们可以区分一切。
- 水果可以是橙子、苹果、 梨或桃子
- 供应商可能新鲜 n 天
- 水果来自不同国家，出口税不同
- 水果也按重量计价
- 水果可能有处理要求（冰箱、水枝）
- 水果也可能仅因为以下原因计价不同的起源
一个想法是效仿某些行业的做法，为每个变体分配一个唯一的产品代码：例如 Orange5dayfreshAustralia200gfridgeSydney
然而，这将是一个非常长的值列表，并且如果任何成本发生变化，那么搜索将是地狱来自土耳其的 200 克苹果现在的出口税增加了 20 美分。添加新变量也会严重扰乱整个表，并且需要大量重新编码。
另一种方法是假设一个标准的橙子、桃子、苹果和梨以及不同的重量（二维表）。在其他表格中，针对任何给定偏差准备成本节省/增加的列表。 （概率定律应该告诉我，我有 80% 的“标准”水果，以及 20% 来自时髦地方、价格昂贵的利基水果）
例如，无论重量如何，1 天的新鲜橙子与标准的 4 天新鲜橙子相比要多 50 美分。
（是的，这意味着主表需要至少有一个不影响其他变量的变量）
最终约束：数据输入由雇工完成，需要易于在 Excel 中输入。编程可能很困难，但通用界面并不困难。
有什么建议吗？
The situation is akin to the following:
Assume I am a store selling fruits, I would like to record the costs of each type of fruit. Assume the customer has specific tastes, and they can differentiate everything.
- A fruit can be an orange, apple,
pear, or peach - It could be n days fresh from the vendor
- The fruits come from different countries and have different taxes for export
- Fruits are also valued by weight
- Fruits could have handling requirements (Fridge,water sprigs)
- Fruits could also be valued just because of different origins
One idea is to do what is done in some industries, assign a unique product code for each variation: e.g. Orange5dayfreshAustralia200gfridgeSydney
This however, would be a HUUUUUGE long list of values, and should any costs change, it would be hell to search for Apples from Turkey that are 200 grams have now 20cents more export tax. Adding new variables would also massively mess up the whole table and require much recoding.
The other way is to assume a standard orange, peach, apple and pear and different weight(2 dimensional table). On other tables, prepare a list of cost savings/increase for any given deviation. (Law of probability should show me I have 80% "standard" fruits, and 20% niche fruits from funky places having funky costs)
E.g. a 1 day fresh orange compared to a standard 4 day fresh orange is 50cents more, regardless of weight.
(Yes, this means the primary table would need to have at least one variable that dont affect the others)
Final constrain: The data entry is done by hired hands, needs to be simple to enter in Excel. The programing can be difficult, but not the general interface.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果需要将信息编码到产品代码中，可以使用子代码构建完整的产品代码。
您可以使用国家/地区代码表示国家/地区，并使用其他子代码进行处理和来源。
示例：
是一个巴西苹果，已放置 6 天，重 145 克，具有 HR 处理和 OR 来源。
显然，每种代码类型都需要一个查找表和外键。
If the information needs to be encoded in the product code, you can use subcodes to build a complete product code.
You can use Country Codes for the country, and other subcodes for handling and origin.
EXAMPLE:
is a Brazilian apple, six days old, weighing 145 grams, with HR handling and OR origins.
Obviously you would need a lookup table and foreign keys for each of the code types.
您似乎已经解决了您的问题 - 您的数据结构是错误的。
您的列表实际上是适用于您购买的每“批次”库存的属性的集合 - 也许“n 天新鲜”除外，其中该属性是采摘日期和交货日期属性之间的差异。
因此，您真正想要的是每个属性类型的数据表，然后是添加属性的交易数据表。输入是一种形式，其中属性是循环。
您还需要表中的定价规则，可使用该规则来计算价格。
如果您的交易表同时记录销售和购买，那么您可以根据您喜欢的任何组合过滤要报告的属性。
但让我感兴趣的是你如何计算出你所销售的水果的年龄。您可能从文件中知道，您一天从西班牙购买了 x 个橙子，另一天从意大利购买了 y 个橙子，但盒子里的橙子只是橙子 - 那么您如何辨别哪个橙子呢？或者您打算使用 FIFO 或 LIFO 方案？
You seem to have twigged your problem - your data structure is wrong.
Your list is really a collection of attributes that apply to each "batch" of stock you purchase - except perhaps for "n days fresh" where the attribute is the difference between the attributes of date picked and date delivered.
So what you really want is a data table for each attribute type, and then a transcation data table where you add the attributes. The input is a form where the attributes are loopups.
You also need your pricing rules in a table, which you can use to calculate prices.
If your transaction table records both sales and purchases then you can filter on the attributes to report by any combination you like.
But the bit that interests me is how you work out how old the fruit is you are selling. You might know from the paperwork that you have purchased x oranges from Spain on one day and y oranges from Italy on another day, but in the box oranges are just oranges - so how do you tell which are which? Or are you planning to use a FIFO or LIFO scheme?