¿Cómo diseño una base de datos para almacenar propiedades, seleccionando atributos por sinónimos?

Estoy diseñando una base de datos para una aplicación de bienes raíces. Está demostrando ser más complicado de lo que había anticipado (tal vez estoy complicando las cosas).

Los problemas se deben esencialmente a la presencia de:

  • sinónimos Por ejemplo, los términos: piso, apartamento y penthouse pueden referirse esencialmente al mismo tipo de propiedad
  • atributos (diferentes tipos de propiedades tienen diferentes atributos) Por ejemplo, un apartamento podría ser una planta baja o piso superior, etc.

Terminé con un árbol de clasificación elaborado (no intencionalmente) para diferentes tipos de propiedades. Los nodos de árbol son las instancias reales de los tipos de propiedad.

Quiero crear una base de datos para que pueda consultar utilizando no solo ninguno de los sinónimos, sino también los atributos.

Entonces, por ejemplo, la consulta (en pseudo SQL):

SELECCIONE * desde propiedades donde sinónimo = “plano” y atributo IN (“planta baja”, “jardín”);

debe devolver una lista de apartamentos | pisos que son planta baja Y tienen un jardín.

¿Alguien puede ayudarme a diseñar el esquema de la base de datos para permitir el tipo de consultas descrito anteriormente?

Por último, pero no menos importante, usaré MySQl o PostgreSQL como base de datos back-end, pero preferiría que el enfoque sea db agnostic, si es posible.

Tomaría un enfoque diferente a su esquema de atribución. En lugar de tratar diferentes atribuciones como sinónimos, las trataría como superposiciones, o más específicamente, descripciones anidadas de una propiedad. Esto manejaría su caso comercial al mismo tiempo que reconoce la astuta observación hecha por Mike Sherrill.

Aquí hay un boceto rápido de ERD:

ERD

Por medio de un diccionario de datos muy rápido:

PROPERTY es una propiedad inmobiliaria.

CATEGORY es una colección de atributos descriptivos. El objective de esta tabla es más como un organizador de atributos que cualquier otra cosa. Podría incluir cosas como “tipo de propiedad”, “estructura de propiedad”, “cantidad de baños” y cualquier otra cosa que pueda ser de su interés.

ATTRIBUTE es una cualidad de interés específica. Tenga en cuenta la relación involucionada en este tipo de entidad. Trataré más con eso más tarde. El punto principal es que los atributos pueden ser más generales o más específicos y algunos atributos se pueden ver como refinamientos de otros atributos.

DESCRIPTOR es la intersección de una PROPIEDAD y los ATRIBUTOS que se han asociado con esa pieza particular de bienes inmuebles.

Entonces, ¿cómo se supone que esto ayuda?

La clave es cómo funcionan los atributos. Si usa un modelo de conjunto nested , puede abordar criterios de atribución y búsqueda más o menos específicos. Considere el siguiente diagtwig de una CATEGORÍA potencial con sus ATRIBUTOS asociados:

enter image description here

En este ejemplo, la CATEGORÍA es “tipo de propiedad”. Puede ver en el diagtwig que hay un desglose jerárquico de atributos en esta categoría. Cada cuadro en el diagtwig es un registro en ATRIBUTO. Los cuadros que contienen otros cuadros tienen atributos secundarios. Las cajas que están dentro de otra caja tienen un FK en su caja contenedora y así sucesivamente.

De esta manera, podría decir “Quiero encontrar una propiedad que sea un ático”. A continuación, puede encontrar los registros de PROPIEDAD con un DESCRIPTOR relacionado que señala en el ATRIBUTO “Penthouse”. Eso es bastante fácil. ¿Pero qué ocurre si su búsqueda aparece vacía?

La ventaja de este enfoque es que puede aflojar sus criterios diciendo: “vamos a subir la jerarquía de atribución a la siguiente cosa menos específica que penthouse”. En mi ejemplo, eso sería “Highrise”. Ahora prueba tu búsqueda nuevamente y es posible que tengas mejor suerte.

