Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpfo
Starting Member
7 Posts |
 Posted - 2003-02-01 : 19:15:29
|
| 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.ptTks in advance |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-01 : 20:31:52
|
| No, you don't need the middle table, it would be redundant. Assuming that one client will only be in one house, simply add a HouseID column to the Clients table:CREATE TABLE Clients (ClientID int NOT NULL,HouseID int NOT NULL REFERENCES Houses (HouseID),...) --add other columns here, like name, etc.This design won't affect the Bills table and it meets your two requirements. |
 |
|
|
jpfo
Starting Member
7 Posts |
Posted - 2003-02-02 : 07:06:31
|
| Tks robvolk, but in fact 1 client can be in many houses, so your suggestion cant applay... |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-02-02 : 07:31:54
|
You will need a minimum of 4 tables...Create Table Houses (HouseID INT NOT NULL Primary Key)Create Table Clients (ClientID INT NOT NULL Primary Key)Create Table HouseClients (HouseID INT NOT NULL, ClientID INT NOT NULL Constraint PK_HouseClients Primary Key (HouseID, CientID))Create Table Bills (BillID INT Primary Key, HouseID INT) I'll leave the RI to you.....DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
|
|
Database Design Problem...