Table Design Question

[JeffTO]

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

 

[Dan Dungan]

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]

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

 

[Dan Dungan]

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.