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 HOUSE's history of violation regardless of owner, Like:
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. For exmaple, if a date falls outside the time between ownership and sale dates, it does not belonog to that owner.
Option 2: (the one I am favoring)
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:
in option 1 you are linking the CODE_VIOLATION_HISTORY to the HOUSE only, so there's no way to tell when the violation occurred, which is poor design given that your database tracks ownership changes
in option 2 you are linking the CODE_VIOLATION_HISTORY to the HOUSE_OWNER_JOIN table, which pegs it to an appropriate date, however, you are using a surrogate key (house_owner_ID) for this purpose, which i feel is not necessary
you could instead just use the natural key of the HOUSE_OWNER_JOIN table (houseID, ownerID, dateOwnershipBegan)
this way you wouldn't need to join those two table to find out whose house it was when the violation occurred, and you could join directly to the OWNER
makes for simpler queries when for example you want to find all violations by a given owner
A typical query for the current owner information in OPTION 1 might look like (not exactly but close enough for discussion purposes):
select v. violation_ID, h.address, o.ownerName
FROM CODE_VIOLATION_HISTORY v, HOUSE h, OWNER o, HOUSE_OWNER_JOIN j
WHERE j.houseID = h.houseID
AND j.ownerID = o.ownerID
AND j.saleDate != 0000-00-00 //default date
AND v.houseID = h.houseID
How would OPTION 2 query be constructed using a multiple-primary key for the linking table HOUSE_OWNER_JOIN?
So, what you're saying si the only difference between option 1 & 2 is whether CODE_VIOLATION_HISTORY links to the house OR CODE_VIOLATION_HISTORY links to multiple primary keys in the HOUSE_OWNER_JOIN, right?
a typical query for the current owner would most likely involve LEFT OUTER JOINs because not every owner of the house will have had violations
the difference in this query between option 1 and 2 is which tables are joined to which tables (it doesn't matter whether the join is on one surrogate key or a composite natural key)
whether you put things like gutters into the HOUSE_OWNER_JOIN table is up to you, and i guess it would depend on whether it helps in the overall purpose of the database (if you can have a violation based on no gutters, then i guess so)