1. Registered User
Join Date
Feb 2003
Posts
3

## 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

2. Moderator.
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
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. Registered User
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.
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:

------------ -------------- ------------------
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?

4. Moderator.
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. Registered User
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.