Best way to insert multiple values with checkboxes into db

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

thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Best way to insert multiple values with checkboxes into db

Post by thinsoldier »

califdon makes some good points but they really only relate to the tables: properties, buyers, property_types, and the linking table property2property_types.

like i said, I've tried multiple approaches to this an wound up going with a linking table for the multiple property type issue.

But for just remembering which property types a user of the system prefers, you don't need 3 tables for that. A comma separated list of type id's would work for that. Heck, you don't even have to save it in the database.

FYI: eventually the client is going to want to list the "amenities" of the houses/buildings/gated communities. Those will need the multiple table approach too.
Warning: I have no idea what I'm talking about.
getseen
Forum Newbie
Posts: 14
Joined: Thu Jul 23, 2009 8:50 pm

Re: Best way to insert multiple values with checkboxes into db

Post by getseen »

califdon wrote:I'm afraid that's bad advice. The first rule of normalization requires that every column represents a SINGLE-VALUED data element, so comma-delimited strings is one of the most fundamental mistakes you can make. The first thing to determine is whether any single property record can be described by more than one "type". Can a "chalet" also be "terraced"? If the answer is No, it's quite simple: just have a column for "type". If the number of "types" is small (say, under ten or so), I would use an enumerated data type ("enum"), which will only accept values defined in the table; this avoids misspellings, which then affect searches.

If a property might have more than one "type", you are describing a many-to-many relationship (one property may have "many" types, a "type" may be associated with "many" properties) and that always requires 3 tables. In this case, your property table, a table of possible "types", and the third intermediate table that holds the linking records; for every property-type relationship, there will be one record in the 3rd table. This is standard relational database design and trying to do it any other way is guaranteed to be a problem in the future operation of your database. Read a basic tutorial on relational database design, such as http://www.ehow.com/way_5465335_relatio ... orial.html or http://parallel.vub.ac.be/documentation/database/tutor/.
If i've got the 3rd 'holding' table, which has all the property types in it and a 'property_id' which will link it to the rest of the property information, how would I link it to 'buyers_choice'?
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Best way to insert multiple values with checkboxes into db

Post by thinsoldier »

What do you use to generate your chart?

More non-technical advice from me (based on my 4 years of doing real estate projects):

Photos sell. If I told any of my real estate clients that they could only have 5 photos they wouldn't have hired me.

What I did instead was to have a photos table.
id, property_id, filename
(over the years it evolved into the media table)
id, property_id, filename, caption, type (image or document or audio/video or flash)
then I realized the other non-property sections of the site needed photos too so I added a section field and renamed the property_id field to just parent_id.

If I want all the photos for a property I pull from the media table where parent_id = $property_id AND section=properties.
If I want photos for a news article or some other section I change to section=news in the query.

Some would say that having the section field and saving the name of other tables in there suggests I should just have a separate media table for each section.
As in tables named: media_news, media_reports, media_properties, media_sellers, media_buyers, media_realtyagents, etc...

Back to your chart.
I don't really see any benefit in separating sellers from their addresses. But if your sellers_property table fields grow over time like mine did you'd probably consider in the future splitting the sellers_property table into 2 tables, one with the most common, most needed fields and then the rest of the fields in another table.
Fields like:
which administrator created the record
when it was created
when last someone checked the property details were up to date
who last edited it and when
whether it should be featured on the home page
any co-brokers associated with the property
MLS number of the property (more important than your site's local id number)
Rough, general area address of the property itself
GPS coordinates of the property for google maps
Exact block #, lot #, and unit #
various amenities (pets allowed, fully-furnished, free internet, 24hr security, etc)
zoning area
price per square foot
zoning
gross rentable area
lot size (sq ft)
living area (sq ft)
maintenance fees....

If your client is actually a real esatate agency you can expect over the years they'll ask for about 2 or 3 fields like these every 3 months. It's best if you can get a better idea of what they really need as early as possible. The ultimate goal of course being the creation of a web based system that can manage all their information so well that they stop using whatever in-office system they were using and only use the website. Now you've got a client for life.


Your buyers_choice and property_types look like mirror images of each other. If they client wants to add new property types you'd have to make changes in 2 places.

All you need is a property_types table and a table linking property types to the properties and a table linking property types to the buyers preferences.
Although I still say for the buyers preferences you don't need a link table. In that instance you can get away with just a comma separated list of property_type ids saved in a field in the buyers table.

Why is seller_id the primary field of sellers_property?

One of my clients has 23 property types. In the administration area when they're adding a new property they hated having to dig through a long list of 23 things just to tick 3 or 4 of them. It made more sense to have property types in 2 groups.
Residential types (condo, townhouse, triplex, apartment, residential lot),
Commercial types (warehouse, restaurant, office space, office building, parking lot, commercial lot, apartment building, factory).
So on the property types we added a category field.
Warning: I have no idea what I'm talking about.
Post Reply