What I'd like to do is post a very very basic version of the database structure and get feedback.
Then gradually add more and more of the real fields that are needed and get more feedback along the way.
I'll be giving details about the purpose of the individual fields, the problems they were supposed to solve, the client's reason for having me add them, etc. (this is based on a real estate system that's been in use for over 5 years on a dozen sites, hopefully it will be insightful for others just starting on their first real estate site)
Anyone interested in helping?
At this early stage lets just look at the table for the real estate listings themselves.
Code: Select all
CREATE TABLE IF NOT EXISTS `listings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`import_id` varchar(64) NOT NULL DEFAULT '0',
`mls_id` varchar(64) NOT NULL DEFAULT '0',
`client_id` int(11) NOT NULL DEFAULT '0',
`agent_id` int(11) NOT NULL DEFAULT '0',
`feature` varchar(16) NOT NULL DEFAULT 'no',
`hide` varchar(16) NOT NULL DEFAULT 'yes',
`property_name` varchar(64) NOT NULL DEFAULT '',
`price` int(11) NOT NULL DEFAULT '0',
`location1` NOT NULL,
`location2` NOT NULL,
`location3` NOT NULL,
`description` text NOT NULL,
`notes` text NOT NULL,
`date_listed` date NOT NULL DEFAULT '0000-00-00',
`date_verified` date NOT NULL DEFAULT '0000-00-00',
`date_expired` date NOT NULL DEFAULT '0000-00-00',
`bedrooms` int(9) NOT NULL DEFAULT '0',
`bathrooms` decimal(3,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`id`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2863 ;
Field Info:
import_id: This is not a foreign key. All of my realestate sites have needed to import existing data from the clients old sites or messy excel files. So this is where their old internal identifier gets saved. This is useful for the first year or so after switching to a new site so old regular visitors can paste in the same old id value they remember and get the listing the expect. Also good to have your 404 page look at the old site id and redirct the visitor to the proper page on the new site.
mls_id: This is not a foreign key. The way the mls in this country works, you add the listing to the central mls server which gives you a string that you then have to paste into your own site. There's no web services or anything for automatic integration between site and mls.
client_id: foreign key to clients table
agent_id: foreign key to agents table
category: I'm not quite sure how i want to handle this one.
I can tell you what I currently do. There are 4 main categories:
residential = residential properties for sale (like a house, condo or vacant lot)
rental = residential properties for rent (like a house, apartment, condo)
commercial sale = commercial properties for sale (like a warehouse, office, parking lot, or house surrounded by warehouses (seriously, we have _almost_ no zoning laws in my country ))
commercial rental = commercial properties for rent
In the early days I could just put one of those 4 values in and be done with it. But it turns out there are a ton of places where someone would build a house, remodel the side facing the street to be hair salon, split the main house into 2 apartments and rent them, add a 2 storey bit on to the back of the building for the owner to live upstairs, and operate a sign-shop downstairs while renting space in the back yard to a mechanic to store old cars there. On top of all that if someone makes a good enough offer they could buy the whole lot. Seriously. Places like that covers all 4 categories! So I had to add the ability to have multiple categories and I foolishly did that by saving a comma separated list of categories to this field
feature: yes/no or 0/1 value. Featured listings can be featured on the home page, randomly pulled into a sidebar, special searches for only featured properties, etc...
hide: prevent the listing from being displayed to the public (in the future I'll discuss other fields and combinations of values that result in the listing being hidden)
property_name: name/title/very short description of property
price: sale price of property. But remember that a single property can be for sale, rent, or lease under 3 completely different prices.
Sell the whole property for $700,000 (residential sale)
Sell just the back yard as a separate lot for $180,000 (residential sale OR commercial sale)
Rent the back apartment as a living space $700 per month(rental)
Rent the back apartment as an office space for $850 (commercial rental)
I have a way of sort of handling this that I'll discuss later.
location1, location2, location3:
Usually an island, a section of the island, an up-scale gated community in that area of the island.
My country is a collection of islands so city/county/state doesn't apply here.
There's also the issue of tiny specks of islands offshore from the larger islands being semi-famous while nobody cares about the main island. Imagine if any time you asked someone to name the 50 US states they always said "Hollywood" instead of California.
description: long text description of property
notes: private notes only visibly by administrators
date_listed: when the listing was added
date_verified: the last time an agent confirmed the accuracy of the information about the listing
date_expired: when to stop showing the listing to the public
bedrooms: # of bedrooms
bathrooms: # of bathrooms
Does it look sensible so far?
Anyone see anything that should be in a separate table?