Say I have a table of hierarchical data, lets say it is a table representing the management hierarchy of a company. So, it would look something like:

X_ID (PK) Emp_ID Manager_ID

What would you call this table? If you are supposed to name the table after the singular of what a row represents, would a good name be something like ManagmentNode? Or would ManagementBranch be better because the row actually represents an entire branch of management information?

Why wouldn't you just make manager ID a foreign key in your employees (or persons) table? Each employee only has one manager, right? Not sure why you have a separate table here with its own PK.

JimFoye wrote:

Why wouldn't you just make manager ID a foreign key in your employees (or persons) table? Each employee only has one manager, right? Not sure why you have a separate table here with its own PK.

Ahhh, yeah. I was trying to come up with a simple example but the one I gave doesn't really explain the situation fully. Here's the real case...We are tracking contracts in a hierarchical table. Any particular row in the table represents just one level of the contract...so for example, one row might represent an order contract, while its children represent Base Year 1, Option Year 1, Option Year 2, etc...Rolling up to the "order" level represents the entire order. The order can not be accurately represented as just that one row in the table, it only has context when its child rows are attached.

I can see calling any one entity with its children a "Contract" but I cannot see calling the table Contract, because each row does not in itself represent a contract....any ideas?

Wow, that is completely different, I mean we went from "management hierarchy of a company" to something like "contract terms"!

Does each term or period (base, option 1, etc.) have an employee and a manager associated with it? Or were you just making up those fields in your example? Maybe it would be better to just post the real table structure(s)

I would forget about the hierarchical nature of your data here and just come up with a name that accurately (more or less, anyway) represents what each row corresponds to in the real world. It sounds like "contract term" or "contract period" might be getting close. IMHO, anyway.

mikeg22 wrote:

We are tracking contracts in a hierarchical table. Any particular row in the table represents just one level of the contract...so for example, one row might represent an order contract, while its children represent Base Year 1, Option Year 1, Option Year 2, etc...Rolling up to the "order" level represents the entire order. The order can not be accurately represented as just that one row in the table, it only has context when its child rows are attached.

To me, it sounds like you have two tables...

Contract ContractDetails (with a type discriminator)

FROM contract c JOIN contractdetails d on c.contract_id = d.contract_id

What am I missing?

BOb

PilotBob wrote:

mikeg22 wrote:

We are tracking contracts in a hierarchical table. Any particular row in the table represents just one level of the contract...so for example, one row might represent an order contract, while its children represent Base Year 1, Option Year 1, Option Year 2, etc...Rolling up to the "order" level represents the entire order. The order can not be accurately represented as just that one row in the table, it only has context when its child rows are attached.

To me, it sounds like you have two tables...

Contract ContractDetails (with a type discriminator)

FROM contract c JOIN contractdetails d on c.contract_id = d.contract_id

What am I missing?

BOb

Well, its a strange situation...The children of a contract can be contracts themselves. For example, a BPA (Blanket Purchase Agreement) can have orders (another type of contract) under them. This is what makes this such a strange situation, the contracts are themselves unique things, but are also defined by what their children...

mikeg22 wrote:

PilotBob wrote:

mikeg22 wrote:

We are tracking contracts in a hierarchical table. Any particular row in the table represents just one level of the contract...so for example, one row might represent an order contract, while its children represent Base Year 1, Option Year 1, Option Year 2, etc...Rolling up to the "order" level represents the entire order. The order can not be accurately represented as just that one row in the table, it only has context when its child rows are attached.

To me, it sounds like you have two tables...

Contract ContractDetails (with a type discriminator)

FROM contract c JOIN contractdetails d on c.contract_id = d.contract_id

What am I missing?

BOb

Well, its a strange situation...The children of a contract can be contracts themselves. For example, a BPA (Blanket Purchase Agreement) can have orders (another type of contract) under them. This is what makes this such a strange situation, the contracts are themselves unique things, but are also defined by what their children...

So, wouldn't this be a self relation then? Contract table could perhaps have a ContractParentId field, and be null if it were the top level Contract record?

BOb