Page 1 of 1

Table design from the ground up

Posted: Mon May 03, 2010 12:54 pm
by thinsoldier
I need help rebuilding a huge mess of a database. It's for a real estate site.

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?

Re: Table design from the ground up

Posted: Fri May 07, 2010 12:40 am
by Zyxist
At the first sight, I would put at least part of the location description to a separate table. You say that your country lies on several islands, but this does not matter from the database point of view whether they are called islands, states, provinces or something else. If you want to clean the database, prepare a collection of island rows which would simplify searching. If there is a small famous piece of land, you can always add it as a second, optional level (islands -> regions) or an extra text field.

Another thing are data types. For example, I completely do not understand why "feature" and "hide" columns are "varchar(16)". Storing several different representations of the same state is not correct. If you have strings like "yes", "no", you should convert them to numerical or logical identifiers. And why "bathrooms" are stored as fractions? Do your customers have properties with 3.3 bathrooms? :)

The last thing: use InnoDB and modern features, as foreign keys or transactions. They are really helpful with every bigger database preventing from data corruption.

Re: Table design from the ground up

Posted: Mon May 10, 2010 2:04 am
by thinsoldier
Zyxist wrote:At the first sight, I would put at least part of the location description to a separate table.
Those 3 fields will hold id's from another table. In the current system I have islands, locations, districts, and a lot of other stuff in a general purpose 'categories' table. I'm pretty sure it'll be an improvement to have a table for just the 3 levels of locations but I'm not sure how much more complicated my JOIN search queries are going to need to become if I split up everything in that categories table. (I still don't know jack about joins or how to build them dynamically for an advanced search form).
Zyxist wrote:Another thing are data types. For example, I completely do not understand why "feature" and "hide" columns are "varchar(16)".
Side effect of this system being built from a much older system and I never really got around to tweaking the defaults from the old tables. Also, on a few occasions I've had to use those 2 fields in ways that weren't originally intended. Like feature in "sectionA" or "agentSection2". And hide from user type1 or user type 2. In hind sight I think those unique cases should have been handled by a separate table or a new field or something.
Zyxist wrote:And why "bathrooms" are stored as fractions? Do your customers have properties with 3.3 bathrooms? :)
For consistency we forced "half bath" to be specified by ".5" but I guess it doesn't really have to be a decimal field... it could just be a varchar field.
Is there any benefit to using a decimal field over varchar if your query contains WHERE bathrooms > 3.
Hmm... just remembered a couple times where somebody had some mansions with multiple half baths.
ie: Bedrooms: 14, Bathrooms: 9, Half-Baths: 3
I guess half baths should really be its own field.
Zyxist wrote:The last thing: use InnoDB and modern features, as foreign keys or transactions. They are really helpful with every bigger database preventing from data corruption.
I've started looking into those more now that I've found out nobody around me knows why the heck we use myisam in the first place. There shouldn't be any issue using InnoDB for me since I'm the only one doing real estate. I just hope there's nothing missing from the implementation of InnoDB on our server that would keep things from working. I'm on a really old mysql version.

Re: Table design from the ground up

Posted: Thu May 20, 2010 10:38 am
by thinsoldier
If my listings table has a lot of fields with small values (int, varchar)
and 2 fields that are text
and the client tends to put a lot of text in these two fields
would it be beneficial to have those 2 text fields in a separate table?

99% of the time when any listing details are shown it's just combination of the non TEXT fields.

And actually only 1 of those TEXT fields are ever shown to site visitors. The other is for admins only.

Re: Table design from the ground up

Posted: Thu May 20, 2010 10:45 am
by thinsoldier
If a listing needs to know where it's located (like country, state, city) do I really need to save location_1, location_2, location_3?

Couldn't I just save the deepest level location id as `location` then figure out the 1 or 2 locations above it later?

