Real Estate Listing Database Design

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
slindstr
Forum Newbie
Posts: 6
Joined: Thu Aug 04, 2005 11:01 pm

Real Estate Listing Database Design

Post 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
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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...
User avatar
slindstr
Forum Newbie
Posts: 6
Joined: Thu Aug 04, 2005 11:01 pm

Post by slindstr »

Thanks for the quick reply!

Thanks
Steven Lindstrom
npeelman
Forum Commoner
Posts: 32
Joined: Tue Jul 27, 2004 5:13 am
Location: Oviedo,FL.

Re: Real Estate Listing Database Design

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Re: Real Estate Listing Database Design

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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).
Post Reply