表结构和关系 [英] Table Structure and Relationships

问题描述

我想要建立表和关系的建议。快速摘要...


TblProduction包含与生产过程相关的生产总量和各种温度。


TblEnvironental包含生产时的天气状况。它不需要来自表格制作的数据，但需要记录每天的制作。


TblPropane和tblNaturalGas包含起始，添加和使用的数量。它使用总生产数量来计算每吨生产所使用的加仑数。


ThbDiesel不需要来自tblProduction的数据，但需要记录并与tblProduction保持同步。它记录燃料库存


TblAsphalt需要从tblProduction的总量来计算百分比。该表有4个与之关联的表，用于转换测量值和温度。


TblLime需要来自tblProduction的总数量来计算使用的百分比。


我还有一些，但他们都做同样的事情。因此，在我更困难的聚合表上，twinny帮助我改进了这一点，所以现在我想让所有表格正确相关或者如果需要调整表格，然后再将更多精力放在表单和查询中。谢谢

I want advice on setting up tables and relationships. Quick summary...

TblProduction contains production totals and various temperatures related to the production process.

TblEnvironental contains weather conditions at the time of production. It doesn?t require data from table production but does need to be recorded with each days production.

TblPropane and tblNaturalGas contains starting, added and used qty. it uses the total production qty to calculate gallons used per ton of production.

ThbDiesel does not require data from tblProduction but needs to be recorded and kept in sync with tblProduction. It records fuel inventory

TblAsphalt requires total quantity from tblProduction to calculate percentages. This table has 4 tables associated with it to convert measurements and temperature.

TblLime requires total quantity from tblProduction to calculate percentage used.

I have a few more but they all do the same thing. So on my more difficult aggregate table twinny helped me get that correct so now I want to get all the tables related properly or if needed adjust tables before putting more effort into forms and queries. Thanks

推荐答案

我认为这些表中的许多表能完全相同但不同的物品（丙烷，天然气，柴油等）是否正确？


如果是这样，那么将它们存储在单独的表中并不是一种有序的方法。您应该尽可能地将表格结构与实际情况对齐，为此您必须学会区分什么是简单属性和什么构成单独的概念。


我怀疑这是您应该在项目中处理的第一个级别，但我知道您已经在处理一些查询。坦率地说，这是背靠背的。


你必须（我说必须从最适合你的角度来看，不是我以任何方式负责你的项目）你明白了。）把一件事放在另一件事上，除非你想让整个大厦在你的耳朵周围翻滚，否则你必须在你开始构建它之前得到结构。


有支持相同概念的多个表格是一个明显的错误，应该在任何查询工作之前纠正。


我知道在这个阶段听到这不是好消息，但是相信我，它没有更好的听到它进一步下线。

Would I be right to think many of these tables do exactly the same job but for different items (Propane, Natural Gas, Diesel, etc)?

If so then storing them in separate tables is not a well-ordered way to do it. As much as possible you should align the table structure to the actual situation and for this you must learn to distinguish what is a simple attribute and what constitutes a separate concept.

I suspect this is the first level you should be dealing with in your project but I know you''ve already been working on some queries. Frankly that is back-to-front.

You must (I say must from a perspective of what works best for you not that I''m in charge of your project in any way you understand.) build one thing upon another and, unless you want the whole edifice to tumble down around your ears, you must get the structure right before you start building upon it.

Having multiple tables supporting the same concept is a glaring error that should be remedied prior to any work on queries.

I know this isn''t good news to hear at this stage, but believe me, it doesn''t get any better hearing it further down the line.

嘿Neo，


所以让我们这样想吧;

你有一家餐馆。你有更多的常数，如电力和天然气。你必须让它们煮。但它们不是食物。 （对我来说这是LP，NG和Diesel）


然后我们有成分。肉类，奶制品，蔬菜。它们都是最后一餐的一部分，但却是非常不同的部分。 （对我来说就是沥青，石灰，骨料等。）


然后我们还有工艺/配方，多少盐，什么肉，什么温度的厨师？ （对我来说它的相似之处。总成分是多少，每种成分的百分比，过程温度是多少）


最后使用上面的例子，我们需要知道我们购买了哪些成分，我们烧的食物（废物），我们今天使用的每种肉，蔬菜等量是多少？ （所以对我来说，我输入销售，浪费并计算总数。其他所有成分使用该总数来计算其使用的百分比）。



所以现在更具体......我的成分


1）聚合 - 我有多种聚合类型和来源可以同时使用。


2）石灰或添加剂 - 我可以同时使用但不能同时使用。它们只是防止沥青从骨料中剥离。我认为我将这些长期添加到组合表中的问题是我总是可以用不同的填充物替换我的石灰。因此使我的桌子更加混乱。


3）沥青 - 多个储罐和等级。不同等级永远不会一起使用但是如果多个坦克确实具有相同等级，则所有等级都可以使用相同的生产运行。我没有看到将所有坦克组合成一个表的问题，除了计算完成的方式我打破了重复的规则...... Tank1，Tank2，Temp，Temp2等许多领域。

4）NG或LP都用来加热聚合物但从不在一起。此外，在加热时，可以使用重油甚至是柴油，这样可以真正拉长桌子。



所以我明白你在说什么但同时从我学到的东西，我需要单独的表来制作任何这些可能的附加或扩展，或者我误解了什么？


