Table Design Question

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

  1. JeffTO

    JeffTO Guest

    Hello

    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
    on.

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

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

    L1Table
    L1_ID
    L1_Name

    L2Table
    L2_ID
    L1_ID (Linked to L1 Table)
    L2_Name

    L3Table
    L3_ID
    L2_ID (Linked to L2 Table)
    L3_Name

    L4Table
    L4_ID
    L3_ID (Linked to L3 Table)
    L3_Name

    L5able
    L5ID
    L4ID (Linked to L4Table)
    L5Name

    OR

    Suggestion 2

    Single Table
    Role_ID
    Level (Indicating which level the record is in 1, 2, 3, 4, 5)
    Name
    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

    Jeff
     
    JeffTO, Nov 7, 2011
    #1
    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
     
    Dan Dungan, Nov 7, 2011
    #2
    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
    way

    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

    Thanks,

    Jeff



    On Nov 7, 4:38 pm, Dan Dungan <> wrote:
    > 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
     
    JeffTO, Nov 7, 2011
    #3
  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
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. shannon

    table design question

    shannon, Jul 20, 2003, in forum: Access Table Design
    Replies:
    0
    Views:
    88
    shannon
    Jul 20, 2003
  2. Michael Camm

    Table Design philosophy question... Text vs Memo

    Michael Camm, Aug 8, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    110
    Michael Camm
    Aug 8, 2003
  3. Andrew

    basic table design question

    Andrew, Aug 20, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    105
    Andrew
    Aug 20, 2003
  4. J W Crosby

    Table Design Question

    J W Crosby, Oct 30, 2003, in forum: Access Table Design
    Replies:
    3
    Views:
    64
    Mike Sherrill
    Nov 9, 2003
  5. Carol

    Question about table design

    Carol, Oct 31, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    78
  6. CodeMonkey

    Table design question - Do I need to split this table up?

    CodeMonkey, Oct 11, 2007, in forum: Access Table Design
    Replies:
    7
    Views:
    172
    CodeMonkey
    Oct 13, 2007
  7. Mr C
    Replies:
    5
    Views:
    117
  8. JD McLeod

    Table Design or Report Design?

    JD McLeod, Jul 23, 2009, in forum: Access Table Design
    Replies:
    7
    Views:
    243
    Duane Hookom
    Jul 24, 2009
Loading...