CODEWITHSUNDEEP
mysqldatabase-design
Demonslay335
Demonslay335

Reputation: 796

Combined or Separate Tables

I have a current database structure that seems to split up some data for indexing purposes. The main tickets table has more "lite" fields such as foreign keys, integers, and dates, and the tickets_info table has the potentially larger data such as text fields and blobs. Is this a good idea to continue with, or should I combine the tables?

For example, the current structure looks something like this (assuming a one-to-one relationship with a foreign key on the indexes):

`tickets`
--------------------------------------------
id | customer | vendor | opened
--------------------------------------------
 1 |       29 |      0 | 2013-10-09 12:49:04

`tickets_info`
--------------------------------------------
id | description           | more_notes
--------------------------------------------
 1 | This thing is broken! | Even longer...

My application does way more SELECTs than INSERTs/UPDATEs, so I can see the theoretical benefit of the splitting when large lists of tickets are queried at once for an overview page (250+ result listings per page). The larger details would then be used on the page that shows just the one ticket and its details with the simple JOIN (amongst the several other JOINS on the foreign keys).

The tables are currently MyISAM, but I am going to convert them to InnoDB once I restructure them if that makes any difference. There are currently about 33 columns in the tickets table and 4 columns in the tickets_info table; the tickets_info table can potentially have more columns depending on the installation ("custom fields" that I have implemented akin to PHPBBv3).

Upvotes: 1

Views: 64

Answers (1)

mucio
mucio

Reputation: 7119

I think this design is fine. The tickets tables is used not only to show single tickets information, but also to do calculation (i.e. total of tickets sold in a specific day) and other analysis (How many tickets sold that vendor?).

Adding the tickets_info will increase the size of you tickets table without any benefits but with the risk to increase access time to the tickets table. I assume good indexing on the tickets table should keep you safe, but MySql is not a columnar database, so I expect that a row with big varchar or blog fields requires more resources.

Beside that if you use the ticket_info for single ticket queries I think you already get good performance when you query that table.

So my suggestion is leave it like it is :)

Upvotes: 1

Related Questions