Un sistema como este le brinda la capacidad de ser tan específico como lo desee en cada categoría de atribución, mientras que relaja los otros lo suficiente como para comenzar a obtener resultados de búsqueda. De esto se trata realmente el trabajo de un agente inmobiliario, ¿no es así? ¿Ayuda al cliente a hacer los compromisos necesarios para encontrar el mejor ajuste a sus criterios más importantes?

Manejo de conjuntos nesteds

La única parte difícil de este enfoque es cómo manejar los conjuntos nesteds. Hay muchas formas de hacerlo, muchas de las cuales han sido documentadas exhaustivamente en otros lugares. A mí me gusta la técnica del número de visitas , especialmente para conjuntos de datos relativamente estáticos. Esto hace que sea muy fácil encontrar coincidencias para un ATRIBUTO dado o cualquiera de sus hijos sin tener que hacer nada exótico en su SQL.

EDITAR: Entonces, ¿cómo funciona esto?

OP preguntó cómo manejas cosas como la cantidad de habitaciones y cómo son las consultas. Tomemos otro ejemplo para la ilustración:

Ejemplo de dormitorio

Lo anterior muestra los conjuntos nesteds para la CATEGORÍA “Número de Dormitorios”. También agregué los números de visita al diagtwig. Tenga en cuenta la forma en que funcionan los números de visita, en particular, tenga en cuenta que los números de la izquierda (verde) y derecho (rojo) para cualquier valor de atributo dado contienen los números de visita izquierda y derecha para cualquier atributo subordinado. Por ejemplo, “2+ dormitorios” tiene números izquierdo y derecho 6 y 15 respectivamente. Cada atributo que corresponde a “2+ habitaciones” tiene números de izquierda y derecha que se encuentran dentro de este rango.

Entonces, ¿cómo consultarías las propiedades con un descriptor dado? Digamos que queremos encontrar todas las propiedades con dos o más habitaciones. El SQL para una consulta así podría verse más o menos así:

 select P.* from PROPERTY P inner join DESCRIPTOR D on P.id = D.property_id inner join ATTRIBUTE A on D.attribute_id = A.id where A.left >= (select X.left from ATTRIBUTE X where X.name = '2+ Bedrooms') and A.right <= (select Y.right from ATTRIBUTE Y where Y.name = '2+ Bedrooms') 

Tenga en cuenta que la consulta anterior es un poco diferente de lo que realmente podría usar. Por ejemplo, probablemente busque el atributo de filtrado utilizando su clave de identidad int en lugar de su nombre de cadena. Sin embargo, pensé que lo dejaría como se muestra para mayor claridad sobre el punto principal, que es el filtro al buscar no un atributo específico relacionado, sino también cualquier atributo relacionado que se encuentre dentro de su rango de filtro.

Si desea filtrar en múltiples atributos, simplemente agregue más sub-cláusulas a su cláusula where.

Para manejar sinónimos, puede tener una búsqueda de muchos a muchos entre una tabla que contiene la lista estática de sus tipos de propiedad, y una tabla que contiene el sinónimo. De esta forma, un sinónimo podría asignarse a más de un tipo de propiedad.

Por ejemplo:

 Table:Property Type 1 House 2 Appartment 3 Large House 4 Cave Table:Synonym 1 house 2 flat 3 dwelling 4 condo 5 mansion Table:PropertyType-Synonym 1 1 (House is a house 1 3 (House is a dwelling) 2 2 (Appartment is a flat) 2 3 (Appartment is a dwelling) 2 4 (Appartment is a condo) 3 1 (Large House is a house) 3 3 (Large House is a dwelling) 3 5 (Large House is a mansion) 4 3 (Cave is a dwelling) 

Para las propiedades, puede utilizar un tipo de estructura de atributo abierta.

Por ejemplo:

 Table:Property 1 Apartment F, Field House Gardens 2 123 Alphabet Street, NumberTown Table:Attribute 1 Is ground floor? 2 Number of bedrooms 3 Has garden? Table:Property-Attribute-Values 1 1 No 1 2 2 1 3 Yes 2 2 5 2 3 Yes 

Espero que esto ayude