Table Structure.........

[sajidaziz]

hello this is my 1st posting.......
please help me .........


I have 3 tables.....

Table : Customers

CustomerID
CustomerType (i.e. 1st Allottee, 2nd Allottee etc.)
Name
Address
C/o
...

Table: Apartments

File# (every apartment has unique file#)
Category
App#
Level
Area
Price
Discounted (yes/no)
DiscountRate %
...

Table: Receipts

Rec#
RecType (Down Payment, 1st Installment, 2nd Installment...)
Ref
Date
Chq#
Amount
...

one customer can have more than 1 apartments, and one apartment can be transfered to another customer (file# same but customerID changed)
one customer is paying different types of payments against his apartment.

please help me to manage tables and relationships.

 

[Newman]

I am not sure, since I looked at it very fast, but I think that the link between customers and receipts shouldn't be there. Instead I would try to add a second link between the tables appartements and receipts using CustomerID.

I also noted that the customers table is linked with mediator's name. It should be link with the primary key (MediatorID). Same thing with the ReceiptType.

Finaly, you should refrain from using «Name» and «Date» as field's name, since these are reserved words. Use CustomerName and ReceiptDate instead.

 

[sajidaziz]

thanx Mr.Newman....
but should i do when same apartment transfered to another customer? it means that
same apartment has two entries....

plz look at these relationships deeply........i will be thankful to u....

 

[ejstefl]

So one customer can have more than one apartment... can one apartment have more than one customer? If so, you need a many-to-many relationship, which means you need a table as follows:

tblApartmentCustomer

ApartmentCustomerID - pk
ApartmentID - fk
CustomerID - fk