What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]

by

Generally, there are two strategies to handle a situation like this:

  1. Use Exclusive FKs

Essentially, each of the possible parent tables will have its own, separate foreign key in the child table, and there is a CHECK enforcing exactly one of them is non-NULL. Since FKs are only enforced on non-NULL fields (meaning, when a FK is set to NULL there is no database-level validation), only one of the FKs will be enforced.

For example:

enter image description here

(relationship between user and group omitted)

CHECK (
    (group_id IS NOT NULL AND user_id IS NULL)
    OR (group_id IS NULL AND user_id IS NOT NULL)
)
  1. Use Inheritance

Inherit user and group from a common supertype and then connect the setting to the supertype:

enter image description here

For more information on inheritance (aka. category, subclassing, subtype, generalization hierarchy etc.), take a look at “Subtype Relationships” chapter of ERwin Methods Guide. Unfortunately, modern DBMSes don’t natively support inheritance – for some ideas about physically implementing it, take a look at this post.

This is a heavy-duty solution probably not justified for just two tables (groups and users), but can be quite “scalable” for many tables.

More Related Contents:

  1. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  2. What’s wrong with foreign keys?
  3. NoSql vs Relational database
  4. Relational table naming convention [closed]
  5. What are the design criteria for primary keys?
  6. When I should use one to one relationship?
  7. How should international geographical addresses be stored in a relational database?
  8. Still Confused About Identifying vs. Non-Identifying Relationships
  9. Minimum no of tables that exists after decomposing relation R into 1NF?
  10. NULL permitted in Primary Key – why and in which DBMS?
  11. uncertainty in developing a database model
  12. Database development mistakes made by application developers [closed]
  13. Surrogate vs. natural/business keys [closed]
  14. What are database normal forms and can you give examples? [closed]
  15. How to create multiple one to one’s
  16. Sql – Indirect Foreign Key
  17. Multiple schemas versus enormous tables [closed]
  18. First name, middle name, last name. Why not Full Name?
  19. Oracle (ORA-02270) : no matching unique or primary key for this column-list error
  20. Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)
  21. Techniques for database inheritance?
  22. Modeling Product Variants
  23. Storing time-series data, relational or non?
  24. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  25. Proper database model for a user feedback system (an interesting case)
  26. Ideas on database design for capturing audit trails [closed]
  27. Versioning Database Persisted Objects, How would you? [closed]
  28. Best design for a changelog / auditing database table? [closed]
  29. Derived account balance vs stored account balance for a simple bank account?
  30. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

Leave a Comment