[rapht]

Hi all,

I'm currently designing a database and am facing a question I couldn't relate to the traditional normal forms : what to do with intra-table relationships.
Let me explain.

I need to store items called "entities" which all share the same set of information : basically, a name and a type.

The thing is, these entities are related to each other by a parent-child relationship :
- I have one entity of type 1 that has no parent
- All entities of type 2 are children of the only entity of type 1
- All entities of type 3 have one entity of type 2 as parent
- All entities of type 4 have one entity of type 3 as parent
and so on.

The number of "types" is currently fixed but may change in the future, so it's best to consider it unknown.

The design I came up with is simply :

TABLE Entities (entity_id, entity_type, entity_parent)

where entity_parent is meant to hold a reference to an entity_id.

Is it acceptable as it is ? It sounds to me as far from best practices as one could be, but I could not think of something better - other than indeed creating as many tables as there are types right now and programmatically adding tables when new types are created in the future.

Thanks for any comments on that.

[r937]

Originally Posted by rapht

Is it acceptable as it is ?

yes, it is

[pootle flump]

I'd say so but if you must enforce this type of thing relationally then perhaps:
TABLE Entities (entity_id, entity_type, entity_parent, entity_parent_type)
(i.e. two columns in the relationship) and a check constraint to enforce your rules.

[rapht]

Originally Posted by r937

yes, it is

Originally Posted by pootle flump

I'd say so but if you must enforce this type of thing relationally then perhaps:
TABLE Entities (entity_id, entity_type, entity_parent, entity_parent_type)
(i.e. two columns in the relationship) and a check constraint to enforce your rules

Thanks for your help !

[blindman]

Scrap the "Types" concept.
You can determine Type dynamically by the level of the record in the tree, and this way you will not need to "retype" branches if the structure of the tree changes.

[pootle flump]

Oh yes - that is much better.

[rapht]

Originally Posted by blindman

Scrap the "Types" concept.
You can determine Type dynamically by the level of the record in the tree, and this way you will not need to "retype" branches if the structure of the tree changes.

True. But :
- actually the tree should not change except for adding more depht.
- what's more important, I want to be able to easily get all the elements of one particular type/level in the tree. In your option, I would first need to recompile the tree to the given depht to get such a set.
Since such a query is going to be required frequently, I think I prefer the "typed" option, even though it's a bit less flexible.

Thanks anyway for the suggestion, will remember it for future use

[blindman]

Originally Posted by rapht

- actually the tree should not change except for adding more depht.

Should not and will not are miles apart. 95% of coding deals with issues that occur 5% of the time.

Originally Posted by rapht

- what's more important, I want to be able to easily get all the elements of one particular type/level in the tree. In your option, I would first need to recompile the tree to the given depht to get such a set.

I'm not even sure what you mean by "recompile", so I doubt that whatever this action is would be necessary.

[rapht]

Originally Posted by blindman

I'm not even sure what you mean by "recompile", so I doubt that whatever this action is would be necessary.

Yeah that wasn't the right word, I meant "recalculate".
And as I said, if the tree "should not change", even if it does that will be kind of exceptionnal. That's why I think optimizing for reading/querying the tree by storing "types" is more relevant than optimizing for tree updating which will seldom happens.
But I wonder what's your take on that ?

[blindman]

I already told you my take on that.
You have a business rule defining the labels for different levels of the tree. Rather than calculating this dynamically, you are going to store it in the tree.
What are you going to add to the database to ensure that the labels are correct? What constraints or triggers will you write to enforce the data integrity?
Any code you add will have its own overhead, in CPUs and administrative maintenance. And if you do not enforce these constraints by some means, then they are not constraints are they? And sooner or later they WILL be violated, leaving you with corrupted data and incorrect output.
How many records do you anticipate having in this table?

[pootle flump]

Originally Posted by blindman

What constraints or triggers will you write to enforce the data integrity?
Any code you add will have its own overhead, in CPUs and administrative maintenance.

Post #3 - declarative integrity - overhead nominal - no triggers.

[blindman]

How would that handle updates to EntityType? I think every attempt would fail until you removed the constraint.

[pootle flump]

The entity type relationship between parent and child is relative so there is no need for literals in the constraint (other than null parent if entity type = 1).

[blindman]

But if you change a parenttype in a record, will that change cascade to the parenttype in all the related records?

[blindman]

Say you have this data:

ID	Type	ParentID	ParentType
1	A
2	B	1		A
3	C	2		B

All well and good, because you have a business rule that 1st level entities are type "A", second level are type "B" and third level are type "C", etc, and this data satisfies the constraints you outlined above.
Now, you promot record 2 to the top level, changing its type from "B" to "A". Maybe you can get ParentType of record 3 to update from "B" to "A" by cascading the change, but you are still going to need a trigger or something to update record 3's type from "C" to "B".
So you may get this to work, but I do not see it as a simple solution.