Table Design Question

Discussion in 'Access Table Design' started by JeffTO, Nov 7, 2011.

  1. JeffTO

    JeffTO Guest


    I have a question on Table Design

    For Part of a DB that I am designing I have a 5 Level Reporting
    Structure that I am trying to create the most efficient table design
    for. I want to make sure that it is built for the easiest way to
    enter the information but also to query and exract information later

    Which once of the following is "better" or more Proper"

    Suggestion1: (Which is the way I would normally design my tables)


    L1_ID (Linked to L1 Table)

    L2_ID (Linked to L2 Table)

    L3_ID (Linked to L3 Table)

    L4ID (Linked to L4Table)


    Suggestion 2

    Single Table
    Level (Indicating which level the record is in 1, 2, 3, 4, 5)
    LinkedToID (Where this would link to a record in the same table)

    I am seeing pros and cons to both potential designs and am just not
    sure which way to go - if anyone has any thoughts or comments that
    would be very much appreciated. If anyone needs any more details
    please let me know

    Thanks in advance for any comments

    JeffTO, Nov 7, 2011
    1. Advertisements

  2. JeffTO

    Dan Dungan Guest

    Hi Jeff,

    In your first example, I'm unclear how each table contains a field "name".

    In your second example, how are you going to link two records?

    Could you provide sample data, and maybe a written description of the entities and relationships?

    Dan Dungan, Nov 7, 2011
    1. Advertisements

  3. JeffTO

    JeffTO Guest

    Thanks For Replying Dan

    In the first example:

    L2 is releated to L1 with a 1 to many relationship between
    L2Table.L1ID and L1TableL1ID
    L3 is releated to L2 with a 1 to many relationship between
    L3Table.L2ID and L2TableL12D
    Same for L4 and L5
    The "Name" field in each table simply refers to the fact that each
    Record will have a Name or Title (there is a lot of other fields as
    well for each record in all of the tables but the design is basically
    the same for each table)

    In the 2nd Example the records would be "linked" through the
    "LinkedToID" field - unlike traditional relationships between tables
    this design would only denote that Record 2 was "related" to record
    one as it would have a"1" in the "LinkedtoID" field. I would handl
    the entegrity through my design I am just not sure this is a good way
    to set up the table and I think my first suggestion is the most ideal

    Let me know if this gives sufficient answers to your questions and if
    not I will provide some sample data to try and explain better


    JeffTO, Nov 7, 2011
  4. JeffTO

    Dan Dungan Guest

    I'm curious what L1 etc. represents. What are the relationships.

    When I see, "L2 is releated to L1 with a 1 to many relationship between
    L2Table.L1ID and L1TableL1ID"

    it seems you're modeling a family with five generations or something.

    I just don't understand how I can help. Sorry.
    Dan Dungan, Nov 9, 2011
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.