A column as primary key or two foreign keys as primary key

by

My question is about EReportReq table. I’m not sure if I need a column as primary key (EReportReqId) or I can use eReportId and requirementId as primary key.

You can use either of them – none of them is absolutely “better”. Just be careful that if you decide to use the first approach, also create a UNIQUE constraint on {eReportId, requirementId}.

The fist approach (with non-identifying relationship and surrogate key) leads to:

  • “leaner” foreign keys in child tables (which is EReportReqImg in this case) – as you already noted,
  • the cascading ON UPDATE doesn’t propagate to children (so if you update EReport.eReportId, only EReportReq.eReportId is cascade-updated, but not EReportReqImg.eReportId)
  • and can be more friendly to ORMs.

On the other hand, the second approach (with identifying relationship and natural keys):

  • has potentially less need for JOINs (e.g. you don’t need to EReportReqImg JOIN EReportReq just to find-out requirementId – you have it directly in EReportReqImg.requirementId),
  • is better suited for clustered tables (e.g. EReportReq rows with the same eReportId will be stored physically “close”, which may significantly benefit some queries)
  • avoids additional index on the surrogate key.

Since you have a small number of child tables, “fat” FKs don’t matter much and since we are dealing with IDs, they are unlikely to change and cascading ON UPDATE is unlikely to be a problem. So, my instinct is to go with the second approach, but you might have some other considerations that might tip your decision in a different direction…

More Related Contents:

  1. How to design a product table for many kinds of product where each product has many parameters
  2. What is Normalisation (or Normalization)? [closed]
  3. Database design – articles, blog posts, photos, stories
  4. Schema for a multilanguage database
  5. Calendar Recurring/Repeating Events – Best Storage Method
  6. How to version control a record in a database [closed]
  7. designing database to hold different metadata information
  8. Same data from different entities in Database – Best Practice – Phone numbers example
  9. Sql – Indirect Foreign Key
  10. Defining multiple foreign keys in one table to many tables
  11. What are the lengths of Location Coordinates, latitude and longitude? [closed]
  12. Are nulls in a relational database okay? [closed]
  13. How should I implement this schema in MongoDB?
  14. Database schema which can support specialized properties
  15. How do you deal with polymorphism in a database?
  16. Nullable Foreign Key bad practice?
  17. How do I design a database to store properties, selecting attributes by synonyms
  18. Composite primary key or not?
  19. Hash Collision – what are the chances? [closed]
  20. How do you determine how far to normalize a database?
  21. Can we create multicolumn unique indexes on MS access databases?
  22. Should I have a dedicated primary key field?
  23. What is the best database schema to support values that are only appropriate to specific rows?
  24. Database design for audit logging [closed]
  25. Should I use a single or multiple database setup for a multi-client application? [closed]
  26. Database Design Best Practices [closed]
  27. How to Store Historical Data [closed]
  28. Bill of materials database model
  29. What are design patterns to support custom fields in an application?
  30. mongodb schema design for blogs

Leave a Comment