Newbie. Is database setup ok
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 ;