Real estate/ leasing database help?

[dannybhoy]

Hi all

Im new to all this so please be gentle....Im currently doing a degree in Business and for IT we are doing access2007, for our 100% Continuous assessment we have been asked 2 come up with a simple database for a business . I have been given a estate agent / property management business .
We have to do it ourselves and present it to our lecturer in April to prove we did it . Im only brain storming at the moment but just wondered if anyone had any pointers as im not sure which would be easier a real estate agents or a property leasing business I think the leasing would give me more options but still not sure.
Now this doesnt have to be done to programmer standard we just have to show understanding of access linked to excel which we covered last year. Just looking for ideas really not too much at all on the internet for a business such as this looks like I got the short straw to be honest as others in my class got doctors surgeries and dvd rental businesses which i think would be easier.......

well anyway only started today but in terms of tables for a property leasing business I was thinking

Properties - property ID -address- rooms - property value - rental value
-
Tenants - Tenant ID - current property ID - First name - second name - months at existing address- monthly rental- payment up to date

Landlord Details -Landlord ID - First Name- second name- address- monthly payment

Property Maintenance Property ID

thats as far as ive got feel free to rip me to shreds haha cheers

 

[CBrighton]

I'd get rid of "payment up to date" in the tenants table and create a payments table.

Something like:
PaymentID (PK)
TenantID(FK)
PaymentAmount
PaymentDate
PaymentMethod

Maybe add another table to log communications between the compay/landlord and tenant?

I assume the property maintenance table will track work done to the property, costs, cause of damage, etc. If not I'd have a table for that too, linked to the property table.

 

[boblarson]

And what is first name and second name (in both the tenants and landlord tables)?

 

[dannybhoy]

Thanks for your help sorry for dely in responding couldnt log in all day today .......

First name and seconda name just meant say Bruce.....WIllis thats all !

C Brighton thats a great idea as I was wondering how I could link the table to other tables and your use of the primary and foreign keys and your idea for an property maintenance table make real sense..... As I will also have to link excel to my database at some point ( only in wk 2 so far so not sure how we will do this but do not need to know now) I will also be demonstrating nested IF statements which I have done before and confident in doing them.

I had a bit of an ideaearlier and would like to run it by you ... If I also did an employee table I could then do nested IF statements to see who was top seller this month? year? see who had made most commision ? ages of staff etc I could run numerous queries ultimately (again yet to cover queries yet)
What im asking is do you think this would be a good idea or would I just be complicating things too much .....I must ask lecturer whats minimum /maximum number of tables etc now a staff table could have

employee number pk
Name
address
current years at firm
sales this month
sales this year
commision
On target

ive only jst started relationships so not going to understand that fully yet I do not even fully understand the rules governing primary and secondary keys just wanted 2 make a start on this now instead of doing my usual and waiting till the last week........must admit this is a brilliant forum and allready ive learnt so much .....Im very appreciative to all the help so far

 

[Thales750]

What level of normalization is required?

 

[dannybhoy]

sorry but im only just over a week into this access course forgive me but what does Normalization mean ?

 

[CBrighton]

An employees table could certainly be included, you could add an FK key in the properties table to link it to the employee responsible for leasing it via the PK in the employee table.

I'd advise reading through this site: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

It's a short paper about relational database design which explains both relationships and normalisation.

 

[dannybhoy]

Thankyou CBrighton ill be back on here when I have read a bit more and understand a bit more thanks

 

[dannybhoy]

Hi me again I now have thought of 4 tables they are as follows though I need to have 5 minimum or even 6 and I am having difficulty thinking of a way of linking the final tables.....I have all the other info for populating tables its just the relationships that are stressing me out. Ive just noticed that I cant have rental and sales figures in the same properties table as surely they will conflict so maybe I should come up with a separate table for properties for sale and for rent though then I may not be able to link tables efficiently.....As I said this is a work in progress but its proving very annoying

Employees
Employee ID PK to property tableNameaddress
dob
salary
houses sold
houses rented
commision
date hired

Property
Property ID PK
Employee ID FK
address
lease
for sale
valuation
bedrooms
garage
monthly rental fee
start date
end date
maintenance fee
maintenance code

Tenants
Tenant ID PK to payment tableNameProperty ID FK to Property tablehome phone
mobileoccupationmonths at address
landlord ID FK to landlord table

PaymentS
Payment ID PK
Tenant ID

Miantenance
maintenance IDrecords etc

Landlords
Landlord ID
details etc

