0

We have a project running where i need to do some ETL work into a new database that created was by a different company. While going through the database diagram given to me by the developers i saw that there was a circular reference on 4 of the tables:

I can't upload the diagram but here is a general structure of the tables:

CASE

  • ID (PK) CaseStatusInfo (FK)
  • SignOffPlanning (FK)
  • SignOffReportReview (FK)
  • SignOffQuarterlyReview (FK)

CaseStatusInfo

  • ID (PK)
  • CaseId (FK)

SignOffPlanning

  • ID (PK)
  • CaseId (FK)

SignOffReportReview

  • ID (PK)
  • CaseId (FK)

SignOffQuarterlyReview

  • ID (PK)
  • CaseId (FK)

How these Info tables linked to the Case table will be used is that it will be storing historic statuses of a case by having the primary key stored within each Info table. This really does make logical sense but i feel that it might have been better to normalize these tables even further to keep the historic data in those normalized tables.

My question is: What problems could arise from having this type of database structure instead of further normalizing the tables?

Henry
  • 1
  • 1

1 Answers1

0

Sounds like you really want 1-1 relationships to these tables. I am assuming that each of the tables might have heaps of other properties so I could understand why you wouldnt want to combine them into a giant CASE table. In that case... you might want to combine the PK/FK to force a 1-1 relationship...

CASE

  • ID (PK)
  • CaseStatusInfo (FK)
  • SignOffPlanning (FK)
  • SignOffReportReview (FK)
  • SignOffQuarterlyReview (FK)

CaseStatusInfo

  • CaseId (PK, FK)

SignOffPlanning

  • CaseId (PK, FK)

SignOffReportReview

  • CaseId (PK, FK)

SignOffQuarterlyReview

  • CaseId (PK, FK)
Felix
  • 1,205
  • 9
  • 17
  • Hi Felix. I do want a 1-1 relationship but avoid a circular reference. As they've explained to me this structure helps to keep historic data (which i feel is a lazy way to do it). Besides my dislike of this structure i'm not sure what problems there would be using this design besides the fact that of the circular reference. – Henry Jan 29 '13 at 10:14
  • If you look closely at my answer the cirular references are gone? Of course CaseStatusInfo, SignOffPlanning, SignOffReportReview and SignOffQuartelyReview cannot exist if the case is removed. However this would be the case in your table too (unless the CaseId is nullable in those tables). I would assume however that you would be keeping the case data for 'historic data' as well... – Felix Jan 29 '13 at 10:19
  • I should point out that when you insert one of these tables CaseId = Case.Id of the associated case record. – Felix Jan 29 '13 at 10:20