RobVious RobVious - 3 months ago 19
SQL Question

Modeling a 1:1 optional-on-both-sides relationship

I have three entities:


  • User
    - can have many
    Reviews
    and can have many
    Transactions

  • Transaction
    - must have a
    FromUser
    and
    ToUser
    , can have
    FromUserReview
    or
    ToUserReview

  • Review
    - Can have
    Transaction
    , must have
    FromUser
    and
    ToUser



The idea is that users may write reviews on one another, may send payments to each other. A user can only write one non-transactional review for another user - otherwise, reviews must be attached to transactions.

Essentially, this becomes a 1:1 optional-on-both-sides relationship between Transactions and Reviews. I was thinking about modeling this with a join table that contains:


  • ReviewId

  • TransactionId



And calling it TransactionReview. This seems to eliminate model/code duplication, but complicates my business logic.

Another alternative I see is creating two entities:
UserReview
and
TransactionReview
- which will simplify logic but will force me into code repetition and having two tables for what should be a single entity.

What is the right way to go about this? I am using Entity Framework code-first, in case it matters.

Answer

I have prepare some code, please check and try.

public class User
{
    // properties
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual Address Address { get; set; }
    public virtual ICollection<UserReview> UserReviewsFromMe { get; set; }
    public virtual ICollection<UserReview> UserReviewsToUsers { get; set; }
    public virtual ICollection<TransactionReview> TransactionReviews { get; set; }
}

public class Review
{
    public int Id { get; set; }
    public string Content { get; set; }
    public string EntityName { get; set; }
    public int EntityId { get; set; }

    public virtual TransactionReview TransactionReview { get; set; }
    public virtual UserReview UserReview { get; set; }
}

public class Transaction
{
    public int Id { get; set; }
    public string Note { get; set; }
    public DateTime CreatedOnUtc { get; set; }

    public virtual ICollection<TransactionReview> TransactionReviews { get; set; }
}

public class UserConfiguration : EntityTypeConfiguration<User>
{
    public UserConfiguration()
    {
        ToTable("User");
        HasKey(p => p.Id);

    }
}

public class ReviewConfiguration : EntityTypeConfiguration<Review>
{
    public ReviewConfiguration()
    {
        ToTable("Review");
        HasKey(x => new { x.Id });
    }
}

public class TransactionConfiguration : EntityTypeConfiguration<Transaction>
{
    public TransactionConfiguration()
    {
        ToTable("Transaction");
        HasKey(x => new { x.Id });
    }
}

public class UserReview
{
    public int Id { get; set; }
    public int FromUserId { get; set; }
    public int ToUserId { get; set; }

    public virtual User FromUser { get; set; }
    public virtual Review Review { get; set; }
    public virtual User ToUser { get; set; }
}

public class TransactionReview
{
    public int Id { get; set; }
    public int TransactionId { get; set; }
    public int UserId { get; set; }

    public virtual Transaction Transaction { get; set; }
    public virtual Review Review { get; set; }
    public virtual User User { get; set; }
}

public class UserReviewConfiguration : EntityTypeConfiguration<UserReview>
{
    public UserReviewConfiguration()
    {
        ToTable("UserReview");
        HasKey(x => new { x.Id });
        Property(a => a.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);

        this.HasRequired(ur => ur.FromUser)
            .WithMany(u => u.UserReviewsFromMe)
            .HasForeignKey(ur => ur.FromUserId)
            .WillCascadeOnDelete(false);

        this.HasRequired(ur => ur.Review)
            .WithOptional(r => r.UserReview);

        this.HasRequired(ur => ur.ToUser)
            .WithMany(u => u.UserReviewsToUsers)
            .HasForeignKey(ur => ur.ToUserId)
            .WillCascadeOnDelete(false);
    }
}

In the above UserReviewConfiguration class, I mapped like this: A user can have zero or more UserReview's posted, a UserReview is posted by one user only and can be for one user only, and is mapped with one review only, making the Review and User entities independent as well if someone needs.

public class TransactionReviewConfiguration : EntityTypeConfiguration<TransactionReview>
{
    public TransactionReviewConfiguration()
    {
        ToTable("TransactionReview");
        HasKey(x => new { x.Id });
        Property(a => a.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);

        this.HasRequired(tr => tr.Transaction)
            .WithMany(t => t.TransactionReviews)
            .HasForeignKey(tr => tr.TransactionId);

        this.HasRequired(tr => tr.Review)
            .WithOptional(r => r.TransactionReview);

        this.HasRequired(tr => tr.User)
            .WithMany(u => u.TransactionReviews)
            .HasForeignKey(tr => tr.UserId);
    }
}

In the above TransactionReviewConfiguration class, I mapped like this: A user can have zero or more TransactionReview's posted, a TransactionReview is posted by one user only and can be for one Transaction only, and is mapped with one review only, making the User, Review and Transaction entities independent as well if someone needs.

Hope this helps...

Comments