Real Estate Database

database structure help

[MrFahad]

Hello, I'm doing a real estate site as my IGCSE computer studies course work and i was wondering if someone can help me with the properties table structure.

can anyone tell me what fields should i use, because there are 3 types of properties villa's,buildings and ware houses. and i can't put number_of_apartments in a villa, so how should i do the structure of the database

Thank you :)

[physics]

Regarding the different types of properties, it seems like there are two basic options. If the non-shared fields are simple, like number_of_apartments, you could include them in the table for all kinds of properties - then when you display the data don't display fields with empty values.

Another, probably technically better, option would be to have a properties table and then separate tables for things like apartment info. So:

property:
id type ...
1 villa ...
2 building ...

apartment_info:
id_property number_of_apartments other_useful_apartment_info_field ...
2 6 ...

Then in the code if the type of a property is a building you'd look in the apartments table but not if it was a villa. Or use a join. Actually, even this structure isn't optimal - you'll be repeating the words villa, building, etc in properties - so there should be a table for those (which, incidentally, could also store the required fields for this property type).

If this seems a bit vauge/run on sorry about that - de-obfustication is left as an excercise to the student ;)

Usually the best way to approach these sorts of problems is to write all of the possible fields down and then get those fields into a normalized database structure:
[databasejournal.com...]

[MrFahad]

Field Type Description
property_id Int(11) Property identification number
property_name Varchar(40) Property name
Property_locationVarchar(40)Property location
property_type Varchar(40) Number of apartments if any
no_of_floor Int(40) Number of floors if any
no_of_bedrooms Int(40) Number of bedrooms
Features Varchar(1000) Features of the property
description Varchar(1000)Description of the property
property_price Int(11) The price of the property

What about something like that?

[ZydoSEO]

As Physics said, the proper way to normalize this schema would be something similar to the following:

TABLE: tProperty (this table should contain attributes that are common to ALL property types)
PropertyID
PropertyType
PropertyAddress (or AddressID if you keep all address types in a separate address table)
ListedPrice
ListingDate
ListingExpirationDate
CommissionType (Fixed, Percentage)
CommissionAmount (if CommissionType = fixed - Not sure if peeps every get paid flat fee for selling)
CommisionPercentage (if CommissionType = Percentage - This is normally how real estate agents/brokers are paid)
YearBuilt
HeatedSquareFeet
Acreage (I would put this here even though for a mobile home it may not always apply - only if selling lot+mobilehome)
...

Think of the above table as a superclass (like shapes) if you're familiar w/ Object Oriented programming. This would contain all attributes of properties that apply to ALL property types.

Then I would have a separate table for each sub-class (like circle, square, rectangle, tetrahedron, etc) of property such as single family detached houses, condominiums, duplexes, apartments, mobile homes, warehouses, etc. similar to the following:

TABLE: tSingleFamilyDetached
PropertyID (Foreign Key back to tProperty.PropertyID)
BedroomCount
BathroomCount
GarageType (none, 1car, 2car, etc)
Fireplaces (0,1,2,3, etc)
...

TABLE: tCondominium
PropertyID (Foreign Key back to tProperty.PropertyID)
BedroomCount
BathroomCount
GarageType (none, 1car, 2car, etc)
Fireplaces (0,1,2,3, etc)
HomeOwnersAssociationDues

TABLE: tMobileHomes
PropertyID (Foreign Key back to tProperty.PropertyID)
BedroomCount
BathroomCount
Manufacturer
Model
MobileHomeType (single-wide, double-wide)
Width (12', 14', 24', 28', etc)
Length (50', 60', 70')
Weight
....

and so on for all PropertyTypes.

If you're looking for high level info about a property, frequently you can get it from just the tProperty table. But if you need details for a particular type you join tProperty with one or more Property sub-class tables.

For example, if PropertyType=1 is an apartment building and you want all of the info about apartment buildings listed so far the month of November, you might do something like:

SELECT *
FROM tProperty p
JOIN tApartment a ON p.PropertyID = a.PropertyID
WHERE p.PropertyType = 1 AND p.ListingDate > '11/01/2008'

Hope this helps...
Zydo

[edited by: ZydoSEO at 9:11 pm (utc) on Nov. 7, 2008]

[MrFahad]

what the type of the PropertyType would be ? varchar or text?

[physics]

ZydoSEO, thanks for the explanation! But let's remember this is a SCHOOL PROJECT so we don't want to do the whole assignment for MrFarhad ;)

[MrFahad]

Come on Mr.Physics >_> give me a break

[ZydoSEO]

Where possible I always use INTEGER data types for 'IDs'. Integer comparisons are always faster than string comparisons.

[piatkow]

Of course it should all fall out from the logical data model. If the course has the OP building a database without learning any theory then his lecturer should be beaten over the head with the collected works of Fabian Pascal.

[MrFahad]

what storage engine should i use?

[MrFahad]

any reply ?

[ZydoSEO]

Are you asking which Database Management System (DMBS) to use as in MySql vs. MS SQL*Server vs Oracle vs something else? I would say that depends on what you have access to. If I were a poor college student then I would pick whatever is free that I have access to. But that's just me... :)

[MrFahad]

well I'm using MySQL, and i'm high school student :P (not poor), anyways i was talking about storage engines in MySQL e.g (MyISAM, InnoDB, HEAP etc..), but i searched and now i know what i need

thanks you