I'm designing mysql database for a php app, which has two sections: adding advertisement for properties like house shops.. , and searching for them.
I have tables for users, addresses, ads...
The problem is properties table. I don't know if I should split that up into multiple table or should I have one big table? Columns are too many. I have up to 100 fields, like:
Property type: house, shop, fields... Each property type has some fields like bedrooms number for house, and other fields like air conditioning system, facilities, equipments..
What should I do? Should I divide this table into smaller tables?
I'd recommend to use a bridge table. You can see on this Link what the bridge tables are.
Generally, you have 2 choises which are both schema-design:
Using just one table (named property) and add bulk data to it. In this case, fields are your features (like air_conditioning, bedroom numbers and so on). By defining these field as boolean and denoting 1 for "The property has this feature" and 0 for "The property has NOT this feature", You can do what you want.
Using a simple bridge table which is a table that connects the property table to the features which you should define for property items.
Actually the second approach is an applicable approach in database design. Because it is scalable i.e. You can add every new feature every time!
Hope this explanation would help you!