[thelist] DB design question

Luther, Ron Ron.Luther at hp.com
Mon Dec 9 09:23:01 CST 2002

Hi Guys,

Sorry to jump in late.  It's been a pretty interesting thread so far.
There are certainly a lot of different ways to store 'properties' and
relate them back to some parent entity.

[One 'solution' I've run across in the past was when I was working for
a company that used a 100 bit binary field to store 'amenities'.  On
the reporting side, I had to 'decode' the stored value and know that
for product "a", a "1" in bit 38 meant that it had a radio modem
certified for use in the UK for example.  Not a real elegant solution,
but one way to do things.]

A couple of somewhat random notes on the real estate example that's
been under discussion:

(1) I don't think these items are usually entered "free form"; e.g.
'amenity 1' - 'description 1'.  I think they're usually entered using a
pretty standard form.  There is a reason for this.  The data entry is
done by the realtor agency - so it's gotta be pretty simple.  ;-)

(2) Home buyers and investors, the folks using the entered data, need to
see the output in a standard format as well.  If your search criteria
returns 50 properties to review, you don't want to have to search each
printout with a magnifying glass to see how many bedrooms there are or
whether the garage is attached or detached.  You need a fairly standard
output format as well.

Because of those two items, I'm not seeing a real need for an 'amenities'
table at all.  I think I'd use predefined fields for all of the standard
information ----- and add a text field or two to allow space for the
listing agent to write in things like "heliport" or "Tiffany windows".  I don't think
that needs to be in a separate field.

How do these people get paid?  They get paid to facilitate change of
ownership transactions on homes/housing/estates/property.  Is an agent
going to tell a prospect "Sorry - I search for 'heliport' - there aren't
any for sale right now - try back in six months."?  I don't think so.
I think they'll run a search for 'lot size > 2 acres', call a contractor
friend to get a ballpark price for putting a heliport in, and tell the
prospect they have half a dozen potential properties to go look at.

Having looked at a lot of MLS form outputs over the last 2 years I'll
definately agree that there is a lot of garbage and incompleteness in
the data ... as well as outright errors and possible fabrications.

{Duplicate Listings} That was an interesting issue about 'border'
properties.  Any decent buyer's agent will run the search criteria for
both counties and present the results to the prospect.  The issue comes
into play on the seller's agent side - they want that listing to show
up in a search for 'county x' even though it's across the street in
'county y'.  Even though, in some instances, this might be a good thing
I think it would be discouraged due to the potential for abuse.  Taken to
an extreme, this behavior would destroy the value of the database.

So how is it prevented?  My suspicion is that it's not.  I would guess
that it's handled by "business rules" rather than technical design. If
realtor "A" at Century 21 catches realtor "B" at ReMax entering a listing
multiple times ... I think they 'turn them in' and they (and their
agency) get fined.  I'm pretty sure there are a number of these "business
rules" -- like marking a listing as 'sale pending' within 48 hours of
receiving an offer ... those kinds of things.  And this 'self-policing'
definately goes on in my area - especially since MLS is a shared resource.

Anyway, the point of the second half here (I guess) is that everything
doesn't have to have a technical solution.

Hope that didn't go too far out of bounds,


-----Original Message-----
From: Ken Schaefer [mailto:ken at adOpenStatic.com]

: > : and so the question is, what's to stop the table from having two rows
: with
: > : the exact same address?
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > You could create a data domain constraint. :-)

More information about the thelist mailing list