Page 1 of 1

Real Estate Listing Database Design

Posted: Thu Aug 04, 2005 11:11 pm
by slindstr
Hello,

I searched around a while and couldn't really find anything that helped me out. Here's the scenario:

I need a way to update real estate listings for my web site by entering information in text fields and uploading pictures.

I'm fairly new to using MySQL but I already know how to insert the data into an SQL database and how to upload pictures to a folder using php, but I was wondering what the best way is to deal with the pictures.

The only solution I can think of is to upload the files to their folder, and then save their paths to an array, and then save the array in the SQL database with all the other info. This way when I go to generate the html page I can just type something like:

Code: Select all

<img src="php code to retrieve the path of the picture">
Would this work?

Thanks for your insight!
Steve Lindstrom

Posted: Thu Aug 04, 2005 11:15 pm
by hawleyjr
Yes, store the path and/or file name in a db and call the url. A much metter option then storing the image in a table...

Posted: Thu Aug 04, 2005 11:24 pm
by slindstr
Thanks for the quick reply!

Thanks
Steven Lindstrom

Re: Real Estate Listing Database Design

Posted: Sun Aug 14, 2005 3:29 pm
by npeelman
slindstr wrote:Hello,

I searched around a while and couldn't really find anything that helped me out. Here's the scenario:

I need a way to update real estate listings for my web site by entering information in text fields and uploading pictures.

I'm fairly new to using MySQL but I already know how to insert the data into an SQL database and how to upload pictures to a folder using php, but I was wondering what the best way is to deal with the pictures.

The only solution I can think of is to upload the files to their folder, and then save their paths to an array, and then save the array in the SQL database with all the other info. This way when I go to generate the html page I can just type something like:

Code: Select all

<img src="php code to retrieve the path of the picture">
Would this work?

Thanks for your insight!
Steve Lindstrom
I'm a fan of storing images in the DB when needed and I think this is a great reason. Do you know how many photos you will be attaching to a particular listing? Remember that a CHAR/VCHAR only holds up to 255 characters for your array of filenames (10 pics * 15 char filename 'image_xxxxx.jpg' = 150 chars). Going VCHAR hinders the DB and storing images in the same table as the text info converts everything to VCHARs. If you store them in the database (separate table - KEY, POINTER, BLOB data) you can assign any number of images to a listing just by adding another entry to the DB and POINTing it to the listing in question. Any updating or deleting would never touch the listing table. And backups would contain everything, text, images, everything.

Norm

Re: Real Estate Listing Database Design

Posted: Sun Aug 14, 2005 4:08 pm
by McGruff
npeelman wrote:Do you know how many photos you will be attaching to a particular listing?
That's simply an argument for db normalisation ie create an images table with a one-to-many relationship from properties to images.

Posted: Mon Aug 15, 2005 9:58 am
by timvw
Here is a possible db/fs setup:

houses (house_id, name, location, ...)
houseimages (house_id, image_id)
images (image_id, name, real_path) //

And probably end up with real_path as /foo/images/image_id (Which would make the real_path redundant in the db table).