also how many tables can a primary key be linked to ? Thanks and sorry if this looks a shambles not as easy as I thought , Our brief was to keep things simple but maybe I have bitten off more than I can chew here

 

[CBrighton]

Primary keys can link all over the place.

Most of my tables have a staff or account table. I use the employee number or account number to link with many other tables.

If you need a minimum number of tables then you can always add more. Things like a user login system could be added which would add a table for signons, passwords, access levels/rights, etc.

 

[dannybhoy]

Thanks again, thats brilliant I didnt realise that the primary could link to many foreign keys but I suppose thats what 'one to many' in relationships means. Im seperating the properties tables now and gonna stick them up on the actual database and see if theres any problems.....Luckily i do not have to go into that amount of depth with staff passwords etc but im thinking 6 tables will be more than enough as everyone in my class is doing 5, I will have plenty of options then to do queries on many different things and also incorporate the excel side of things..... The more I look at it though I cant see any way of including a single maintenance table that would work alongside the others without conflicting in some way.....any suggestions would be welcome.....

thanks again for the help Im learning all the time

 

[Thales750]

Thanks again, thats brilliant I didnt realise that the primary could link to many foreign keys but I suppose thats what 'one to many' in relationships means.

Actually one-to-many refers to a unique record in one table (primary key from a parent table) linked to any number of records (foreign key) in a child table.

The description is for one relationship.

And you can have the Primary Key from the parent table create any number of one-to-many relationships in a system.

 

[dannybhoy]

so let me get this straight as I have now designed my 5 tables I may have one more to add maybe (will get a screen shot on here soon) , In the employee table I have my primary key as employee ID this is then linked to a foreign key in rental properties named employee ref and also linked to a foreign key in sales properties named employee ref both with referential integrity checed.....I did this as I had to seperate the properties into rental and sales as they have different unique fields to poulate, my thoughts were that the employee ref field in each table being the code which shows who is responsible for the property........It made the relationship when i created it and my lecturer if im honest wasnt a 100% sure if I wouldnt have problems further down the line when I come to Queries etc she said it looks fine anyway tho she aint an experienced programmer and we are only touching on access really on this course ....As I said will have a screen shot up b4 the night is out just hope it works as im starting to populate my tables now which with my typing skills will take me all night so if i had to start again I think I would cry !

 

[Rabbie]

sorry but im only just over a week into this access course forgive me but what does Normalization mean ?

Without an understanding of Data Normalization you are not going to do a good job. Google it if you don't know what this is about.

Normalization is so fundamental to Relational Databases tha you need to get your head round this before you go any further.

 

[dannybhoy]

Thanks I have done lots of reading on normalization I have 3 access books here with me and understand it to a certain degree but I do not have the experience of building multiple tables to know if I will have problems and to put it simply someone who does this for a living or even does it as a hobby is going to understand terms a lot quicker than me. I know not to duplicate data thats what i have eliminated by having sales properties tbl and rental properties tbl every there is no duplicated data in eithe rof them but they are linked to an employee ID in the employees tbl . I just want to know if this is wrong or right really and if its wrong is there any suggestion to put it right......Maybe im too much of a novice to be on this website but doesnt seem many other places to go and im a student so cant pay someone to do it as its my project and I want to learn.........

I have included my realtionships table so far ao any advice would be great and remember this is fictional so is going to be basic I only have to do 3 examples of each query type ie wild cards, expressions , paramters , do a few reports and some forms it doesnt have to be complex at all

 

[DCrake]

If you first think about the comodity - A house.

The house can be either for sale or for rent or even both but lets keep it simple sale/rent.

The house can be either empty or occupied at any point in time.

It can be a single occupancy or a house of multiple occupancy.

Therefore you need a table to hold the occupants in. Each occupant will have a key this will be referred to as a Foreign Key (FK). They also have a Primary Key to identify them as you are aware.

One person can live in many houses over the course of time, but in theory can only live in one house at any one time.

Then you have got the salespersons (Employees) thay also have a PK to uniquely identify them, this too becomes a FK in the houses table.

The tenant also pays rent/mortgage so you need another table to hold this information. Now you should also record the current rent in the house table and you also need to hold it in the rents table as this can change over time but you need to store it for historical purposes.

Just think about all the different elements that are required. You could go down the route of tennancy agreements and itineraries (house could be part or fully furnished) however for the situation you are in this may overcomplicate the issue and be outside the remit.

 

[dannybhoy]

Thanks again plenty to think about there but my question still hasnt been answered?