CODEWITHSUNDEEP
mysqlsqldatabase-design
Sourav
Sourav

Reputation: 17520

Blogging Article Table Structure - MySQL

I created 2 tables for storing Article info.

KB_Parent

KB_Detail

I created the KB_Parent table for searching and listing latest article purpose and KB_Detail table for showing detail about the article.
As KB_Parent will be used for searching only i'll impement MyISAM FullText and for the other table InnoDB

Am i correct about my table or just a single Table can solve the job ?

I avoided using foreign key so i don't have to run inner query or join to fetch result

Upvotes: 1

Views: 729

Answers (2)

Gareth
Gareth

Reputation: 936

You didn't explicitly say this in the question, but I am assuming you want to go with this design for the following reasons:

  • the text field is huge and you expect to need to return a large number of rows when displaying summary data (or even worse you need to do a table scan sometimes to get the list of summary rows). You are trying to skip the unnecessary reads of the text field for performance reasons.
  • MyISAM supports full text search whilst InnoDB does not, thus you use MyISAM for the table where you will use full-text search.

I would verify whether assertion 1 is really correct. If the summary queries are likely to use a single index and there are likely to be a small number of rows returned, then assertion 1 isn't correct. Just collapse to one table.

If assertion 1 is correct and the performance difference is substantial, then yes, it would be better to have two tables. However, if you are using two tables, I would avoid MyISAM as it does not support transactions (i.e. you could get into a state where only one of the two tables exists if there was an error on the insert) and it doesn't support foreign keys. Whilst InnoDB does not natively support FULLTEXT search there are open source external open source FULLTEXT search engines you can use (like Sphinx).

And if you do use two tables, I don't see why you should repeat the data. If someone selects to see the details of a row, you will already have the Title/Author etc. from the summary row (so you shouldn't need to do a JOIN query).

I also don't see what you have against foreign keys. They will allow you to main referential integrity between the two tables. The only real cost of foreign keys is when you add/delete/modify rows (an extra lookup by index is required to make sure the row in the other table exists). And when you need to do a query when you join the two tables, it isn't expensive (as the database can look up the second table via the primary key obtained from the first table -> most likely joining the two tables via a nested-loop join).

Upvotes: 1

zidarsk8
zidarsk8

Reputation: 3348

If the only difference between those two tables it the "Text" field, you should just use the KB_Detail table. There is no benefit to using both those tables.

Upvotes: 1

Related Questions