Database Schema for RETS Data

[David Kullmann]

Hello,

Currently I'm syncing one table from the CARETS database and it's around 322 columns (the property table). It's getting pretty cumbersome to work with, and, I am going to have to start supporting more than one MLS soon.

I was wondering if anyone here had advice on how to normalize the data from multiple MLS sources. What do you usually do? What do you keep and what do you get rid of?

My initial thoughts are:

A base property table with common property data (beds, baths, sq feet, status, dates, prices, etc).

A second property table containing more detailed information (directions, school district, etc.)

An agent table, which is related to the property table via a selling agent, listing agent, and alt agent

An office table, which is related to the agent table

It is a little tricky to figure out what data should go where for properties. My property table could easily have a hundred fields, or, easily could have 20 fields. Any suggestions? What do you do?

Thanks,

DK

[Jared Ritchey]

Hey DK, here is an example if it helps.
Generally, what I have been doing is individual table per MLS and if the MLS has a large inventory I may opt for tables per property class. Then, if I can get away with it, I'll break up the data into separate tables to try and normalize the DB. However, PHP and MySQL normalization means multiple joins and the more joins the more complex the query can become. I have this nasty little issue now in the ARMLS market where my client has us downloading all data for all classes in addition to two special status types along with a data scraper for city, county and state data. The joins slowed the process down considerably until I went to a single table for the listings separating only a few elements into their own table.

armls_listings
metrolist_listings

mls_agents (agents from each MLS, flagged only by an MLS code)
mls_offices (offices from each MLS, flagged only by an MLS code)
mls_geocoding
mls_conditional (reo, foreclosure, shortsale, featured, portfolio, etc and so on.)
mls_images
mls_media
mls_history

--
You received this message because you are subscribed to the Google Groups "PHRETS" group.
To view this discussion on the web visit https://groups.google.com/d/msg/phrets/-/Duu1173uLMwJ.
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

[David Kullmann]

Jared:

Thanks for the reply - what about separating listing and property data or is there no point in that?

For example:

One property may have multiple listings. The listings contain a superset of data about the property.

properties table --one-to-many--> listings table --one-to-many--> listing history

Have you ever had a problem you think would be solved by making the "property" itself the main table all other tables are related to?

Have you ever had a problem where you need more listing information but don't have it because you can only store one listing per address?

Or, have you ever had duplicate properties show up when there should only be one record showing?

I think if you are only working with Actives you might not have this problem, but I save all statuses (and show what I can based on the MLS rules)

-DK

David Kullmann

Cofounder, Master Deal Analyzer, Technology Samurai

http://www.flipcomp.com

[Jared Ritchey]

David, email me directly so we don't use PHRETS group to discuss things not related to PHRETS in here.

My email is brokerw...@gmail.com

J

[ahstanford]

Hi David and Jared,

I am supremely interested in the conversations that went on privately
regarding this topic. I am working on designing a database structure
for the same purposes and your insights would be greatly appreciated.
Would either of you be kind enough to forward the resulting email
thread to me?

Thanks in advance,
Alex

On Mar 20, 1:05 pm, Jared Ritchey <brokerwebsi...@gmail.com> wrote:
> David, email me directly so we don't use PHRETS group to discuss things not
> related to PHRETS in here.
>

> My email is brokerwebsi...@gmail.com

>
> J
>
>
>
>
>
>
>
>
>
> On Tue, Mar 20, 2012 at 9:53 AM, David Kullmann <da...@flipcomp.com> wrote:
> > Jared:
>
> > Thanks for the reply - what about separating listing and property data or
> > is there no point in that?
>
> > For example:
>
> > One property may have multiple listings. The listings contain a superset
> > of data about the property.
>
> > properties table --one-to-many--> listings table --one-to-many--> listing
> > history
>
> > Have you ever had a problem you think would be solved by making the
> > "property" itself the main table all other tables are related to?
>
> > Have you ever had a problem where you need more listing information but
> > don't have it because you can only store one listing per address?
>
> > Or, have you ever had duplicate properties show up when there should only
> > be one record showing?
>
> > I think if you are only working with Actives you might not have this
> > problem, but I save all statuses (and show what I can based on the MLS
> > rules)
>
> > -DK
>

[David Kullmann]

Send me an email with your questions and Id be happy to help

David Kullmann

[Wade Berlin]

LOL - he did.

On Apr 25, 2012, at 9:36 am, David Kullmann wrote:

Send me an email with your questions and Id be happy to help

David Kullmann

Thank you for your time,

Wade Berlin

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

BrokerIDX Networks

Creative,Affordable, Cutting Edge Technology for the Real Estate Professional

www.brokeridxsites.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Jared Ritchey]

Sorry did not get back to you, been hammered with work lately.
What did you need in the way of examples;


Jared Ritchey / RETS IDX Development

--
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.