I need help creating a data dictionary for PHP/MySQL driven application

I am creating a property search/ comparison website. I require help creating the mysql tables that house the data.

I am unsure how many tables I will need and what I should use for the column headings also.

I want the website to be as precise and complete as possible.

I got the idea to create this website as I am currently looking for a place to live and the search criteria is really simple however the property compari开发者_高级运维son websites don't have complete/accurate data on the properties of which I'm searching.

I don't know how 'normalised' the tables need to be, for example I could have:

TABLE: properties, FIELDS: id, address_id, bedrooms, bathrooms, garage, garden. TABLE: address, FIELDS: address_id, address_line_1, address_line_2, address_line_3, town, city, postcode.

But then would I need to go into detail about each bedroom within a house and specify its dimensions etc.

If anyone has any suggestions, no matter how small. I would really appreciate it.

For arbitrary properties of the house you could have one table for all.

CREATE TABLE `property_prop` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`properties_id` INT UNSIGNED NOT NULL ,
`name` VARCHAR( 32 ) NOT NULL ,
`value` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
INDEX ( `property_id` )
) ENGINE = MYISAM

INSERT INTO property_prop (properties_id,name,value) VALUES (100,'Spacing','100');
INSERT INTO property_prop (properties_id,name,value) VALUES (100,'Rooms','4');
INSERT INTO property_prop (properties_id,name,value) VALUES (100,'Description','This is a nice house. ;)');

//Get a list of all properties for house with id 100.
SELECT name,value FROM property_prop WHERE properties_id=100;

For good and fast searching on site the best way is standalone searching server like SOLR or Sphinx. Regarding your question:

Mysql Solution.

1. Create table like you described.

2. Storing data in xml format for each field. For example, for field bathrooms you can create xml like

   <rooms number="3"> <room length="5" width="10"> <room length="7" width="9"> <rooms>

3. Access to specific value in some field via mysql function ExtractValue(). See http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html