Defining multiple foreign keys in one table to many tables

by

You could do this:

 post:
  * post_id (PK)
  * title
  * body

 photo:
  * photo_id (PK)
  * filepath

 comment:
  * comment_id (PK)
  * body

 comment_to_post
  * comment_id (PK) -> FK to comment.comment_id
  * post_id (PK) -> FK to post.post_id

 comment_to_photo
  * comment_id (PK) -> FK to comment.comment_id
  * photo_id (PK) -> FK to photo.photo_id

There’s still the possibility of having a comment that belongs to two different items. If you think that would be an issue I can try to improve the design.

More Related Contents:

  1. Sql – Indirect Foreign Key
  2. How to design a product table for many kinds of product where each product has many parameters
  3. Database design – articles, blog posts, photos, stories
  4. What’s wrong with foreign keys?
  5. 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]
  6. How to version control a record in a database [closed]
  7. designing database to hold different metadata information
  8. MySQL – Conditional Foreign Key Constraints
  9. When/Why to use Cascading in SQL Server?
  10. What are the design criteria for primary keys?
  11. Are nulls in a relational database okay? [closed]
  12. CONSTRAINT to check values from a remotely related table (via join etc.)
  13. Database schema which can support specialized properties
  14. Nullable Foreign Key bad practice?
  15. How do I design a database to store properties, selecting attributes by synonyms
  16. Composite primary key or not?
  17. Hash Collision – what are the chances? [closed]
  18. How do you determine how far to normalize a database?
  19. Can we create multicolumn unique indexes on MS access databases?
  20. Should I have a dedicated primary key field?
  21. 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]
  22. What is the best database schema to support values that are only appropriate to specific rows?
  23. Database design for audit logging [closed]
  24. Should I use a single or multiple database setup for a multi-client application? [closed]
  25. Database Design Best Practices [closed]
  26. A column as primary key or two foreign keys as primary key
  27. How to Store Historical Data [closed]
  28. Is there a severe performance hit for using Foreign Keys in SQL Server?
  29. Bill of materials database model
  30. MySQL Relationships

Leave a Comment