Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2008
    Posts
    29

    Intra-table relationships

    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 :
    Code:
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rapht
    Is it acceptable as it is ?
    yes, it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  4. #4
    Join Date
    Jun 2008
    Posts
    29
    Quote Originally Posted by r937
    yes, it is
    Quote 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 !

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes - that is much better.

  7. #7
    Join Date
    Jun 2008
    Posts
    29
    Quote 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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote 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.

    Quote 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2008
    Posts
    29
    Quote 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 ?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    How would that handle updates to EntityType? I think every attempt would fail until you removed the constraint.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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).

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    But if you change a parenttype in a record, will that change cascade to the parenttype in all the related records?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Say you have this data:
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •