CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

Need Advice on building the database. all in one table or split?

i am developing an application for a real-estate company. the problem i am facing is about implementing the database. however i am just confused on which way to adopt i would appreciate if you could help me out in reasoning the database implementation.

here is my situation.

a) i have to store the property details in the database.

b) the properties have approximately 4-5 categories to which it will belong for ex : resedential, commnercial, industrial etc.

c) now the categories have sub-categories. for example. a residential category will have sub category such as. Apartment / Independent House / Villa / Farm House/ Studio Apartment etc. and hence same way commercial and industrial or agricultural will too have sub-categories.

d) each sub-categories will have to store the different values. like a resident will have features like Bedrooms/ kitchens / Hall / bathroom etc. the features depends on the sub categories.

for an example on how i would want to implement my application you can have a look at this site.

http://www.magicbricks.com/bricks/postProperty.html

i could possibly think of the solution like this.

a) create four to five tables depending upon the categories which will be existing(the problem is categories might increase in the future).

b) create different tables for all the features, location, price, description and merge the common property table into one. for example all the property will have the common entity such as location, total area, etc.

what would you advice for me given the current situation.

thank you

Upvotes: 1

Views: 239

Answers (3)

markus
markus

Reputation: 40675

In order to implement this properly you need to know (read) about database normalization. Every entity needs its own table. You will have tables for:

  • objects (real estate objects)
  • categories
  • transactionTypes
  • ... etc.

If you have hierarchical categories, strictly organised in a tree structure, you may want to implement this as a tree structure, all stored in one table. If there are possibilities of overlaps, then it means you need to have different tables for each, like:

  • propertyTypes
  • propertyRatings
  • propertyAvailability
  • ... etc.

Upvotes: 2

Walter Mitty
Walter Mitty

Reputation: 18940

Categories of properties is yet another example of the gen-spec design pattern.

For a prior discussion on gen-spec here is the link.

Upvotes: 0

jondavidjohn
jondavidjohn

Reputation: 62392

Generally you could have a table for each property "type" containing the "type" specific information but also have a corresponding "common" table that would contain common fields between all types such as "price", "address", etc...

This is how MLS data is structured.

Upvotes: 0

Related Questions