Table Structure and Relationships

[williamson1979]

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

[NeoPa]

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.

[williamson1979]

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

[williamson1979]

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

[NeoPa]

It's certainly an important question, and I don't have an absolute answer for you. I know databases but I don't understand your situation as well as you do, nor do I wish to. I'm here to tell you about database design - conceptual and practical.

From what you say my inclination would still be to keep conceptual items together. That's far from the last word though. Only you can think it through thoroughly and come up with a design that works. If it doesn't you'll experience problems that you'll have to deal with. It's the real world and only a thorough understanding of the situation will give you the best answer for your situation. That's not something I even want to help you with. I can't possibly know the full SP of your situation and any advice I give is likely to have undue weight.

I can only give you advice which is generally true about designing databases. Are the differences ones of fundamental design or just attributes of a common design. The question's certainly important, but it's a question for you.

[williamson1979]

If I add another “item” I just repeat fields. I’ve tried to search and read up on. I see various sites adding what i consider similar “items” in one table. I’ve also read to seperate tables like a person might file paper documents. So to me it’s hard to understand which is correct.

If I were to add my production items to a single table and of course the supporting tables then create additional tables for parts and another for maintenance records that would also seem correct since they are very different. Then build a relationship between parts and maintenance?

[williamson1979]

So my confusion is where do you draw the line on similar and different? I know Production is different than maintenance so that’s easy. But the materials and fuels in the production process is where I have questions. The materials are very different but are all part of the product. And there are multiple tanks for each that I need to track individually which is fine but in my example as you see I repeat fields. So is it better to repeat a few fields or duplicate a few tables? I think it’s easier to repeat a few fields but that might not be the best way.

[williamson1979]

On a side note even if it’s kinda long it will only have maximum 365 records per year in production and more like 250. If that makes a difference.

And from my last post with twinny I’m using his idea of the query as a record source rather than using vba to calculate fields. That was for another table but I can’t see how the same principle wouldn’t apply.

So if you see nothing fundamentally wrong I’ll put all Production minus the aggregate into one table/query and seek an opinion once I have it completed.

[NeoPa]

You're asking me to pronounce on your setup - which I'm not qualified to do. To get to that level of understanding would take me a great deal of work and would rely on you being able to express it totally clearly and without error. While I suspect you may manage the last bit, I'm not really here to get that deeply into individual projects. I can only really guide on the principles.

A good general guide is to see if the data will all fit into a single table without too much data being specific to certain of the types. If so then, generally speaking, it should be stored in a single table. If that can't be done without a lot of pushing and shoving then you may need to store the data separately.

I can't and won't tell you what your decision should be. That you must decide for yourself after full consideration, knowing that the success of your project, and how straightforward it proves to be to work on, depends on that decision.

[williamson1979]

I think I will put the data together then and add later if need be. My aggregate will be a separate table as it has the greatest possibility to expand rapidly. I’ll also create a two more tables for maintenance and parts. every thing that has a major difference or as with agg a high chance to expand will be seperate.

[twinnyfo]

As menitoned before, the key is to find single data points that apply to each and every prodcution run once and only once.

So, I know you have mentioned temperature before. If you measure temperature once and only once (or once at the beginning and once at the end) then having the temperature fields in the Production table makes sense. However, if you find yourselves measuring temperature throughout a run, then it is wiser as a separate table.

Remember, although we always prefer "taller" tables (fewer fields, but many records) to "wide" tables (many more fields), a "wide" table isn't necessarily bad. Again, it all depends on what can and ought to be captured as one "productoin record."

BTW, I do think we have the proper direction with the Aggregate Table, as your description and usage match perfectly with such a design.

[NeoPa]

Williamson1979:
My aggregate will be a separate table as it has the greatest possibility to expand rapidly.

If I gave the impression that having a large number of records was a problem in your design then I certainly didn't mean to. The numbers of records are not pertinent. How similar and standard is the data within those records is what matters. That said, Twinny knows your setup better than I do and he agrees the Agg table should be separate so that would appear to be a done deal.

