SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Just need critique on my database table structure

    Hi there

    I'm developing a small property management application,
    to record rent payments made on leases (associated to a property)

    Im just a bit uncertain on my table design as to whether it's the right way to do things and was hoping to get some feedback.

    This is what I have at the moment..


    Transaction
    id
    lease_id (fk)
    lease_party_id (fk) <----- Should this be just Person_id??
    date
    description
    amount

    Lease
    id
    property_id (fk)
    lease_party_id (fk) <---- Should this be just Person_id? or is this good..
    start
    end
    rent_amount

    Lease_Party (stores Lease - Tenant/Landlord association)
    id
    lease_id (fk)
    person_id (fk) ~ tenant or landlord
    type_id (fk) ~ reference a type table to state whether tenant or landlord

    Person (stores Tenant/Landlord details)
    id
    first_name
    last_name
    phone
    email


    Any advice / guidance appreciated!
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    how many people can take out a lease on a property?

    also, i think you're missing a table for the properties

    finally, keep in mind that not every table should have an auto_increment column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    re: autoincrements yeah I can understand, the reasoning for that is largely to simplify the process of deleting/updating records from the PHP Application

    The property table exists I just left it out to not clutter the post as I'm really just seeking approval of the above setup/connections.

    Typically there are 2-3 people on a lease. Usually a couple, also regularly 3 friends. Hence why I made lease_party.
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by azuranz View Post
    re: autoincrements yeah I can understand, the reasoning for that is largely to simplify the process of deleting/updating records from the PHP Application
    this is actually a myth

    Quote Originally Posted by azuranz View Post
    Typically there are 2-3 people on a lease. Usually a couple, also regularly 3 friends. Hence why I made lease_party.
    well, something's not quite right

    you have a foreign key from lease to lease_party, and a foreign key from lease_party to lease

    that's not going to work
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh no no sorry my mistake! lol

    As i was writing out the original post i actually changed the table structure lol..

    Here is what it should have been.

    Lease
    id
    property_id (fk)
    start
    end
    rent_amount

    Lease_Party (stores Lease - Tenant/Landlord association)
    id
    lease_id (fk)
    person_id (fk) ~ tenant or landlord
    type_id (fk) ~ reference a type table to state whether tenant or landlord
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night


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
  •