Need improvements in table design (1 Viewer)

[Ramnik]

Hello everyone ,
i have made a production database application in which table structure looks like this .... (see attachment)

every section stock is maintained with a query and i need to sum the total stock from each section which is not possible with that number of queries . do i need to change the table design or any other optimisation ????

thankssss

 

[plog]

Without seeing some sample data and knowing more about what the system is for its hard to make many recommendations. One I do see is tbl_DailyBroachProduction and tbl_DailyBeltProfileProduction should probably be merged into 1 table. Since they have essentially the same structure this data should probably all be together in one table.

 

[Ramnik]

actually broach & belt profile are two different sections of a factory ... first the product is processed in broach section and then moved onto the next section i.e. belt profile . although the table field names looks same but still the product is processed in each section separately . moreover each table distinguishes each section and we need to track the product during production in similiarly 12-13 sections .... we need to maintain each section;s balance stock to be processed . and the query i m using to maintain this .
only pass quantity from broach is moved to belt profile while balance stock is not ...

 

[spikepl]

You have not provided a single valid argument to counter plog's objection to your having separate tables, instead of having one table with an extra column. Putting same kind of stuff in different containers goes against normalisation. There is no normalisation police, but the price for not normalised data is increased coding effort and maintenance load.

 

[Ramnik]

i need to monitor each section's production datewise and productwise,
i dont have same data //.... production of one section is different from the other .
suppose 1 section on 1-01-2012 produce A=30 pc., b=40pc., d=50 , and the other section produce e=45pc, a=15pc, s=47 pc.
if i combine into a single table how would i know which product is from which section and i have 800 products . and i want each stock lying at each section .
if u have a better design i will surely change it .. plz suggest

 

[Ramnik]

and suppose i produce a=30 pc in section 1 and it is then moved to stock query of section2 . then the section2 can only process from a=30 pcs and not more . it is a chain of processes for each product going through each section . and if i want to track any product i find it in the balance qty of each section's stock query

 

[plog]

suppose 1 section on 1-01-2012 produce A=30 pc., b=40pc., d=50 , and the other section produce e=45pc, a=15pc, s=47 pc.

Again, we have no idea what that statement really means. Especially since I see no 'Section' field in the data you posted. While we do not understand your data better than you, we do understand data in general pretty well. So, when we see 2 tables with identical structures, we know that's not the way to do things. There's always exceptions, but the case you've laid out doesn't sound like one.

Until we can see sample data and understand the process better our opinion isn't going to change. Can you provide sample data and/or give us an explanation of the process--and by that I mean explain the physical process and not what you envision happening in a database system. Use no database jargon.

 

[Ramnik]

this is the attachmnt for the illustration . have a look and suggest a better table structure ..
i need whole section's stock combined into one single stock for shortage calculation as well .....
thankssss

 

[plog]

Helps a little, but only confirms that the two tables need to be combined. I also now see an unneccessary field in your tables--'TotalQuantity'. You should not store calculated values. This seems to be equivalent to PassQuantity + RejectQuantity, if so, do not store this value in your table--calculate it in your query when you need it.

Last, and this may be digging too deep too soon, but I don't believe your TotalBelt Profile data is correct. In it you have a field called 'PASSQTY' which seems to be the summation of the 'totAL qty' field. How come its summing that field and not its similarly named 'passqty' field? The Total Broaching data sums the 'passqty', but Total Belt Profile data does not.

Again, the over riding issue is you need to merge these 2 tables. You do that and you can get to Stock @ Belt Profile data without the sub-queries.

 

[Ramnik]

ya i found the first entry of total belt profile is incorrect there should be 53 instead of 55 ... but that is not the question ...
can u provide a table design ..... ????????????
if i combine the two tables how would i know which field is for which section's production ????????? can u suggest field nnames for my table ????? without saying same thing again nd again . i want to know if i combine the two tables into one can i get the all things that i need , ??????? thankssss

 

[sgbotsford]

My suspicion is that you are looking at the problem incorrectly. Or I'm not understanding it correctly.

I think you are saying that you have a multi-section factory, each section produces different products. In some cases a given product needs to be processed several times in different sections.

If this visualization of mine is correct would it not be easier to keep it as a single file, with a few more fields for recording it's current status, current location moving through the system?

You essentially have a problem in queueing theory. Yes, you could maintain a separate table for each queue, but it's a LOT easier to just a a field for queue.

Also: Do you actually need real time status on this? If you don't, then regard each move of a pallet load of stuff from E to F as a transaction. At the end of the day, you run a report against the transacation database.

 

[Ramnik]

thanks for ur reply .. actually what i m saying is i have a product is processed in each section day by day .. and i have this table structure ...
if u suggest a single table for whole sections its fine , i will change it . can u give me the field names for this table with respect to my earlier needs ?
and i want to maintain stock for each of the section . and my products are of 4 types in which 1 product mught go skipping 1 or 2 sections and 1 product might go through in a different sequence ., how would i achieve that ? current i am querying this in form design in lookup list based on the type of product ..... can it all be possible if i design it in a single table ???????????? and how will i achieve a chained flow of products through each section from within a single table .... i find it as a very lengthy table filled with lots of lots of fields ...

 

