I'm working on a real estate website, and am struggling to define a design for my DB. Database needs to store Properties, attributes, property category/subcategory, and have them be linked all together. I could easily solve said problem by hard-coding property categories/subcategories in code, but would like to have it be more dynamic.
Now, I want to have categories table having ID and category title, then subcategories table having ID, title and ID of a category it belongs to, so a rather basic manytoone relationship.
I also need a more dynamic approach to attributes, so that I have one table, with ID, title and subcategory_id, where I'll limit attributes so that they can only be used for properties in certain subcategories.
With this said, I'm having an issue figuring out how would I form a table for properties, so that it stores all the properties and its attribute/value pairs.
NOTE: I'm doing this in Django.
PostgreSQL has at least two possibilities to implement dynamic properties:
Second approach seems to have less performance issues but combining SQL and JQuery-like in the code may be hard to maintain. First approach is appropriate for relatively small data volumes (100K-1M objects, up to 10 attributes) because of known performance problems.