Should I split the mySQL tables?

Lets take the example from Yelp: http:/开发者_StackOverflow/www.yelp.com/boston

You can see that it's a website with several different categories, each category containing a listing of places. Should I include all the different places/listing in a single table, or let each category have its own tables?

EDIT: this means having tables 'places_restaurants' and 'places_nightlife', instead of just having the single table 'places' and every entry of every different category will be stored in one huge table... Will this affect performance?

One table per category will require that you CREATE a table every time there's a new category. I'd prefer CATEGORY and PLACE tables, with a one-to-many or many-to-many relationship between them.

You should keep all of the categories in the same table and then have a CategoryID which actually maps each category to the specific / desired category. Your application should be built in a way that is inherently extensible which creating tables each time is definitely not.

It depends. You could normalize the database so that all categories are in their own table, and only referred to from other tables by a foreign key. But there are some arguments that performance outweighs normalization, and so it may be beneficial to keep category names both in their own table of record, and also to include a category name column in other, frequently-joined tables.

If you took the second approach, you would need to ensure data integrity by implementing UPDATE and DELETE triggers such that whenever a category changes in the table of record (presumably, not often), that other tables containing copies of category names also get updated.

It still depends on the application ,also, all the categories is a many to many fields with a main table and of course beliving u have some unique columns in each table