My only intention is to ensure you get the design considerations out of the way as a very early step in the project as finding errors in that later on will be very troublesome for you. This is often difficult to call, as you can see, when projects don't fit neatly into the theory. Nevertheless, however hard it is to make the right call it's certainly important. Many who are still learning skip past this stage too quickly and without adequate attention to it.

People are always repeating mistakes made by others but it's best to avoid them if you can.

[williamson1979]

Hey twinny. So temperatures are just averaged for the purpose of this report. There are instruments that record and chart if it’s required constantly. So far as the production report goes every data input is once only. Either as a average, beginning, ending, added or percentage of.

[williamson1979]

Hey Neo. The aggregate table was Twinny’s idea. But for the calculations and materials it literally added 40 fields of agg1, agg2 etc repeating at least 10 times for each added, sold, used and percentage. He has me in the direction of agg, used, added etc which makes it very easy to add new material vs the way I had it. My way would have taken 2 tabs and queries from repeated field names. His way seems much better with the high chance of adding new materials.

Far as everything else associated with the report there is a possibility to add another fuel tank for instance but nothing like adding aggregate types and sources.

So from what you two have told me I think I’m headed towards the correct setup for my situation.

[williamson1979]

So to sum up. I’ve taken the dB twinny added the update for aggregate table too and started a clean dB with that format. I cleaned up field names by adding Asph, Prod, ASLime, FuelNG, FuelLP etc to identify each materials/ process in the table easier.

Then I set my record source to a query based on that table and added the calculations; so all my calculations are updated instantly which I like.

And I’m adding prefixes to all the controls and labels on my forms.

[NeoPa]

I'm happy if you're happy. If it isn't right then it won't be for lack of my highlighting potential issues or the importance of this stage of the project.

If I'm honest it sounds like quite a complicated project to be cutting your database teeth on, but that's not too atypical in real life (in business particularly), and you give the impression of being fundamentally capable.

Good luck with it.

[williamson1979]

I’m happy if it is correct for this project. Basically all the production is in one table except aggregate which has its own. They are all related fields. Obviously 1 material is not the same as another material but if it’s looked at as the materials for the production process than we go back to the single table. It’s like a pizza except I’ll only have 1 record per day vs many. So Heat, dough, meats, sauce and vegetables. Is it better to track it together in a single row or create tables for each ingredient type?

I’m learning so I have no strong opinion. I just want to try and get it lates out correctly.

[NeoPa]

Just so we're clear a pizza would have one table for ingredients, but certainly not one row. Each ingredient would warrant a separate row (or record as we call it in database terms). Stuffing all the data into a single row is certainly not what I'm talking about here.

Here's an example :

[tblIngredient]
IngredientID    PK  AutoNumber
PizzaID         FK  Long
ProductID       FK  Long
Quantity            Long

There would be a pizza table [tblPizza] with a [PizzaID] and a product table [tblProduct] with a [ProductID]. With FK references to these tables you could specify, separately, for each pizza type (Marguerita; Quattro Formaggio; etc --> [tblPizza]) and each product (ingredient in a recipe - Egg; Cheese; Olive; etc --> [tblProduct]) how much of each product goes in each pizza type.

I'm sorry if you already understand all this and I'm just repeating myself, but your last comment caused me to believe maybe you weren't quite getting my point. It may just be I misunderstood your comment of course.

[williamson1979]

I’m sure I don’t understand everything clearly. So I’m only recording general quantities. So using our pizza reference I’d record all types together. So only how much cheese, dough etc regardless of what type pizza.

My aggregate table does record individual types but not the production table. I’m only concerned with the big picture there. Im not adding customers, product type or jobs. That would be nice but being I’m green at this I’m happy to keep it simple and as I learn more maybe add more specific data.

[NeoPa]

It's been a long time since I actually started a project that way. For ease of terminology, and hopefully to lead to fuller understanding as things become clearer to you later on, we'll call it the Excel approach. As I say, I can't even remember how complicated that will make your life. It may be that you can push through this way and notice where it lets you down so that your next project can be built on more solid foundations.

I can hear you aren't prepared to throw away what you've already produced, and I can understand that. It's obviously not the way I'd want to lead you, but life is life and none of your choices at this stage is easy.

Let's see if we can help you progress meaningfully with your 'Excel' project and leave you to prepare for your next one as & when it comes. It's not all down-side. 'Excel' projects do have a tendency to require some clever VBA coding, which is actually fun. Also, as long as there are no plans to extend it later and the specifications you're working to are perfectly accurate, as they may well be in your type of situation, you may find it possible to produce a perfectly working resultant project. Even the most experienced of us started somewhere and very few haven't got an old project to look back on that had many things fundamentally wrong with it.

[williamson1979]

Hey Neo. It’s not really a question to abandon and start over or not. It’s really just has been a question about structure. Wether to seperate every material or to combine. If I get what has been said then I’d have the two tables since in production I’m merely entering a summary of the day. The aggregate portion is tracked with more detail and more materials. Anything more complicated than what I’m tracking isn’t really needed. At the end of the day it’s simply a daily production report summary with the the ability to generate some inventory reports for aggregate which is why I actually started this. Illso add a maintenance table which does have a benefit but not related to the production materials in any way.

[twinnyfo]

I think what both NeoPa and I are saying is that you must take a look at all of your data points (even just on a piece of paper) and plan out the design of your database. For every data point, determine where it should go and how it should be related to other data.

