Monday, July 7, 2014

SOLID data relationships

One approach I like to take when designing a database schema is to focus on the relationships before adding columns that are not involved in the relationships (in a relational database that is).

The structure and integrity are the most important component of a good database design. One approach is to model the data structures first. If you are working in an object oriented language you could build your objects first with minimal properties. By doing this and thinking about SOLID development principles, we can keep our database design in alignment with our code development practices.

in C#:
public class Person
{
public int PersonID {get;} {set;}
public virtual ICollection<Task> Tasks {get;} {set;}
}
or even using "class/table diagrams":

Person
-PersonID int PK
-Tasks Task[]

Task
-TaskID int PK
-Assignees Person[]

in this model we can already see that a person can have many tasks and a task can be assigned to many people. We can also see that the task class will not map directly to relational tables. There is only one task, this task can be assigned to multiple people. Therefore we will need a linking table to represent the many-to-many relationship between people and tasks. Likely we would want to collect more data about that task assignment, so probably the intermediary table should have it's own model.

TaskAssignment
-TaskAssignmentID int PK
-TaskID int FK
-PersonID int FK
-details...

Person
-PersonID int PK
-TaskAssignments TaskAssignment[]

Tasks
-TaskID int PK
-TaskAssignments TaskAssignment[]

now we can track each assignment and later add props like CompletedDate, hook up another link table to comments so you can save multiple comments with metadata related to each one.

While many-to-many relationships with a single comments table would work, there is a slight issue with data-integrity. A single comment should relate to a single TaskAssignment, and there should be zero-to-many Comments for a TaskAssignment. Or anything we'd like to tie comments to. Note that we might be tempted create a combo PK on TaskAssignments using TaskID and PersonID and do away with TaskAssignmentID, but what if it's a long running task and a person can be assigned to it again?

One way is to add a comments table each time we need comments to relate to another entity. Our query, should we want to view all comments related to some specific root would be really complicated. Another approach would be to have a single Comments entity with a CommentType attribute and whenever we need to add comments to some other table we can add an entity that relates to a specific comment and the target table.

Comments
-CommentID int PK
-CommentType CommentType FK
-details...

PersonComments
-CommentID int PK, FK
-PersonID int FK

TaskComments
-CommentID int PK, FK
-TaskID int FK

TaskAssignmentComments
-CommentID...etc

now we don't have to violate Open-Closed principle to add comments, our comments can be typed in code using TPT inheritance (Comments is the abstract base), and we maintain data-integrity.

The CommentType is so that we can view the Comments table in a query and know what type of entity each one relates to without joining all ___Comments tables in the query. We will have to if we want to know which entity each comment relates to. In .NET, EntityFramework helps significantly with this.

Hope anyone who gets this far will have some neurons firing with regards to structuring a database to support SOLID development practices.

9 comments:

  1. Free information shops are sourced from information caught from at least one operational frameworks or outer data suppliers, or from information created locally inside a specific division or geographic zone.Data Analytics Courses

    ReplyDelete
  2. I was reading some of your content on this website and I conceive this internet site is really informative ! Keep on putting up. Data Blending in Tableau

    ReplyDelete
  3. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.
    ai training in mysore

    ReplyDelete
  4. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    ai training in varanasi

    ReplyDelete
  5. This is an excellent post I have seen thanks to sharing it. It is really what I wanted to see hope in future you will continue for sharing such an excellent post. I would like to add a little comment data analytics course in hyderabad

    ReplyDelete
  6. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in hyderabad
    business analytics course
    data science course in hyderabad

    ReplyDelete
  7. Glad to chat your blog, I seem to be forward to more reliable articles and I think we all wish to thank so many good articles, blog to share with us.

    data science course in indore

    ReplyDelete
  8. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  9. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    data science training in guwahati

    ReplyDelete