cr113 wrote:

I disagree. Your design depends on what is important--not vice versa.


I'll need to be
Is the first level always Company? Is the next level always Division? Is
the next level always Manager? Is the next level always Employee?

What does the ID table give you that you don't get from foreign key
references from Employee to Manager, from Manager to Division, and from
Division to Company?

You did not include an Employee table. Is there a difference between the
Employee table and the Manger table? What is the difference?

Your requirements will determine where the ballpark is. Since those are
mostly unknown to the rest of us, none of us can legitimately say
whether you are in it.

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 3:03*pm, cr113 <cr... (AT) hotmail (DOT) com> wrote:
If your design goals lean toward simplicity and inspectability, you
may want to forego the "ID" table altogether and specify your
relationships via subset requirement constraints (implemented as
foreign keys) between the tables directly.

This approach (as any other) will require careful consideration of the
identifying attributes (candidate keys) for each of the tables, as
each referencing table will make use of the same. The IDs you have in
your initial sketch look to be, conceptually, something akin to
"object ids", which are generally contraindicated -- that which
identifies a company is usually of a different domain than that which
identifies a division, and a manager (although a manager and an
employee may be identified by values from the same domain, e.g.,
employeeId).

I'll again reiterate that there are a number of design criteria (some
in conflict with one another) that you'll need to consider. As Bob has
pointed out, the types of queries you will need to run against the
data also come into play.

If this is a "toy" project of yours, I suggest looking over some of
the designs at this website: http://www.datamodel.org/ and seeing if
you can find/morph/apply solutions similar to the domain you're
working with.

If this is for a professional engagement you're involved in, I would
suggest bringing in a data modeling consultant to help you jumpstart
the project -- assuming you don't have the requisite time to educate
yourself more fully on the data modeling discipline.

HTH,
TroyK

On Feb 8, 5:07*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Yes.


IBM
Div-A
Jones
Smith
Div-B
Smith
Microsoft
Div-A
Johnson
Div-B
Jones
Smith

I'm not sure what you mean by foreign key references.

Do you mean this?

Company Table
Company
IBM
Microsoft

Division Table
Company Division
IBM Div-A
IBM Div-B
Microsoft Div-A
Microsoft Div-B

Manager Table
Company Division Manager
IBM Div-A Jones
IBM Div-A Smith
IBM Div-B Smith
Microsoft Div-A Johnson
Microsoft Div-B Jones
Microsoft Div-B Smith

Or this?

Company Table
CompanyKey Company
1 IBM
2 Microsoft

Division Table
DivisionKey Division CompanyKey
1 Div-A 1
2 Div-B 1
3 Div-A 2
4 Div-B 2

Manager Table
ManagerKey Manager DivisionKey
1 Jones 1
2 Smith 1
3 Smith 2
4 Johnson 3
5 Jones 4
6 Smith 4

I've tried both of these and they seem to get awkward with 3 or more
levels. Are you talking about one of these methods?

I'm just using that as an example. My actual project has 4 levels so I
added a 4th level to make it similar.