Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2013
    Posts
    4

    Talking Great Question about Database Design for Manufacturing Company

    My company makes widgets that our customer designs. since we dont design them, we just make them, the customer tells us a few important pieces of information that may change based on their specific order.

    First I have a relationship between the customer data table and a table with the data about each individual order. we use an in house order number and that is always different so that's my key. I'm a novice just trying to help bring us out of the stone age technologically so I want to design a database. the problem im having is this.

    each job has several piece numbers that the customer gives us. then each piece has several characteristics, length, bend angles per piece (may be up to 14), design of ends(may also be up to 20 or so) and they may all be different between each order from our customers.

    I dont want to have to make hundreds of blank rows in the table for our orders that will have bend degrees and cut lengths when many of our jobs only have a few pieces. we do get larger orders with up to 60 or so different part numbers.

    I guess I need a suggestion on how to set up the tables.

    we call the part numbers Mark #s or MK# some jobs have only two or three MK#s and some have many more. each MK# will always have a few characteristics like length and bend angles. the problem is each MK# may have several bend angles and rotations.

    How would you handle it?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    have a table that defined the elements as a child table to the main MK. eg:
    table: MkDetail
    index
    bend
    rotation

    you may also need a separate table if say there are or could be more than two ends, other wise I@d store the end detail in the MK primary table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2013
    Posts
    4

    What about this?

    I have a question, when I need to connect a child form for mark numbers to the parent form for jobs would it be better to make a lot of fields in that table for the variables in that mk# (up to 14 bends and 14 different rotations) or should I make a 14 child tables of the child table to each have the specific bend and rotation information for that MK#?

    each job has up to 120 mark numbers 50 bends of up to 14 different degrees and up to 35 different rotations. each bend has a degree of bend a rotation and a distance from the end of the piece.

    and some jobs only have one or even no bends.

    then there are some things that are specific to each mk# that don't have a lot of variables. a couple of lugs welded to them and the kind of end preps that are needed.

    its a dusey of a problem that will need a smart mind to solve

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    aaargh why 14 child tables?

    the n elements are all sub defintions of the one part. so I'd expect those sub defintions to be all linked to the one part but in one table
    if a job doesn't have a bend/rotation definition then so what... no further involvement.

    im not too certain what you are trying to say with
    each job has up to 120 mark numbers 50 bends of up to 14 different degrees and up to 35 different rotations. each bend has a degree of bend a rotation and a distance from the end of the piece.
    . Does that mean you have 120 'products'...
    .. from your original question you inferred there were upto 14 bends/rotations
    on the face of it the sub table detailing wach of the bends/rotations seems fairly strraight forward
    table: MkDetail
    ParentPartID PK 'equiv to mark no (I think)
    DatumDistance PK 'this is the distance from the spefcified end and making it part of the priamry key means that it will mean there can only ever be one definition for this part/distance
    bend
    rotation

    aside form simplifying development, testing and so on the other good reason for these sub elements to b e in one table is that you can have as many sub elements as needed. ferinstance what would happen if your customer came to you and demanded 15 (or more) elements. in the one table per element design you'd need to add new tables, make program changes, do testing and so on. in the one table for all elements there are no program changes (you may need to check some reports to make certain all n elements display but beyond that there is no developer impact.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2013
    Posts
    4

    Thank you Big Time

    First of all thank you very much for your time I really appreciate the help.

    The more I look into this the more it seams like I will not be able to database the process down to the level of including bending information in the database.

    Ill try to explain the process.

    our customers send us the design information for the products we make.

    we receive a quote request and generate a quote that is when a job number is assigned by us. there are lots of information regarding that job that I need to track regarding the inspection and documentation of liscensing requirements.

    then there is the technical data for that specific job.

    there may be lots of MK#s for each job.

    there are MK#s for each piece that we cut and bend.

    each MK# can have lots of bends and rotations.

    there is other information that is specific to each MK# also but the bends and rotations are the only thing that has so many variables.

    what I need to be able to do with the data is generate a report that will take the data the engineers put in about the bends and rotations into a sheet they can use to create the product.

    also tracking this will be valuable to generate the shipping labels and part labels. so it will save lots of time if I can get it.

    right now they engineers put the bends and rotations on a axel sheet and that is printed and given to the benders.

    so my problem is that if I make a table that links MK#s to a parent table for the job number then I would need to make a Child table of the MK#s table that has the variables for bend and rotation for the up to 40 bends.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so my problem is that if I make a table that links MK#s to a parent table for the job number then I would need to make a Child table of the MK#s table that has the variables for bend and rotation for the up to 40 bends.
    ...for the upto 14 bends (the maximum per part)
    im getting confused with the differnet numbers is it 40 or 50 different types of bend


    you are going to have to create that in any event. there's no way of getting round that.
    bear in mind having once created the part you can then clone that data if the customer re orders or orders more than one of the same mark.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2013
    Posts
    4

    Thank you

    I was afraid of that. thank you for the help I really appreciate it.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •