CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Babiker
Babiker

Reputation: 18798

Have actual but duplicate data in table or reference its id and run two queries or a more complex query?

enter image description here

Given the previous tables, i am selecting names of kids and their favorite quotes. In terms of speed, efficiency, and database design best practices, which is better? Should i have the actual quote in table kids or should i not have the actual quote but reference its id then do another query to fetch it from quotes table?

Upvotes: 1

Views: 105

Answers (4)

JNK
JNK

Reputation: 65147

It seems like a waste of space to duplicate what appears to be a thousand+ length varchar field more than once.

Normalization of this structure wouldn't really affect performance, and it may actually speed up queries: those very long quotes are going to cause all kinds of page fragmentation in your kids table.

Keeping a separate quote table will make it easier to manage and perform better overall.

EDIT:

To answer the question in the comment, you need a quote_id field in Kids to JOIN on:

SELECT k.Name, q.Favorite_Quote
FROM kids k
LEFT JOIN quotes q
    ON q.id = k.quote_id

Upvotes: 0

Ryan
Ryan

Reputation: 1888

For me it depends on how the data is being used.

For example if the information is needed on each page like

Hey Timmy, we see you're still 8 and your favorite quote is "Action is the blah blah blah"

Then I would go for a single query, but, if the information was only going to be used on a specific page or you want say a quotes page then use multiple queries.

I hope this helps.

Upvotes: -1

cwallenpoole
cwallenpoole

Reputation: 81988

Personally, I find it easier to use ID's, pretty much whenever possible. Imagine needing to change a Lincoln quote and having to disable the foreign keys to do it. Then, when you've updated the quotes table, having to update and re-enable the key on the kids table... I've dealt with that in a legacy system, and I wanted to scream.

But that's just me.

Upvotes: 2

patapizza
patapizza

Reputation: 2398

I'd rather go with the quote's reference id than duplicating the favorite_quote field.

Upvotes: 1

Related Questions