SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table design question? house table, owner table, code violations table - best way?

    Given the tables:

    HOUSE
    house_ID
    address

    OWNER
    owner_ID
    name
    telephone...

    HOUSE_OWNER_JOIN
    ?

    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 current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

    House 1009283
    Address
    Past history (link to the following)

    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.

    ==================================================
    Option 2:
    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
    ...


    Your thoughts?

  2. #2
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would go for option 2, with the addition of the CodeViolationDate in the CODE_VIOLATION_HISTORY table.

    This is because a code violation must have a house and owner, but an owner or house do not necessarily have any violations.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    another question regarding that design:

    If I go with option two but link the CODE_VIOLATION_HISTORY table to the HOUSE_OWNER_JOIN table, should I put all my information regarding that house besides the address in the HOUSE_OWNER_JOIN table? Like paint_color, hasGutters, ... all the things that CAN change from owner to owner?

    Thanks!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    IF tracking the house properties (like number of bedrooms or gutters) is important, it makes sense to put them in the HOUSE_OWNER_JOIN, right? Because otherwise I could not track them in HOUSE since there is only a single record for one house.

    Does that make sense?

    Thanks again!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, that makes sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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