Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    4

    table design question? linking table with or without separate primary key?

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    4
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •