How to split a large data table to some small data tables?

Question

I'm trying to make some classifieds website. I've found some open source classifieds project: osclass.

So I'm about to design the database structure with reference to osclass. osclass's database structure is designed like this:

The category items data table has been divided into 7 small data tables like :

oc_t_item: Stores some base item information.
oc_t_item_comment: Stores the comment for item.
oc_t_item_description: Stores title, content for the item.
oc_t_item_location: Store the location information for the item.
oc_t_item_meta: Store some extra meta information for the item.
oc_t_item_resource: Store the image resources information for the item.

But I don't think this would be wise movement though it makes the logic and structure clear. If we'd like to retrieve some information about some category item, we'd have to do so much join operations between these data tables, which would result in a great performance penalty. So, what is the basic principles and best practices to split some large data table into some small data tables?

oc_t_item defines the primary key, that is item_id. The others take this primary key as a foreign key. — David, Aug 06 '13 at 18:05

score 1 · Accepted Answer · edited May 23 '17 at 11:50

You shouldn't need to split tables like that just out of concern for joins. If the tables related use primary keys for FKs or indexes with the correct columns, the data retrieval would be quite efficient. See this post and the comments+answers, for an example of how the query analysis should be used to make sure your queries are using the right indexes, refs, etc.

Also, if you're going to split tables to move things like location (a varchar field?) out of the table, then are the fields you are left with fixed-width? If not, the data-retrieval speed benefit you get from moving out one variable width column is lost if there are other variable width columns still left in the table, like item_name.

(On a side note, see this answer about the type of optimizations ppl consider doing to improve performance.)

How to split a large data table to some small data tables?

1 Answers1