所以现在我是在尝试正确地把它们放在一起的过程中。我把tblProd和tblEnviro合二为一。然后我创建了该表的查询并将计算添加到该查询。最后我使用查询作为记录源创建了一个表单。在继续前进之前，我会等待反馈。谢谢

Hey Neo,

So lets think of it this way;
You have a restaurant. You have more constants such as electricity and gas. You must have them to "cook" but they are not "food". (For me this is LP, NG and Diesel)

Then we have ingredients. Meats, milk products, veggies. They are all part of the final meal but very different parts. (For me that is Asphalt, Lime, Aggregate etc..)

Then we also have the process/ recipe, how much salt, what meat, cook at what temp? (For me its its similar. How much total ingredients, what percentage of each ingredient, what were the process tempertures)

Finally using the example above we need to know what ingredients we purchased, did we burn food (waste), what amount of each meat, veggie etc did we use today? ( so for me I enter sold, wasted and it calculates the total. Every other "ingredient" uses that total to calculate what its percentage used was).


So more specific now... My ingredients

1) Aggregate - I have multiple aggregate types and sources that can be used at the same time.

2) Lime or Additives - I could use both but never at the same time. They simply prevent stripping of asphalt from the aggregate. The problem I think I''d have adding these long term into a combined table is I could always replace my lime with a different "filler" thus making my table more confusing.

3) Asphalt - Multiple storage tanks and grades. Different grades will never be used together However if multiple tanks did have the same grade the all could be used the same production run. I dont see a issue combining all the tanks into a single table except the way the calculations are done I break the rule of repeating... Tank1, Tank2, Temp, Temp2 and so on for many fields.

4) NG or LP are both used to heat the aggregate but never together. Also when it comes to heating theres the possibility of using heavy oil or even diesel in a pinch so it could really lengthen a table.


So I understand what you are saying but at the same time from what Ive learned I need seperate tables to make any of these possible add-ons or expansions or have I misunderstood something?

So for right now I''m in the process of trying to put it all together correctly. I combined tblProd and tblEnviro into one. Then I created a query of that table and added the calculations to that query. And last I created a form using the query as the record source. I''ll wait on feedback before proceeding to far. Thanks

如果我将所有内容添加到一个表中，这将是一个示例。它只完成Fuel **字段。 Lime和Asph将有另外6个计算字段，它们是粗体。此示例中以粗体显示的字段仅在查询中没有表中的计算。我还必须增加5个字段（如果我从来没有必要添加第二个燃料或石灰罐，那么总共56个字段）到Asph结合和/或说明销售或转移的Asph。如果我最终使用这个结构，我也做了一些重命名以分隔表/ qry中的项目。我没有包含聚合，因为从Twinny如何向我展示它有自己的table / qry并作为子表单插入。我还想添加一个维护/零件库存，但它是独立的，因为它与生产无关，所以我认为它与这个结构的prt无关。


ProdID ProdDate ProdStart ProdStop ProdFireup ProdSold ProdWaste ProdTotal ProdTPH ProdMix ProdInlet ProdExit ProdPhoto EnviAMTemp EnviAMCond EnviPMTemp EnviPMCond HeatLPStart HeatLPStartGall HeatLPStop HeatLPStopGall HeatLPAdded HeatLPUsed HeatLPTon HeatLPProd HeatNGStart HeatNGStop HeatNGUsed HeatNGTon FuelStart FuelStop FuelAdded FuelUsed ASLimeStart ASLimeStop ASLimeAdded AsphGrade1 AsphTemp1 AsphGrav1 AsphCorr1 AsphWeight1 AsphStart1 AsphStop1 AsphAdded1 AsphGrade2 AsphTemp2 AsphGrav2 AsphCorr2 AsphWeight2 AsphStart2 AsphStop2 AsphAdded2

If I add everything to one table this would be a example. Its only complete up to the Fuel** fields. Lime and Asph would have another 6 calculation fields which are in bold type. Fields in bold in this example are only in the query no calculations in the table. Id also have to add 5 more fields (So up to 56 total fields if I never have to add a 2nd fuel or lime tank for instance)to Asph to combine and or account for sold or transferred Asph. I also did some renaming to separate items in the table/qry if I were to end up using this structure. I did not include the aggregate because from how Twinny showed me it has its own table/qry and is inserted as a subform. I also want to add a maintenance/ parts inventory but it would be separate since it has nothing to do with production so Im thinking its not relevant for this prt of the structure.

ProdID ProdDate ProdStart ProdStop ProdFireup ProdSold ProdWaste ProdTotal ProdTPH ProdMix ProdInlet ProdExit ProdPhoto EnviAMTemp EnviAMCond EnviPMTemp EnviPMCond HeatLPStart HeatLPStartGall HeatLPStop HeatLPStopGall HeatLPAdded HeatLPUsed HeatLPTon HeatLPProd HeatNGStart HeatNGStop HeatNGUsed HeatNGTon FuelStart FuelStop FuelAdded FuelUsed ASLimeStart ASLimeStop ASLimeAdded AsphGrade1 AsphTemp1 AsphGrav1 AsphCorr1 AsphWeight1 AsphStart1 AsphStop1 AsphAdded1 AsphGrade2 AsphTemp2 AsphGrav2 AsphCorr2 AsphWeight2 AsphStart2 AsphStop2 AsphAdded2

这篇关于表结构和关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！