none
Redesign a big table

    Question

  • Hi

    I use Access 2007 and I am going to re-design a database. One of the tables has >230 fields. I
    want to divide this table in several smaller but I need to have one form which will display all
    those fields with an efficient manner.  How can I do this? Using a sub-form?

    Each time only a few fields will be filled. Can I “hide”  the rest of them that I will not use?
    Can I use something like a drop-down list to select only the fields I need to fill with data?

    thanks a lot in advance

    Friday, February 04, 2011 3:39 PM

Answers

  • Actually, the database have a Task table with >200 fields and a Materials table with >230 fields :-) Some of those tasks are rare. Some of those meterials used rarely...

    The problem is that i don't know how to design the database. Which criteria to use in order to devide those huge tables?

    I would suggest again reading the articles I linked above.  They will give you an introduction to the method for deciding how to decompose your table.  I also have a tutorials page here: http://www.rogersaccesslibrary.com/forum/database-design_topic238.html, which not only as the articles in a downloadable format, but it has tutorials and links to additional resources.
    -- Roger Carlson
    MS Access MVP 2006-2011
    www.rogersaccesslibrary.com
    • Marked as answer by Bessie Zhao Friday, February 11, 2011 8:45 AM
    Friday, February 04, 2011 5:17 PM
  • jsamon,

    In a sitation like this you honestly have 2 choices.

    1) start education yourself about proper database design and development and prepare to become a real database developer (because those are the skills you will need to develop to do this properly).

    2) hire someone (either a person or a company) who already has those skills - either to create a better database app for you (on a contract basis), or to bring on-staff either temporarily or permamently.

    The links that Roger gave you earlier are a decent start for path #1, but you will probably want to start on smaller stuff until you bring your new skills up to the level you will need to tackle this kind of database problem (which is technically referred to as " a mess")  :-).

    Sorry to say it that cut and dried, but based on what you're saying, that is where things seem to be for your situation.

     


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

     

    • Marked as answer by Bessie Zhao Friday, February 11, 2011 8:45 AM
    Friday, February 04, 2011 5:20 PM

All replies

  • You're jumping the gun by thinking about the form.  First you have to redesign the table.

    A table with 230 fields is almost always denormalized.  Therefore you're going to need to normalize it first.  After you see what your table/relationships look like, THEN you can think about forms. 

    There's lots of information about normalization on the web, but I'd suggest the following blog serie:

    What Is Normalization

    Entity-Relationship Diagramming

    The Normal Forms

    In that order.

     


    -- Roger Carlson
    MS Access MVP 2006-2011
    www.rogersaccesslibrary.com
    Friday, February 04, 2011 4:00 PM
  • jsamon,

    The first question which will need to be asked about this is what is hiding in all that data in 230 fields?

    By that I mean: are all those fields hiding duplicate copies of data from multiple different items which should be separate table entities all their own? if so (which is highly likely), how many of those things should be broken out into separate tables, and what really are all the relevant rules regarding how these things really interact in your business model? It is all these sorts of things that need to be given some careful thought and analysis before deciding what to do with your table.

    Only after we discover the actual business entities which are represented in this mega-table and make sure we know what the interrelationship rules are can we begin to consider what the user interface should really look like.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Friday, February 04, 2011 4:14 PM
  • well, all those fields regard quantities of materials which may or may not be used in a labor task

    Is this helpfull?

    thanks!
    Friday, February 04, 2011 4:43 PM
  • jsamon,

    Well that tells me for certian that the relational rules were not followed in designing the table at all. So, yes it helps somewhat.

    So it sounds like you have a set of Tasks, a set of Materials, and possibly also items like Laborers, Task-Labor, and Material-Labor information which may factor into your business problem. These could all be separate tables with their own business rules for how that data should be retained, maintained, entered, etc. ...as well as the rules for when and how they are combined to perform a given business task/job/quote/contract....

    By using the data in the way you have it, most of those things are all hidden, and the databse cannot help you enforce those rules (or prevent you from breaking them - for good or ill). Also, you have no real protection from duplicating data (either correctly or incorrectly), which is something else that a proper database schema design helps to handle.

     Let me guess: this database started life as an Excel spreadsheet...


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)
    Friday, February 04, 2011 4:57 PM
  • Actually, the database have a Task table with >200 fields and a Materials table with >230 fields :-) Some of those tasks are rare. Some of those meterials used rarely...

    The problem is that i don't know how to design the database. Which criteria to use in order to devide those huge tables?
    Friday, February 04, 2011 5:09 PM
  • Actually, the database have a Task table with >200 fields and a Materials table with >230 fields :-) Some of those tasks are rare. Some of those meterials used rarely...

    The problem is that i don't know how to design the database. Which criteria to use in order to devide those huge tables?

    I would suggest again reading the articles I linked above.  They will give you an introduction to the method for deciding how to decompose your table.  I also have a tutorials page here: http://www.rogersaccesslibrary.com/forum/database-design_topic238.html, which not only as the articles in a downloadable format, but it has tutorials and links to additional resources.
    -- Roger Carlson
    MS Access MVP 2006-2011
    www.rogersaccesslibrary.com
    • Marked as answer by Bessie Zhao Friday, February 11, 2011 8:45 AM
    Friday, February 04, 2011 5:17 PM
  • jsamon,

    In a sitation like this you honestly have 2 choices.

    1) start education yourself about proper database design and development and prepare to become a real database developer (because those are the skills you will need to develop to do this properly).

    2) hire someone (either a person or a company) who already has those skills - either to create a better database app for you (on a contract basis), or to bring on-staff either temporarily or permamently.

    The links that Roger gave you earlier are a decent start for path #1, but you will probably want to start on smaller stuff until you bring your new skills up to the level you will need to tackle this kind of database problem (which is technically referred to as " a mess")  :-).

    Sorry to say it that cut and dried, but based on what you're saying, that is where things seem to be for your situation.

     


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

     

    • Marked as answer by Bessie Zhao Friday, February 11, 2011 8:45 AM
    Friday, February 04, 2011 5:20 PM