Capacity Planning Database

[Shane_Mc]

Hi All,

For a collage project we are trying to build a database to store information for a capacity planning tool. The required information is primarily based around times taken for an operation and the number of people required.

This information all needs to be stored by production line. In this manufacturing there are sub-assemblies or sub-lines which feed into further sub-activities which lead ultimately to the main line. However, this is to be a generic tool theoretically there could be an infinite amount of levels of these lines. In practice it should not go over 11 or 12.

Has anyone got any idea how to go about arranging the tables and ER diagrams to deal with this.......without having to build a separate table for each level? Any help with this one would be greatly appreciated.

If my description of the problem in paragraph 2 is not clear enough I will post a diagram of the situation.

Thanks
Shane

 

[ggreg]

I would think that you would not need a table for each line
but you will have fields like

id mainline levels sublevel
1 A 1 1
2 A 2
......................
10 A 10
11 B 1
12 B 2
13 B 3
14 C 1

Does this help your thinking or not?

 

[Shane_Mc]

Thanks for your responses guys, but I'm still stuck. ggreg, I had initially thought of doing something similar to what you suggested but it wouldn't suit because what we have to develop is a generic tool and not everyone who would be using the tool would have the same number of parent-child levels.......hence, null fields.

Pat, a classmate is doing a project where he requires a database to act as a BOM and I did out his basic ER diagram for him......he added some extra fields and stuff but the rest was mine, so I understand the workings of a BOM but couldn't apply it to my situation. As you suggested I did a search for both BOM and Bill of Material but it hasn't solved my problem. I also searched (as was suggested in another thread) through geneology and tree structure. There are many topics on all these areas but none of them seem to deal with the basic structure of the tables.

I'll just keep working but if any of you think of anything or have a more indepth explaination I would really really appreciate it.

 

[ggreg]

Shane_Mc, I think your understanding my thought...but I am not sure
how it would allow nulls but i could see where my system
is going to limit ...sub levels to how many fields you would build...
I am thinking with mine that for a production line A
you could have 1 or 12 or 144 or infinite amount of levels along
with an infinite amount of sub lines for what ever level you needed. With that said, I guess you need to build some kind
of adjustable multi array instead of a single array with the arrays
allowing you to expand fields in a table.

Pat I would like to learn more about what your telling me
because I thought I was at the parent level with A, B and C
production lines ...what do you mean by self reference ?
Could you expand a little more on your comments for me?

 

[Shane_Mc]

ggreg, I think what you suggested allowed for nulls in that if you allow 12 fields for layers, that allows 12 layers of activities or sub assemblies.....but if a user only has a requirement for 5 layers the last 7 fields are empty. Maybe I've mixed up your message.

What I'm toying with at the moment is the idea that even the main line is just an assembly with a set of activities. And as such any sub-assembly in the level directly below can too be considered an activity......Just like as Pat suggested a BOM structure. This means that I have three tables, tblLine (because any given user can have a number of lines in the system), tblAssembly and tblActivity. Every assembly has a number of activities.....of which some may be assemblies (at a lower level in the hierarchy). But I don't know how to list these assemblies as an activity of another assembly. I'm guessing that as Pat said it is some form of self referencing, just cause these assemblies will be refered to in two tables.......but I'm still stumped!

 

[ggreg]

if you ever get this figured out I would like to see
it if you could attach it here... currently I can
open 97/2000/and XP.....I think you dont understand
me because of your comment...5 layers the last 7 fields are empty
what i am talking about is a field called level
in which you would put a number in it along side that field
is a field that names the main line
main line is A
and if A has 12 level you put in
A.....1
A.....2
all the way to 12 but if A only has 5 you would only have
5 numbers 1 thru 5.......but from Pat's comment I
must be missing something for my way to work if
I get a chance tomorrow I will attach a mdb.