Within this situation, I am focusing on a task which involves a database of library book data. I've several tables within this database:
Books (book_id, title, description, genre_id, language_id, purchase_date, sections, words, book_review_count)
BookSearchData (book_id, Fulltext-searchable game titles and explanations)
Genres (genre_id to genre_title)
Language (language_id to language_title)
CheckoutData (book_id, last_checkout_date, checkout_user_id)
A little of table-construction rationale: I made separate Books and CheckoutData tables because during my setup, I'll be making frequent demands for that CheckoutData information and will not need anything apart from the information incorporated within the CheckoutData fields. Hopefully it was the proper way to go.
The commonest data request, however, is a book data summary, which presently involves JOINs between all the tables. A magazine result includes the next information:
- Genre Title
- Language Title
- Purchase Date
- Review Count
- Examined? (Which is dependent upon set up worth of checkout_user_id equals within the CheckoutData table row connected using the book)
My question: Do you consider it might be useful (performance-smart) basically produced another table within this database that incorporated an easy two-colum setup composed of the book_id along with a book_data_summary?
Itidentification could be, as you'd imagine, some type of integer. Itinformationoverview will be a area of type TEXT.
The issue when i view it...this introduces lots of data redundancy. Additionally, it implies that each time that the book is examined (Table: CheckoutData) or maybe a magazine review is added with a user (Table: Books), I'll should also update itinformationoverview area.
Is worth doing?
Edit: I have to clarify something. Within this situation, I am doing a bit of a higher-speed library scenario (yes, that appears type of silly, but it is a bit of an experimental project). The CheckoutData table will probably be receiving lots of card inserts - often (we are speaking...a couple of hundred each minute). And Book results is going to be asked for around exactly the same rate, with possibly 10 or 20 book records being asked for every time.
So A lot of checkouts, in addition to A lot of demands for book summaries.
(we are speaking...a couple of hundred per minute)
a minute each minute, about 10 card inserts per second. That's not so much, a database are designed for 1000's of card inserts per second.
For those who have a question having a performance problem, begin using EXPLAIN to determine what's going on and why situations are slow.
I do not just like a two column setup, but a denormalized version from the primary query (e.g. produce a view with all of 5 tables became a member of properly, after which place after that into another table) might have real benefits.