[Ramnik]

well initially i have posted this question because i cant total the balance quantity at each section in query design as the number of queries become large . so i needed to maintain the total balance stock out of this . and i was suggested to keep all data in a single table .
well i appericiate that but can i be able to exatract each information out of this one table in which date,section name, product,passqty etc are there ?????
and if not then is there any way to convert the stock query into a stock table and then extract all the stock from tables instead .... plz suggest
thankssss

 

[plog]

To merge them you would make a new table based off the structure of tbl_DailyBeltProfileProduction (because it has one more field than the other table). I would call this new table 'tbl_DailyProduction'. You would eliminate the 'TotalQuantity' field and then create a new field to determine which section the record is for. This can either be a text value, or it can be numeric and link to a table that defines your sections.

and how will i achieve a chained flow of products through each section from within a single table

I do not know what this means, nor do I see it in the sample data you provided. If somehow you are batching products together you could create yet a new entity in a table called tbl_Batches so that you can keep all items together that need to remain together. Then you would have a new numeric field called 'BatchID' in whatever table the batches need to be tracked in. Again, I don't see how you are doing this currently, so this sounds like a new issue.

 

[sgbotsford]

I'm not about to create your whole system for you.

Your table has fields:

Date
ProductName
TotalQty
RejectQty
Section

You use a standard summary report, grouping by sections.
Where you need it, you calculate passed. (You only need to count two of the 3 of pass, reject and total. Record the two that are easiest to count.

I'm still unclear: Is product A actually made in two places? Or is A partly made in one section and then moved to another section for more work?

 

[Ramnik]

If somehow you are batching products together you could create yet a new entity in a table called tbl_Batches so that you can keep all items together that need to remain together. Then you would have a new numeric field called 'BatchID' in whatever table the batches need to be tracked in. Again, I don't see how you are doing this currently, so this sounds like a new issue.

i dont batch items together, if thats the issue i would issue items as a batch number rather than integer quantity ....
and only adding a section_name field doesnt solve all the problem that i have , will i be able to track each section's present stock and maintain a chained flow of which section the product would go next ????? currently i am doing this with limiting the input forms upto balance stock of that section .

 

[Ramnik]

I'm still unclear: Is product A actually made in two places? Or is A partly made in one section and then moved to another section for more work?

the product is the same , only the processes vary . product A is processed in many sections to produce its final form .....

suppose i have a product A (type1 , (there are total4 types of product)) which goes in this flow section1 -> section2 -> section3-> section6 .
currently what i am doing is as i have each section's different table and each section's stock is maintained with a stock query . i implement this flow which help of querying the products based on types ...... ...

can i maintain each section's stock .... ? and can u provide me with the data that i have shown to u in previous pic ? thankssss

 

[plog]

Based on the last image you posted (Post #8), you are not tracking products through sections. In that image you posted there is no field that ties a specific record of one table to a record in the other. Yes, you have product name fields in both, but they don't directly tie to another record in the other table, thus my idea of 'batches' to do so.

As for maintaining stock--do you mean determining how much of a product is in each section, a running total of sorts? If so, that's easy if all of the data is in the same table so that's not an argument against consolidation.

As for providing you with the data you have shown in the previous pics, I can't because you admitted your data is wrong. Show me an updated image with good data and I will show you how it should work consolidated.

 

[sgbotsford]

Ok's try again:

Your basic table for this system. Let's ignore the defective ones, add that one later.
tblWorkflow
Date
Section
Item
Count

If you have 20 sections, this table at any given time have Sum (Section(i)*item(i)) records.
You may want an additional section called Warehouse indicating that it's in inventory.

Let's start with nothing in the factory.

2013-02-06 Item A Section 7 Count 60
2013-02-06 Item B Section 8 Count 100
2013-02-06 Idem C Section 9 Count 120
2013-02-06 Item A Section 9 Count 40
...
A suitable report will crank out the totals for you.

However, you may want to add routing to this.
So you have a 1 to many relation between item and route,

Route:
Item
Sequence
Section
NextSection

A typical table for this would look like this:
Item A Sequence 1 Section 3 NextSection 7
Item A Sequence 2 Section 7 NextSection Warehouse
Item A Sequence 3 Section Warehouse
Item B Sequence 1 Section 4 NextSection 5
Item B Sequence 2 Section 5 NextSection 19
Item B Sequence 3 Section 19 NextSection 2
Item B Sequence 4 Section 2 NextSection Warehouse
Item B Sequence 5 Section Warehouse.

...

So now you run a query against tblWorkflow that summarizes what was done. You can save this as a temporary table.

Now generate a report, using that table, and for each Item & section looking up NextSection, generate a report organized by Nextsection, with details by item number.