Help Please! Database Design - Relationships

[Kryst51]

OK, just read your response, It's no problem how you receive these, but that's a major part of design is how the physical process is done. I have a lot of ideas for you, so I am working on it....When I'm done, I'll post it and you can tell me what you think, and tweak it so it fits you better.

 

[Kryst51]

Also, it helps me to do it, before I explain it, so if what you really wish for is to do it yourself, let me first so I know how to tell you, if I try to tell you without doing it, I'll mess up...

 

[ceinwyn]

I would love to be able to do it myself but unfortunately this is where I stuck
I would be really grateful if you can show me how to overcome this on the file. And I think showing it on the actual file would be far more descriptive for me too, than telling it. I am a visual learner too.
Also, I have some other areas that I want to apply database system (like defect reports). If I can see how it is done on an actual work of my own, I think I can do the rest.
Thank you for all your efforts.

 

[Kryst51]

Will the original factory sheet quantity stay the same for an item? For instance, if the factory sheet says QTY: 10 of item one needed, will that always be true, so that if you pull 10 pcs, and 1 piece is destroyed for some reason, you still need 10 good pieces? to fill the requirement of the factory sheet?

 

[Kryst51]

OK, This factory sheet thing is so confusing, I need a run down of the process. A factory sheet = job. This is filled out by the salesman? Or your production people? Is it in essence a work order?

Is this what you are trying to achieve?

1. You get a factory sheet.
2. You enter the info into the database.
3. The factory sheet gets "filled" - What does this entail, putting items in a package, producing items, then putting them in a package? I also want to talk more about what you said about some material being scrapped, that is a little confusing as well.

4. Output = factory sheet completed, backordered, etc.... (Is this right?) I don't think the term "order" is correct, as this is already what you said, we need to define the purpose of this database. It would help me a lot.

I am feeling a little overwhelmed myself with this, but am excited to work on it, because I am learning a lot. Part of it, is I don't know your process.

 

[Kryst51]

I also have a feeling that I have made this more complicated then this needs to be, so maybe someone else could look at it on here and suggest ways to simplify it.... And I suspect the majority of the problems, again come from not knowing the process or exact purpose.

 

[ceinwyn]

Ok, I think I understand what you mean, the problem is partly me maybe, because I am working with this process everyday and expect everybody to be familiar with it
Well, in the attached file I tried to make a workflow as detailed as possible. Hope this would be helpful.
(yes i love colorful charts)

Cheers.

 

[ceinwyn]

Also the purpose of this database is:
1- Keep track of the jobs in my production area
2- Get weekly scorecards generated automatically (currently I am filling them via pivot tables, and copy paste data)
Scorecard needs the following info for each day:
1- how many production jobs come in (also the total number of qty pcs and qty sheets for the incoming jobs)
2- how many production jobs are printed (also the total number of qty pcs and qty sheets for the output jobs)
3- how many production jobs are cancelled (also the total number of qty pcs and qty sheets for the cancelled jobs)
4- how many production jobs are in the backlog (also the total number of qty pcs and qty sheets for the "in progress" jobs)
5- how many sample jobs come in
6- how many sample jobs are printed
7- how many sample jobs are cancelled
8- how many sample jobs are in the backlog

I would like to be able to generate this report from the database weekly with a Weekly Report function ( just the idea makes me happy )
Currently I have several cells (areas) under my responsibility, and each cell manages to make a mistake on this report, I have to check them all before sending them out.

And at the end of the month, I have to generate a monthly scorecard, which combines the weekly reports. This would be easy with the database also.

Also, you see the OPC and DIC fields. They are used in a formula to give me a value for every ORDER. And I have to create a weekly report which shows the sum of that weeks values. I have to achieve a target.

Currently, I am doing this with pivot tables too. Again copy paste from the pivots to a separate spreadsheet.
I want to be able to generate this report with access also.

Hmm, I am trying to think if I need anymore info from this database.
I think this is all for now, but for example, after a while, I will be able to analyze the reprints too. Now, I am not doing that. Maybe I would change the NOTES field into a lookup table for reprint reasons ( ı don't know couple of major reasons ) then I might be able to get the problem areas that causes most reprints and losses.

Well, this is a general scope for this database.
You see, it would help me tremendously! Because if I can manage to pull this through, I am thinking of an operator performance kinda database. which would be a sidekick for this one.

I think I am getting out of control the database demon has gotten into me...

 

[Kryst51]

Would it also be helpful to track which cells have which "orders"? That work flow is extremely helpful.

 

[ceinwyn]

Oh good to hear that, I have started to think if I made things more complicated with that workflow after I sent it

Actually, my cells are in different places, and I am receiving their reports separately. I am planning to put the database in each cell, and treat them as separate factories (actually they are). So their data will never be mixed anyway.

 

[Kryst51]

Ok, looking at your flowchart, one question is raised, is the RBO connected to the "Item" or is the RBO connected to the factory sheet. It looks like the RBO is a one-to-one to the factory sheet. And the item is a one-to-one to the factory sheet.

Is that correct?

 

[ceinwyn]

Item is connected o an RBO normally. because every item is a product of that particular RBO. But, in my database I wanted to connect the RBOs to the item, because from production point of view, RBO is just a reporting function but item itself is the key element.
But as long as every factory sheet has one item and one item has one RBO I am ok with any change on the relationships.

 

[Kryst51]

OK, Thank you for clarifying.

 

[Kryst51]

In your flow chart are color of tag, size of tag, type of tag (ladies, mens, kids), what it is (T-shirt, etc.). for the item name. How does this translate to the item, or substrate? ie. Substrate name = Ladies or mens or kids, Substrate type = T-shirt, Substrate GSM = Size.

Also, in your tables, Don't use numbers or special characters in the table names or field names.

One more thought, if you put the database on each cell as a separate entity, it will make it difficult to summarize the information from all cells together. I would suggest having cell identification built into the database and keeping it together as one. You would still be able to report by cell and would still be able to have each cell doing entry at the same time, as access handles this kind of stuff well. (at least in my opinion).

 

[Kryst51]

I've been thinking more about the factory sheet thing. If a factory sheet only has one item on it. Then I think that from a database standpoint you may not need a separate item table. Think about the possibility of having item parts (Item name, Size, Color, RBO, Substrate Name, Substrate Type, Substrate GSM) These parts get put together to form the factory sheet with the pc quantity on it. During reporting you would still be able to group together so that it appears as an "item" but you would be cutting out a middle linking table which tends to make things messy especially if you are just learning access, it is hard to figure out how to make forms and queries work sometimes.

A draw back would be, is if you use the same combination of item parts a lot, you would have to reenter that combination everytime you had a new factory sheet.

What do you think?

 

[Kryst51]

One more thing, what identifies a group of factory sheets. I don't mean the primary key, I mean what gives meaning to the group if you were trying to find a particular group of sheets? What grouping of fields, or is there a name that you refer to? So if you were trying to find a particular group of factory sheets you would recognize it on a report or in a search?

 

[Kryst51]

When you have to rework part of a factory sheet for whatever reason, is it still part of that same production group, or can it go on a new production group. Would the rework hold up the completion of the rest of the production group (order)?

 

[ceinwyn]

Sorry for late reply,

1- Ladies, men, kids, t-shirt... these are not related to my database, I just wanted to make it easier to visualize what my business deals with. Only important element is the item name, the others are just examples. Substrate = the paper we print the tag on. GSM = the weight of the paper (gram / square meter), Type = type of the substrate ( gloss, matte ... ) - these are all connected to an item. This info comes to us in the factory sheet, so we would know which paper/gsm/type to use on an item.
2- I cannot put cells together, they are all on different countries!
3- Having item parts on separate table makes sense, I am going to think about it.
4- A group of factory sheets are identified by their production requirements, as seen in the chart, the planner looks at a bunch of factory sheets that day, and tries to make a plan for the presses. You can have a 6 color press, a 4 color press and a 2 color press. It would be nice to group items with 2 colors and send them to 2 color press, in order to block the 6 color machine with 2 color jobs. This is a pure planning part, I receive these groups already made by planning (they staple the factory sheets together), and we make the print layout according to this group (that I call "order"). I do not have a name for this group, that's why I created an autonumber field to make them unique. All I want to see are "the group of factory sheets that I process" and "individual factory sheets info that these groups hold". I cannot use just group because I also need to see the items and their individual details.
5- When I rework a group it becomes a new "order" as seen in the chart. The sales dept might think that it is still the same job but I also need to keep track of my printed sheet amounts. Even when the job is destroyed during post production and couldn't sent to the customer, I still must have that item on my printed list because I actually printed them. If I enter the "order" as the old order "I would lose my past printed sheet data" and "I would lose track of my operators time tables". I cannot erase an actually printed job from my database just because the post printing messed it up. I have to enter each reprint as a new job.

hope this helps,
is it possible to make a order entry form with the info at hand?
because I am running out of time. if I can see an example I believe I can do something, but I just don't know how to add sub factory sheets to an order.

frustrated a little

thank you for your help...

 

[ceinwyn]

ha ha
i have two fours
sorry for that...

 

[Kryst51]

OK, I have a basic db set up, I just haven't put the production aspect in. I have been thinking about it though. Let me know what you think of this. I am actually going to post two. One is where I take out the item table, and build the item at the factory sheet level, assuming that there is only one item per factory sheet. The other assumes that there can be multiple items per factory sheet. The first on is further along as I have worked with that idea a little bit longer.

I still think the table structure needs to be tweaked, but given the timing, I want to give you what I have. (I guess what I am saying is I am not as far along on this as I would like to be)