I'm leaning towards a "no" answer on that. I mean I could do it. But then it complicates when someone wants to do a search for all listings in "Florida" (id#127).
If most of the florida listings have "miami" (id#136) as the `location` value it won't find them. Unless I use some sql magic that I don't know about.

*U.S. locations used because they'd probably be more familiar to y'all.

Re: Table design from the ground up

Posted: Thu May 20, 2010 11:04 am
by AbraCadaver
thinsoldier wrote:If a listing needs to know where it's located (like country, state, city) do I really need to save location_1, location_2, location_3?

Couldn't I just save the deepest level location id as `location` then figure out the 1 or 2 locations above it later?

I'm leaning towards a "no" answer on that. I mean I could do it. But then it complicates when someone wants to do a search for all listings in "Florida" (id#127).
If most of the florida listings have "miami" (id#136) as the `location` value it won't find them. Unless I use some sql magic that I don't know about.

*U.S. locations uses because they'd probably be more familiar to y'all.
You would need to save the city, state/territory, country for all listings or possibly the zip code could work as well. There is a Cleveland TX and Cleveland OH, as well as a Hollywood FL, Hollywood CA, Hollywood MD, etc... for example.

Re: Table design from the ground up

Posted: Fri May 21, 2010 10:07 am
by thinsoldier
AbraCadaver wrote:You would need to save the city, state/territory, country for all listings or possibly the zip code could work as well. There is a Cleveland TX and Cleveland OH, as well as a Hollywood FL, Hollywood CA, Hollywood MD, etc... for example.
I'm keeping the list/tree of locations in a `locations` table and then I'll save 3 location ids in the listings table as location1, location2, location3.

In the old system I had a general miscellaneous `categories` table where I heaped together a lot of stuff like lists of locations, co-brokers, roof types, etc. It made sense at first since having a table for each "list" would mean a dozen tables with the same structure but it turns out that when I need to include that categories table in various join queries (sometimes joining it 5 times in 1 query) the sheer number of records in that table x5 contributes to my slow queries.

This time I'm trying a separate table for each list. This seems better to me especially since only about 30% of my old categories table ever needed to be seen by public visitors.

Re: Table design from the ground up

Posted: Fri May 21, 2010 11:15 am
by thinsoldier
I got tired of staring at the mountain of fields that is the listings table so I updated the rest of the tables in the database.

Half baths is now its own field.

Administrator Notes are now their own table. Added benefit is now I can build a more advanced notes interface where each change to the notes can be its own record dated & credited to the user who made the note.

I've decided to move the "featured" value to a separate table as well. In the current system, picking a random featured item takes forever to query the listings table. It would be much quicker to have the ids of the 30 or so featured listings in their own table.

I've been looking at ways to speed up that query and there are many other ways I could approach this so I might drop the featured table in the future.

I'm pretty satisfied with them at this point. I'm sure there are many improvements that can be suggested.

The purpose of some tables & fields might not be obvious. I'd gladly explain them.

Code: Select all

-- Server version: 5.1.33
-- PHP Version: 5.2.9
-- Database: `realty_3`

-- --------------------------------------------------------
-- Table structure for table `r3_agents`

CREATE TABLE IF NOT EXISTS `r3_agents` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL DEFAULT '',
  `lastname` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(32) NOT NULL DEFAULT '',
  `password` char(32) NOT NULL,
  `level` varchar(16) NOT NULL,
  `jobtitle` varchar(32) NOT NULL DEFAULT '',
  `email` varchar(32) NOT NULL DEFAULT '',
  `phone` varchar(32) NOT NULL DEFAULT '',
  `cellphone` varchar(16) NOT NULL DEFAULT '',
  `listoffice` varchar(32) NOT NULL DEFAULT '',
  `bio` text NOT NULL,
  `photo` varchar(32) NOT NULL DEFAULT '',
  `show_on_site` varchar(6) NOT NULL DEFAULT 'yes',
  `sort` int(9) NOT NULL DEFAULT '50',
  `developer` varchar(32) NOT NULL DEFAULT '',
  `notify_islands` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_amenities`
--

CREATE TABLE IF NOT EXISTS `r3_amenities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `market` varchar(16) NOT NULL COMMENT 'sale or rental, resident or commerc',
  `category` varchar(64) NOT NULL COMMENT 'formerly formcat',
  `sort` int(11) NOT NULL COMMENT 'sort order',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_clients`
--

CREATE TABLE IF NOT EXISTS `r3_clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL DEFAULT '',
  `lastname` varchar(32) NOT NULL DEFAULT '',
  `company` varchar(64) NOT NULL DEFAULT '',
  `title` varchar(6) NOT NULL DEFAULT '',
  `salutation` varchar(32) NOT NULL DEFAULT '',
  `phone_work` varchar(32) NOT NULL DEFAULT '',
  `phone_home` varchar(32) NOT NULL DEFAULT '',
  `fax` varchar(32) NOT NULL DEFAULT '',
  `cell` varchar(32) NOT NULL DEFAULT '',
  `email` varchar(32) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `address1` varchar(32) NOT NULL DEFAULT '',
  `address2` varchar(32) NOT NULL DEFAULT '',
  `address3` varchar(32) NOT NULL DEFAULT '',
  `city` varchar(32) NOT NULL DEFAULT '',
  `zip` varchar(9) NOT NULL DEFAULT '',
  `state` varchar(32) NOT NULL DEFAULT '',
  `country` varchar(32) NOT NULL DEFAULT '',
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_cobrokers`
--

CREATE TABLE IF NOT EXISTS `r3_cobrokers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `location` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of all cobrokers (category)' AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_featured_listings`
--

CREATE TABLE IF NOT EXISTS `r3_featured_listings` (
  `listing_id` int(11) NOT NULL,
  UNIQUE KEY `listing_id` (`listing_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='unique ids of featured listings';

-- --------------------------------------------------------

--
-- Table structure for table `r3_join_amenities_listings`
--

CREATE TABLE IF NOT EXISTS `r3_join_amenities_listings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `listing_id` int(11) NOT NULL,
  `amenity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_listings`
--

CREATE TABLE IF NOT EXISTS `r3_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',
  `hide` varchar(16) NOT NULL DEFAULT 'yes',
  `property_name` varchar(64) NOT NULL DEFAULT '',
  `price` int(11) NOT NULL DEFAULT '0',
  `location1` int(11) NOT NULL,
  `location2` int(11) NOT NULL,
  `location3` int(11) NOT NULL,
  `description` 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,
  `bathrooms` int(9) NOT NULL,
  `halfbaths` int(9) NOT NULL,
  `living_area` int(11) NOT NULL,
  `year_built` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2863 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_listing_notes`
--

CREATE TABLE IF NOT EXISTS `r3_listing_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `listing_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `note` text NOT NULL,
  `author` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='notes for  listings' AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_locations`
--

CREATE TABLE IF NOT EXISTS `r3_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_media`
--

CREATE TABLE IF NOT EXISTS `r3_media` (
  `id` int(11) NOT NULL,
  `foreign_id` int(11) NOT NULL,
  `type` varchar(32) NOT NULL,
  `original_filename` varchar(64) NOT NULL,
  `file` varchar(64) NOT NULL,
  `caption` varchar(255) NOT NULL,
  `sort` int(11) NOT NULL,
  `state` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Media for non realty cms area';

-- --------------------------------------------------------

--
-- Table structure for table `r3_media_for_listings`
--

CREATE TABLE IF NOT EXISTS `r3_media_for_listings` (
  `id` int(11) NOT NULL,
  `foreign_id` int(11) NOT NULL COMMENT 'formerly "parent"',
  `foreign_table` int(11) NOT NULL COMMENT 'formerly "section"',
  `type` varchar(32) NOT NULL,
  `original_filename` varchar(64) NOT NULL,
  `file` varchar(64) NOT NULL,
  `caption` varchar(255) NOT NULL,
  `sort` int(11) NOT NULL,
  `state` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Media for common cms modules';

-- --------------------------------------------------------

--
-- Table structure for table `r3_media_for_locations`
--

CREATE TABLE IF NOT EXISTS `r3_media_for_locations` (
  `id` int(11) NOT NULL,
  `foreign_id` int(11) NOT NULL,
  `type` varchar(32) NOT NULL,
  `original_filename` varchar(64) NOT NULL,
  `file` varchar(64) NOT NULL,
  `caption` varchar(255) NOT NULL,
  `sort` int(11) NOT NULL,
  `state` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='media for popular locations';

-- --------------------------------------------------------

--
-- Table structure for table `r3_members`
--

CREATE TABLE IF NOT EXISTS `r3_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(64) NOT NULL DEFAULT '',
  `lastname` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(64) NOT NULL DEFAULT '',
  `password` char(128) NOT NULL,
  `state` varchar(64) NOT NULL,
  `country` varchar(64) NOT NULL,
  `newsletter` char(3) NOT NULL DEFAULT 'no',
  `beta_tester` char(3) NOT NULL DEFAULT 'no',
  `lastlogin` date NOT NULL,
  `created` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_members_favourites`
--

CREATE TABLE IF NOT EXISTS `r3_members_favourites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `listing_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `indexboth` (`listing_id`,`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='members fav listings' AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `r3_member_searches`
--

CREATE TABLE IF NOT EXISTS `r3_member_searches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `label` varchar(128) NOT NULL,
  `format` varchar(5) NOT NULL DEFAULT '',
  `savedsql` text NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created` date NOT NULL DEFAULT '0000-00-00',
  `last_referral` date NOT NULL DEFAULT '0000-00-00',
  `last_rss` date NOT NULL DEFAULT '0000-00-00',
  `last_mailing` date DEFAULT '0000-00-00',
  `last_listing_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 COMMENT='members saved searches' AUTO_INCREMENT=1 ;

Re: Table design from the ground up

Posted: Fri May 21, 2010 11:50 am
by thinsoldier
Back to the listings table. The main issue on my mind right now is that there are a lot of fields.

There are 4 main kinds of listings in my system (if someone has a better suggestion I'd love to hear it, but this way has worked well for my clients for years):
Residential properties for Sale
Residential properties for Rent
Commercial properties for Sale
Commercial properties for Rent

Some of the fields apply to all 4 types. Some only apply to the 2 residential types. Some only apply to the 2 commercial types. Some only apply to Sales or only to Rentals.
Some only apply to Residential Rentals.

Combined, they are a lot of fields and most are never seen by the public and never searched on. I'm wondering if/how I should/could move some of these fields to separate tables.