CODEWITHSUNDEEP
sqlentity-frameworkdatabase-design
RobVious
RobVious

Reputation: 12925

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

I have three entities:

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:

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.

Upvotes: 1

Views: 417

Answers (2)

Muhammad Sohail
Muhammad Sohail

Reputation: 598

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

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

I'd probably go with a simple data model:

  • User
  • Transaction (without storing the information about reviews here)
  • Review (a review must either be for a particular user or a transaction)

You could differentiate a review by it's type (a dictionary table) to know which review is standalone and which comes with a transaction.

You could go two ways about it:

  • have two columns for storing ids of Transaction and User entity and keeping nulls depending on the type
  • or having one column that would identify the id of an entity thanks to the type of a review

I don't see a need for TransactionReview entity, since one review can only be attached to 0..1 transaction. Since transactions can have 0..2 reviews this becomes a one-to-many relationship with optional zero elements.

I agree that it might complicate the logic of retrieval (having to remember that) but I find it very handy when dealing with data modelled like that.

Upvotes: 1

Related Questions