Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Unhappy DataBase Design Problem...

    Hi all,
    I've a app that must complay this 2 situacions:
    1.More than a client can rent the same house at the same time (funny isnt)
    2.Billing must be unique for each house (only 1 for each house).
    So... my main tables are HOUSES, CLIENTS and BILLS, but i cant do this without the creation of a middle table with a IDCLIENTS, IDClient1, IDClient2, IDClient3 and IDHouse. I nedd IDCLIENTS to do the relation between IDBILL on the BILLS table (since i can only have 1 bill for each house, independing of the number of clients on it).
    My problem is and if more of 3 clients are living in the house.... then i must have in my middle table many IDClient(1,2,3,...,n)... not good. Any ideias? my ICQ is 197490130 and my email is as1095310@sapo.pt
    Tks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: DataBase Design Problem...

    Originally posted by jpfo
    Hi all,
    I've a app that must complay this 2 situacions:
    1.More than a client can rent the same house at the same time (funny isnt)
    2.Billing must be unique for each house (only 1 for each house).
    So... my main tables are HOUSES, CLIENTS and BILLS, but i cant do this without the creation of a middle table with a IDCLIENTS, IDClient1, IDClient2, IDClient3 and IDHouse. I nedd IDCLIENTS to do the relation between IDBILL on the BILLS table (since i can only have 1 bill for each house, independing of the number of clients on it).
    My problem is and if more of 3 clients are living in the house.... then i must have in my middle table many IDClient(1,2,3,...,n)... not good. Any ideias? my ICQ is 197490130 and my email is as1095310@sapo.pt
    Tks in advance
    This is a classic case of needing to normalise the data to 1st Normal Form. You need a table with just ONE client_id and ONE house_id, but with MANY rows in it:

    Client_House( house_id, client_id );

    House_ID Client_ID
    ----------- ----------
    123, 101
    123, 102
    123, 199

    This table can handle a house with 1,2,3 or 50 clients no problem.

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    Re: DataBase Design Problem...

    Originally posted by andrewst
    This is a classic case of needing to normalise the data to 1st Normal Form. You need a table with just ONE client_id and ONE house_id, but with MANY rows in it:

    Client_House( house_id, client_id );

    House_ID Client_ID
    ----------- ----------
    123, 101
    123, 102
    123, 199

    This table can handle a house with 1,2,3 or 50 clients no problem.
    I thought about that...
    But i also need a contrat table for each house rented ( 1 unique for all clients on the house ). I'm thinking on adding 1 field to your suggested table:

    Contrat_ID House_ID Client_ID
    ------------ ----------- ----------
    AA112, 123, 101
    AA112, 123, 199
    AA113, 124, 102

    and then make the other table of CONTRATS:

    Contrat_ID DateEntry PayedInAdvance
    ------------ -------------- ------------------
    AA112 01-01-2003 6
    AA113 01-02-2003 1

    Then i just relate the ContratID to the BILLS table one to many...
    I dont like this design... but seems like the only i can have...
    Anyone desagree?
    Tks in advance.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: DataBase Design Problem...

    Looks like the Client_House table is not normalised now: if a contract always relates to a specific house then the tables should be:

    CONTRACTS(contract_id, house_id, date_entry, ... )
    CONTRACT_CLIENT(contract_id,client_id)

  5. #5
    Join Date
    Feb 2003
    Posts
    3

    Re: DataBase Design Problem...

    Originally posted by andrewst
    Looks like the Client_House table is not normalised now: if a contract always relates to a specific house then the tables should be:

    CONTRACTS(contract_id, house_id, date_entry, ... )
    CONTRACT_CLIENT(contract_id,client_id)
    I can agree with you.

    Tks for your help

Posting Permissions

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