Database organisation

Just thought I'd ask on here for database organisation advice,

What i've got is a table called "tblVillas" which will have villa's inside it, with fields such as name, description, location, bedrooms, bathroom, etc.

Thinking of the future though, we might expand to hotels, caravans, cottages etc and would want to search through all these at once.

I was going to change "tblVillas" to "tblListings", so everything can be stored in there if we ever did expand, and I could put in a relationship ID to say if its a villa, hotel, caravan, etc. Would this be a sensible thing to do?, or would it be better to keep them in seperate tables and create a dynamic table with join's if we ever expanded?

If we did expand it would be when that table would have 1000's of villa's in it, so I'm thinking keeping them seperate to improve speed would be better, but would like the opinions of other people that already manage huge databases.

Thanks

If all the different types of properties will end up having a majority of the same set of attributes, then you would basically put them all in 1 table and using a "type" column to identify what type of listing each row is.

Then you can have create type-specific table if there are type-specific attributes.

For general searching, if you can put all the information for search result page in the common table, then you will not need to join any table for type-specific data until when your customer is interested in a particular listing. (PK access)

For type-specific search, you will have to join, but if you can index the "type" column in the common table.

In general, common DB engines should be able to handle 1000's rows in a table without any problem and with minimal DB config tuning, IF you develop optimized SQLs

Thanks for that, really helpful

I've amended my database and PHP to put everything together in the one table.

In addition to what ahk2chan said, it depends on what your site is going to do. If you are going to threat villas, hotels etc. absolutely separately (i.e. separate search, different views etc), you can as well keep them in separate tables to save yourself from the hassle of joins.

But if there is some common data you'd better keep them in one table and put extra data in related tables.

If there will be any kind of search or sort that will be done on all object, then using one "listings" table is absolutely needed.

bobby: you have completely contradicted yourself.

I think a database can do joins more efficiently rather than searching all the diff tables for info esp if there will be huge amount of rows. (correct me if i am wrong)

Very late response, but anyway: you will be able to do joins only if you have one table for an anchor. (i.e. "listings"). Then the related data can stay in different tables (you'll have a painful query though). If your tables are completely unrelated, good luck getting search-able and sortable data from them at once.