Newbie. Is database setup ok
Posted by: simon cleaver
Date: February 23, 2007 08:35AM

Hi All,
Is it possible for thoughts and advice on database below.
I am trying to create an online database to list spanish real estate.

I am pretty much a newb with MySQL and PHP and thought I should get my db correct before I tackle the php.

I would like the db to be "searchable" by areas, price, location, type (maybe others). So do these fields all need to be indexed?

I'm not sure on best method fo storing images. Whether to use a BLOB field, or a field that contains a "link" to images.
One property could have many images - so advice on how to create relationships is appreciated.

I understand would be better of with Innodb type tables, but at the moment this is not possible with my web host. I am transferring to a new host and can change table type then.

Many thanks for any advice or ideas, here is db and tables I have currently set up:

db name: Listingsdb

# Table structure for table `tblareas`

CREATE TABLE `tblareas` (
`area_id` int(11) NOT NULL auto_increment,
`area_name` varchar(100) NOT NULL default '',
PRIMARY KEY (`area_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

# --------------------------------------------------------

#
# Table structure for table `tblimages`

CREATE TABLE `tblimages` (
`imageid` int(11) NOT NULL auto_increment,
`propertyid` int(11) default NULL,
`description` varchar(50) default NULL,
`fileContents` blob,
`mimeType` varchar(30) default NULL,
`mimeName` varchar(50) default NULL,
PRIMARY KEY (`imageid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

# --------------------------------------------------------

#
# Table structure for table `tblproperty`

CREATE TABLE `tblproperty` (
`propertyid` int(11) NOT NULL auto_increment,
`hisref` varchar(20) default NULL,
`location` varchar(50) default NULL,
`plotsize` int(11) default NULL,
`builtsize` int(11) default NULL,
`terracesize` int(11) default NULL,
`price` int(11) default NULL,
`bedrooms` int(11) default NULL,
`bathrooms` float(2,1) default NULL,
`pool` varchar(20) default NULL,
`garden` varchar(20) default NULL,
`parking` varchar(20) default NULL,
`previewdescription` text,
`fulldescription` text,
`moredetailslink` varchar(100) default NULL,
`dateadded` date NOT NULL default '0000-00-00',
PRIMARY KEY (`propertyid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

# --------------------------------------------------------

#
# Table structure for table `tblpropertytype`


CREATE TABLE `tblpropertytype` (
`propertytype_id` int(11) NOT NULL auto_increment,
`propertytype` varchar(100) NOT NULL default '',
PRIMARY KEY (`propertytype_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Options: ReplyQuote


Subject
Written By
Posted
Newbie. Is database setup ok
February 23, 2007 08:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.