Sample database Condominium / Real Estate / Property management

[Daniel2000]

June 7, you are suggesting only 1 table to store both house and tenants info? I was trying to keep them separately but the link is a problem as the unique key is the estate and house number from house. that is why I have the after update procedure that takes the value of estate and house id and store them as one field in the address field in house table.

me.address = me.estate & " " me.houseid.

for the tenant table, i also have the address field which looks up from the info table address field value.

[June7]

I am suggesting a junction table. Note that the suggested table has the UnitID and TenantID but no detail info about either. The junction table associates records from tblTenants and tblUnits. That is 3 tables. This is how many-to-many relationships are handled.

[Daniel2000]

June7. Thanks. the challenge is that I will end up with 3 tables, info, house and tenants.

Info:
District
Estate
BlockNo
Picture

House:
HouseNo
NoBedrooms

Tenants:
Name
Id

challenge is getting a unique ID out of the 3 tables to link them. info to link with house and house to link with tenants. I do not have a unique key in any tables so have to create them using after update procedure. For the info, the unique key are estate and block. So I created address which is the 2 data combined.

for house. unique key is estate and houseno combined which is stored in address 1 (after update procedure). When adding house, the table is looking up the value from table info.address and tenants is looking up table house.address1. These creates their own relationship.

What do you think? Can you think of a better way of designing them? note that the data are in excel and I have to load them in the tables later on. Over 100 of them!!!

[June7]

Only 100?

I avoid compound primary keys. Can use an autonumber field as unique ID primary key which can then be saved as foreign key in related tables. Then no code is needed.

Don't understand the Estate field. Why would the Info table have picture? Shouldn't the House table have picture?

Don't see any 'address' fields.

Is it possible that tenants could be associated with more than one house (maybe they move and come back). If you don't need to allow for this then the junction table I suggested is not needed.

Consider:

Info:
ID (primary key)
District
Estate
BlockNo
Picture

Tenants:
ID (primary key)
Name
Phone
Mailing

House:
HouseNo
NoBedrooms
InfoID (foreign key)
TenantID (foreign key)

[Daniel2000]

Thanks June 7. You have the distrci and then the estate. can have amny estate in same district. The info has picture because these are flats / bed sitters / condominium. the picture is the complete block of the houses. we have estate and can have several block on same estate. Block has average 8 units.

I did not put the address field in the example. At the time being, cannot see tenants associated with more than one house. I will work on the example you advised and then will advise you.

Thanks.

[orange]

The links are found in the Business Rules/Facts. For the data model I suggested in post #2 here are the business facts
http://www.databaseanswers.org/data_...king/facts.htm

Requirement : To create a contact database for tracking, among other things, tenants for rental units. 
Each contact could have several current addresses and several current phone numbers. 

From time to time, a contact could change some piece of information, such as their name (get married), 
an address, or a phone number. 
A range of Enquiries must be supported.
A history must be maintained to look up old information, and track down old Tenants.
An audit trail should be provided to track when a change was made and by whom. 
 
Things of Interest in the Scope of the Data Mode :-
Addresses.
Contact Details.
Operators.
Properties.
Tenants.
 
  
What are the relationships between these Things of Interest :-
An ADDRESS can be occupied by zero, one or many TENANTS.
A TENANT can occupy one ADDRESS at any one time.
An ADDRESS HISTORY.
 
  
What are the characteristics of these Things of Interest :-
An OPERATOR has a Name.
A PROPERTY has an ADDRESS.
A TENANT has a NAME.
Etc..
 

When you decide or are tasked with building a data base, you would gather facts and review these with proponents to verify/adjust as necessary an document the specifications from which the data base will be designed.

[Daniel2000]

Hi June7. Have started looking at the example you mentioned, info.table having id as primary key, tenant.table having id as primary and house.table having info.id and tenant.id as foreign keys.

got confuded when designing the forms for the 3 tables. See attached. Please help me out with one example.

[June7]

Primary and foreign key fields must be the same type of data - either both text or both numeric or both date/time. Your primary keys are autonumber (a numeric type). Therefore the foreign keys must be number type. Change infoid and tenantid to number type fields.

Will have to delete and re-establish the relationships.

Use comboboxes - review http://www.datapigtechnologies.com/f...combobox3.html

[Daniel2000]

Hi All. Thank you for all your help so far. As this is a bigger task than I anticipated, I had to download the desktop real estate template and customise same rather than designing from scratch. I am almost through with the customisations and all is left is for me to test adding new data.

The task at hand now is to post a monthly service fee on all the units. So far my idea is to use query to create the feestable. this query is to look for all active units (houseno) and add a monthly fee and description on them. Then monthly, I will use same query but this time create a monthlyfees table. This monthlyfees table will then append the data in the feestable.

I tested it out and went ok for 2 months but third month is giving me duplicates... the monthlyfees table has 473 records but when to append it says it will append 946 records and if i say yes, I can see the duplicates for the 3rd month.

Anyone has a better idea on how to charge the monthly fees?

Thanks in advance

[June7]

Post the query for analysis. Probably need filter criteria.