I have several tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
houseID
ownerID
ownershipDate //date they bought the house
saleDate //date they sold the house

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...


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:

House 1009283
Address

House History
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.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

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:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...


Options 2 is more logical to me if I want to tack the violation history. Just checking my logic - your thoughts?

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)