Database Design: RETS -> SQL
439 views
Skip to first unread message
mphill
May 21, 2012, 9:59:34 PM5/21/12
to PHRETS
I'm hoping someone can offer some advice to me on this subject. I was
originally going to try to use one table for all property classes but
I am starting to think that is not the best idea as I approach room
data / sizes because none of the columns are reused. Do most people
use a table per property class or are people doing just fine with a
combined table and simply a column to indicate the class type. Any
advice would be greatly appreciated.
I had though of storing all the of the room data as a serialized
object in the database as a possible work around. One limitation I
see of going with a property class per table is if you want to search
for single families and condos at the same time, you would have to use
UNIONS which I would like to avoid.
Thanks.
Michael
originally going to try to use one table for all property classes but
I am starting to think that is not the best idea as I approach room
data / sizes because none of the columns are reused. Do most people
use a table per property class or are people doing just fine with a
combined table and simply a column to indicate the class type. Any
advice would be greatly appreciated.
I had though of storing all the of the room data as a serialized
object in the database as a possible work around. One limitation I
see of going with a property class per table is if you want to search
for single families and condos at the same time, you would have to use
UNIONS which I would like to avoid.
Thanks.
Michael
Jared Ritchey
May 22, 2012, 11:10:18 AM5/22/12
to phr...@googlegroups.com
Michael I'm working on a project right now that I built for a client in the MRED / Chicago market, the database has 125K listings in a single table which has 111 fields.
I keep the GeoCoding in a separate table as well as agent and office details.
The average search time in the quick search is less than a second, and the advanced search with 10 - 15 items selected is about 1 - 2 seconds.
I've built projects with each class in its own database and its certainly doable but not always necessary. Maintenance is certainly easy enough on updates.
As far as serialized data goes, I personally avoid doing so unless absolutely necessary. I built a RETS loader using PHRETS for a project that ultimately requires some data to be stored in a serialized format.
I hope that helps answer your question(s).
--
Jared Ritchey / IDX - RETS Development
I keep the GeoCoding in a separate table as well as agent and office details.
The average search time in the quick search is less than a second, and the advanced search with 10 - 15 items selected is about 1 - 2 seconds.
I've built projects with each class in its own database and its certainly doable but not always necessary. Maintenance is certainly easy enough on updates.
As far as serialized data goes, I personally avoid doing so unless absolutely necessary. I built a RETS loader using PHRETS for a project that ultimately requires some data to be stored in a serialized format.
I hope that helps answer your question(s).
--
You received this message because you are subscribed to the Google Groups "PHRETS" group.
To post to this group, send email to phr...@googlegroups.com.
To unsubscribe from this group, send email to phrets+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/phrets?hl=en.
--
Jared Ritchey / IDX - RETS Development
mphill
May 22, 2012, 4:43:26 PM5/22/12
to PHRETS
I think so. I realize its not specially a PHRETS question but I really
can't think of a better forum to discus in :)
I typically try to avoid serialized information (I usually convert the
array to JSON for maximum portability) but in our MLS none of the room
information reuses column names, and rightly so since a single family
does not have a "unit 2 bed room 1" room. So I have two choices,
create a property room table for each property which will have about
36 columns or take the serialized approach to avoid creating literally
hundreds of new columns. I'm actually starting to thing RETS would
work VERY well with a NoSQL database like Couch or Mongo. I think I am
too far in to switch though - but then again it might be worth it.
Do you display the bedroom data on your websites? I see a lot of
sites that do not, most likely because it is indeed a pain to code
for. If I disallow searching on those columns I could simplly package
up all the room data into a JSON object and just display that when the
property shows up.
Right now I am at 62 columns which I know I have more data to add -
I'm building up around the data instead of diving in and creating an
architectural nightmare. I can do a full import in about 12 minutes.
I am not using LOAD INFILE (its a MySQL thing) for the import. The
part that kills the import time is I am trying to download the hi res
image and create 280px thumbs because our thumbs look.... really bad.
All of my photo data is about 30 gigs.
On May 22, 10:10 am, Jared Ritchey <brokerwebsi...@gmail.com> wrote:
> Michael I'm working on a project right now that I built for a client in the
> MRED / Chicago market, the database has 125K listings in a single table
> which has 111 fields.
> I keep the GeoCoding in a separate table as well as agent and office
> details.
>
> The average search time in the quick search is less than a second, and the
> advanced search with 10 - 15 items selected is about 1 - 2 seconds.
>
> I've built projects with each class in its own database and its certainly
> doable but not always necessary. Maintenance is certainly easy enough on
> updates.
>
> As far as serialized data goes, I personally avoid doing so unless
> absolutely necessary. I built a RETS loader using PHRETS for a project that
> ultimately requires some data to be stored in a serialized format.
>
> I hope that helps answer your question(s).
>
can't think of a better forum to discus in :)
I typically try to avoid serialized information (I usually convert the
array to JSON for maximum portability) but in our MLS none of the room
information reuses column names, and rightly so since a single family
does not have a "unit 2 bed room 1" room. So I have two choices,
create a property room table for each property which will have about
36 columns or take the serialized approach to avoid creating literally
hundreds of new columns. I'm actually starting to thing RETS would
work VERY well with a NoSQL database like Couch or Mongo. I think I am
too far in to switch though - but then again it might be worth it.
Do you display the bedroom data on your websites? I see a lot of
sites that do not, most likely because it is indeed a pain to code
for. If I disallow searching on those columns I could simplly package
up all the room data into a JSON object and just display that when the
property shows up.
Right now I am at 62 columns which I know I have more data to add -
I'm building up around the data instead of diving in and creating an
architectural nightmare. I can do a full import in about 12 minutes.
I am not using LOAD INFILE (its a MySQL thing) for the import. The
part that kills the import time is I am trying to download the hi res
image and create 280px thumbs because our thumbs look.... really bad.
All of my photo data is about 30 gigs.
On May 22, 10:10 am, Jared Ritchey <brokerwebsi...@gmail.com> wrote:
> Michael I'm working on a project right now that I built for a client in the
> MRED / Chicago market, the database has 125K listings in a single table
> which has 111 fields.
> I keep the GeoCoding in a separate table as well as agent and office
> details.
>
> The average search time in the quick search is less than a second, and the
> advanced search with 10 - 15 items selected is about 1 - 2 seconds.
>
> I've built projects with each class in its own database and its certainly
> doable but not always necessary. Maintenance is certainly easy enough on
> updates.
>
> As far as serialized data goes, I personally avoid doing so unless
> absolutely necessary. I built a RETS loader using PHRETS for a project that
> ultimately requires some data to be stored in a serialized format.
>
> I hope that helps answer your question(s).
>
David Kullmann
May 22, 2012, 5:37:49 PM5/22/12
to phr...@googlegroups.com, PHRETS
FWIW I store serialized MySql and deserialized in NoSql and that works great for me.
Serialization is ok for values you don't lookup, you should have some sort of counter cache for rooms already
David Kullmann
Serialization is ok for values you don't lookup, you should have some sort of counter cache for rooms already
David Kullmann
PHRETS
Dec 2, 2015, 1:29:28 PM12/2/15
to PHRETS, michael.j...@gmail.com
I would absolutely build your DB in a relational model. The single Table " Listings" with all property classes is a complete flawed design. Remember in real estate end users are searching the residential data class 90 percent of the time. In a large MLS you could end up with a Listings DB that has over 700 fields and over 300k records. Even with proper Indexing its inefficient and slow as molasses. If you running more then a single DB NOSQL is the way to go - Mongo...
Reply all
Reply to author
Forward
0 new messages