Table Structure Suggestions

[chadsten]

I am working on a site for a real-estate company, and we will be using both a 'homes' and 'neighborhoods' section. These will be directly related to each other. The users will be able to view these homes by price, size, etc., as well as viewing all homes in a certain neighborhood, and viewing neighborhoods, as well as viewing neighborhoods by attributes like ISD(school district), HOA, etc.

So, its pretty simple, and I am equipping it with CMS. But that comes later. So..

The table structure idea that I have now is as follows.

TABLE.homes_base
table stores basic info(as well as neighborhood ID that it is "in". about the home that will be used as 'handles' by the system. Will have a primary key, matching the table...
TABLE.homes_extra
table stores blob text, as well as other info that the system doesn't necessarily 'use'.
TABLE.neighborhoods
stores data that will be used by the system
TABLE.neighborhoods_homes
stores the primary ID, and name in a row for each home it 'has'.

That is my basic outline for it, I am just looking for suggestions, ways to make it better, more efficient, etc. It will probably need to be able to handle around 500 homes at once, possibly more. I don't think anywhere near the 5k range or anything. Thanks in advance!!

[NogDog]

Not sure I see a need for the separate "extra" table, unless there's a possibility of a many-to-one relation ship between it an the "base" table. If not, I would probably just have a single "homes" table, making those optional fields allow nulls.

For the neighborhoods_homes table, I'm not sure what you mean by it having a "name" field. I would think all it would need is two columns, one containing a primary key from the homes table and another from the neighborhoods table. (It would probably then have a 2-column primary key index.)

[chadsten]

The HOMES table will contain several large bodies of text, so I thought it would increase the speed to save the TEXT type on a separate table...let me know if I am wrong...Thank!

[svidgen]

Unlike other data types, MySQL handles each BLOB and TEXT value as an object with its own identity. Storage engines often store them specially; InnoDB may use a separate "external" storage area for them when they're large. Each value requires from one to four bytes of storage space in the row and enough space in external storage to actually hold the value.
(Baron, Zaitsev, Tkachenko, Zawodny, Lentz, and Balling. High Performance MySQL, 2nd ed. 2008, O'reilly)

So, if you really need a TEXT field, there should be no problem storing them with the main table.

[NogDog]

The only problem with multiple large fields in the same table would be if you get lazy and do a "SELECT * FROM..." instead of an explicit list of column names when you do not actually need one or more of those large fields, resulting in the unnecessary retrieval of the data from those fields. But since we're never lazy and always use explicit column lists of only the needed fields in our queries, that would never be an issue, right? ;)