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...]

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?

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]

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

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 ;)

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

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

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.

what storage engine should i use?

any reply ?

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... :)

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