Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Database structure for tables with a variable number of fields.

    Hi all, first post here.

    I have been tasked with creating a database of our manufacturing processes and am stumped. I am not even sure how to describe the problem I have in database lingo.

    I have a table of parts. Each part has anywhere from 1 to 4 sequences in the manufacturing process. Each sequence has a list of tools. For the purpose of this database the ToolListID matches the Sequence. The Sequence is a unique number consisting of the PartNumber_SequenceNumber. This much I have figured out. Now comes the part that I don't know how to go about.

    I have determined that we have three basic types of tools.

    T1: The tool interfaces directly with the machine and uses another tool.
    T2: The tool interfaces with another tool and uses another tool.
    T3: The tool interfaces with another tool and is used to manufacture the part.

    |MACHINE|>T1>T2...T2>T3>

    In the end one Tool can consist of one T1, any number of T2, and any number of T3.
    T1 will always be the first tool in the setup.
    T2 tools will fit either into a T1 tool or another T2 tool.
    T3 tools will only fit into specific T2 tools. (Some T2 tools use two different T3 tools simultaneously)

    I am currently using Access 2007.

    I need to make a list, via a report, that will give me the list of Tools as well as their individual breakdowns. The problem I am seeing is that the Tools do not have the same length of attributes so I can not make a table called Tools. The person that reads the report needs to be able to assemble the tool so the details are quite important along with their order.

    Any suggestions? (On existing layout or tables and the main problem)

    I have attached the database relationship table. You can see my problem where I need to indicate the adaptor, sleeve, tool and insert. Adaptors are primarily T1, sleeves/tools are T2 and inserts are T3. I have not restructured the relationships yet as I was going to wait to see what types of suggestions you all had.

    I think I need some sort of a table to store the individual types of tools, and then separate tables to store each tools possible "sub-tools".

    PS I have no formal database training, however, I am familiar with VBA and the Microsoft Office environment.

    Thanks for all your help in advance!
    Attached Thumbnails Attached Thumbnails relationships.png  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The good news: The may be a new issue to you, but its actually an amalgam of common database design requirements.
    The bad news: These are not easy design requirements to model. Seriously, this requires at least moderate level DBA skill to model and to code for, if not advanced, and I'm guessing that you don't fall into these categories yet. Your employer really needs to hire an outside consultant to work on this with you.
    The really bad news: Doing this right pushes the limits of MS Access as an application platform, and arguably goes beyond it. If you do manage to create this is an MDB file, the result will be a holy mess of tables, queries, macros, forms, and VB Code that, well.... might ensure your job security at your current employer for life, but that's about the best that could be said of it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    What type of application would be more suited to this? Something based on SQL? I was hoping to make a quick db at the start of this task and quickly discovered the complexity. What type of keywords should I be looking for when researching for this type of problem?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    This would be best suited for a database platform with a rich SQL toolbox. I would say SQL Server for ease of maintenance. Or Oracle, though SQL coding in Oracle is more cumbersome than in MSSQL Server. Others (R937) might disagree with me, but I'd recommend against MySQL for a schema this complex.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...but this begs the question: are you going to try creating this database application by yourself at the same time you try to acquire the dba skills necessary to do it, and at the same time you learn the workings of a completely new database platform?
    Contract with a DBA to assist you, please....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2012
    Posts
    3
    Currently I am managing 6000+ excel documents as the previous individual in my job position was more comfortable using excel... That being said about one quarter of those documents are updated on a regular basis. On top of this, the excel documents have numerous formats (no templates were used) so the information is spread out in different spots in different areas. I am pretty good at learning things on the fly, and do the majority of it through the software documentation. I believe we have MSSQL running here already which may make this less painful. Sounds like I'll be learning MSSQL now. In the meantime I am stuck updating these stupid excel files (pardon my english).

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What you have, my friend, is a data problem.
    Get a ticket out of Excel-Hell, and move to relational databases.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

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
  •