CODEWITHSUNDEEP
many-to-manyrelational-databaseentity-relationshipforeign-key-relationship
alex9311
alex9311

Reputation: 1420

Relational Database design - entity associated with relationship

I have a question about best practices for relational database design. Here is an example that shows the issue I'm not sure about.

You have software packages with names, which hold several pieces of software. A user can own any amount of software package but only comment on the individual pieces of software in the packages he owns.

My entities are: User, Software Package, Software Piece, Comment. User -> Software Package is N to N. Software Package -> Software Piece is N to N.

Is the best you can do in a relational database make a Comment hold a foreign key for both a User and a Software piece. I don't really see a way to ensure through the DB schema that Comments can only exist between Users and a Software Piece from one of the Software Packages they own.

Is there a way to associate the Comment entity with a relationship that spans two entities?

Upvotes: 0

Views: 82

Answers (2)

Joel Brown
Joel Brown

Reputation: 14418

Your business rule is that a user can only comment on a piece of software that is contained in a package that they own. What you need to do is apply this rule in the primary key(1) of your COMMENT table.

Consider this ERD:

ERD

Note how the primary key of a COMMENT is the combination of its foreign keys to the owned package and the package content. Since both of these primary keys include the package_id(2) you have a declarative referential constraint that ensures that the user can only comment on a piece of software that is contained in a package that they own.

Notes:

(1) If you are averse to composite primary keys, you can have a candidate key (unique index) which achieves the same result.

(2) If you are using an ORM or something that doesn't allow you to share one package_id column between two foreign key relationships you can have two package_id columns in the COMMENT table and add a constraint that forces the one to be equal to the other.

Upvotes: 1

SylvainL
SylvainL

Reputation: 3938

Not sure how you can do that with an Entity but possibly that you can do it if you create/use a composite foreign key with (UserId, SoftwarePieceId) pointing toward these same two fields on the table expressing the relationship N to N for Users and Software Piece.

However, I'm not sure if building this business rule into your schema will really be a good idea. The checking of most of your business rules should be done a the client tier; not at the database tier; otherwise you might end up with an over-complicated schema.

Upvotes: 1

Related Questions