Just as an example--so that you understand what I mean--you are tracking production. One of the items tracked is the aggregate used. During your analysis of your DB design, you realize that there could be many types of aggregate (again, we've gone over this before, but it is a perfect example, so we go back to it), but those types of aggregate may differ from production run to production run. You then realize that you can put the aggregate in a separate table, related to the Production table. We've done that.

You must do that, using those same principles, for each of your data points: What type of data is it? Is it part of every production run? Are there multiple data points for this on each run? Are there different categories of this same type of data? Could these different categories be lumped into a different table of similar items? Etc.

As in a previous thread, we seem to be spinning our wheels--but we don't know what you are asking us to do. Much of this effort must come from you, knowing what you are measuring and recording and understanding the processes of what you are doing. In my experience, the best-designed databases come from people who have lived in the trenches, doing the work the database is capturing.

And, as of yet, we have not really made any progress or changes to the design/structure of your tables except the Aggregate. We are willing to help, but we don't know how to help because you are not asking for anything specific.

[NeoPa]

In essence the question was for advice on table structure. We've both given advice. The advice is not well received. That's understandable. It's very hard to see why one should redesign when one's own understanding doesn't include appreciation of the negative effects of the original proposed design.

I do agree we've done all we can for this question. I also agree that it's important that Williamson understands that to get more from what we can offer they need to post more clearly-defined questions.

Just as in the real world, we can still offer help. We aren't going to throw our toys out if they choose a different path than the one we advise. We'll take it as it comes. We may be forced to respond to a question with the explanation that we are not really able to help much because their design is all wrong but that type of question may never come up. We'll just have to see what we see.

[williamson1979]

The aggregate I understand and makes sense to me. So beyond that what do I do with the other materials?

So I’m listening so let me say what I understand so far. What I’ve understood is my aggregate has multiple points that are collected per production run so we modified the design to add it a unique table. So now we are at the other data points for production. Those data points are only one entry per production day and all are part of the production process. Being they are related and require only the data point I understood it was acceptable to put production in one table. So this area might be my confusion.

As far as in the field I’m definitely that so what I have would work great and it’s not complex to enter my data. Far as redesign I’m open if it’s needed.

[williamson1979]

Rethinking my wording. If I were to track precisely I’d include customers, jobs, product type, product design, etc. So then I know I’d need multiple tables. In reality I’m tracking very loosely so I’ve excluded all that data from the design since all I really need to know is production total and inventories.

So trying to target my question more precisely... Since I only need to loosely track inventory based off of each days production run is it a proper structure to include all materials used into a single table excluding the aggregate table which Twinny has already helped me with?

[williamson1979]

Hello,

This is the primary part of the db mostly complete minus some navigation. Probably has glaring errors but seems to work fine. This is basically the one you said was ok to have the long table Twinny except I moved the record source to the query for the calculations.

I do however have a "parameter" error soon as qryAggrInv is opened. I simply created a query from tblAggr then added a expression so the inventory for each aggerate type could be tracked. Any help on this would be appreciated.

Thanks

[twinnyfo]

Remove the last field that is grouping your expression.

Plus, why does tblAggr have AggrID, when it should be ProdID? This would be confusing to anyone looking at your DB.

Plus, you've changed the Production Table so your Aggregate query won't work any more.

[williamson1979]

That’s what I’ve been asking and saying. I changed the source of the production table to a query to do the calculations. I ask about that quite a few times in this thread.

The aggregate was working when I tested it before zipping up the file. The only issue I seen was when I created the query to calculate inventory, the grouping it caused parameter issues.

[williamson1979]

Far as the AggrInv query it was tracking additions and subtractions just ask parameters for everything. So I’ll look at again soon as I’m back to my pc but didn’t notice the aggregate not working. So I’ll have to retest that.

[williamson1979]

Hey,
So I got back to my PC and looked the queries over. I changed qryAggrInv from group to sum so that no longer ask for parameters. Thanks

While looking it over I noticed qryAggregate ask for a couple of parameters as well so I just did all the math needed in the query so that seems resolved.

I'm unsure exactly whats not working in the aggregate subform though. I'm not getting an errors and it seems to be tracking with the tblProduction entries so if you would please let me know what I'm missing there Id appreciate it.

Thanks for everyones help

[williamson1979]

So last thing I found wrong. Aggsub sums #error if tblProduction field ProdTotal is 0. This one gets me since it is summing total used, sold and added that’s not related to ProdTotal. I get the fact that the percentage of agg errors since it needs that field. Any ideas?

[NeoPa]

Hi Williamson.
There seems to be some confusion as to what a thread entails here at Bytes.com. It should be treated as the place to discuss a single technical question you have about your project.
It isn't an interface for someone to be mentored generally across a whole range of issues.

May I suggest you get your thoughts together and post new questions (Clearly and precisely obviously) as and when a particular issue you need help with.We can pretty well write this one off as a learning experience. It certainly won't provide much value to any others wishing for help with similar questions.

Dealing with others through such an interface is certainly a skill, and sometimes requires some experience. It is necessary however if these exchanges are to provide any real value.

Mentoring through such an interface is largely impractical anyway. It makes a hard job doubly difficult when the discipline of staying on topic is not there. What may be easier for you makes what is already a difficult job for our experts extra complicated and unrewarding.

[williamson1979]

I’m down to a couple specific questions. I can post a thread about them. My apologies bouncing around.

[NeoPa]

It's all a learning curve. Important points, but you're new so we make allowances of course :-)

[williamson1979]

Thanks. Appreciate it

[williamson1979]

Plus, why does tblAggr have AggrID, when it should be ProdID? This would be confusing to anyone looking at your DB.

Plus, you've changed the Production Table so your Aggregate query won't work any more.
1 Days ago #27

Hey Twinny,
I corrected all field names so the query was working but it only took a minute to change it the production form source back to the table rather than the query so other than making some field name changes so its easier for to see what Im looking at its back to the same format.

The ID question. No explanation for that other than it makes more sense to me when Im working on this to look for the ID that matches the table Im working with rather than 2 similar IDs.