table design question? house table, owner table, code violations table - best way?
Given the tables:
My goal is to be able to track code violations of the house PER owner.
For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:
Past history (link to the following)
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy
For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:
then I could look up all code violations by date and associate them with their rightful owner.
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID: