How to do this structure efficiently

[jaswinder_rana]

Hi:

I was trying to create a small realty app. I am now stuck at how to design tables for all the possible information about a property.

A screenshot of fields needed is at the bottom of this post.

- I am not sure how to deal with all those Radio buttons.
- How to store information about different properties because it maybe a vacant lot, hence no need for all that information

Thanks

[r937]

i would do it with two tables for the properties

one main table with PK = property id, attributes = address, etc.

id, address, datelisted, listingagent
21, 123 sesame st, '2009-12-28', 'Todd'
22, 456 main street, '2009-12-29', 'Biff'

second table holds features, with PK = { propertyid, feature } and feature could be a character column holding meaningful words

21, '220 Volt Outlet'
21, 'Breakfast Nook'
22, 'Wall-to-wall carpeting'

then i would use a 3rd table to register all possible features, so that each feature indicates which section the feature belongs under (i.e. kitchen, flooring, yard)

[Kyle W]

Quick suggestion. I'm assuming yo can have more than one type of flooring. Why not have a drop down menu with an option to add addition drop down menus for multiple selections. Would take up less space, less code to handle all those radials as well.

Before displaying table, ask if lot is vacant or not. Then depending on that answer this table may not need to be displayed. AJAX would go great here

[jaswinder_rana]

Thanks. This will for admin panel only as only site owner will be able to add a property.

I tried to consolidate above 2 posts and this is what I came up with.

table_feature_types
id    name
1     Kitchen
2     Bathroom


table_features
id    id_feature_type(FK)   name
1     1                            marble sink
2     2                           Shower
3     2                           tiled flooring


table_property_types
id     name
1      detached
2      semi-dearched
3      vacant   (May need to hard code it in app so as not to show table for this property type)


table_property_for (NOT SURE about table name)
id      name
1       Sale
2       Rental

table_properties
id  address    datelisted   id_property_type(FK)           id_property_for(FK)
1   address1  2007           1                                        1
2   address2  2008           2                                        2

table_property_features
id_property(pk,fk)     id_feature(pk,fk) 
1                            1
2                            1
2                            2

Then, I can generate radio buttons OR multiple selection for EACH feature type and then DROP ALL entries for that property (if editing; 1 statement) and then copy the newly selected ones.


Is that the right way to go??

OFF TOPIC: Should I create separate table for things like "property status (Sold or available; just an example)", "property type" OR should I just give some numbers and hardcode them in my app??