CODEWITHSUNDEEP
mysqldatabasedatabase-design
Razvan M.
Razvan M.

Reputation: 407

Is having duplicate Database values better than querying more times?

Consider a table called users and a table called votes. A user has an id and a country column. Every vote belongs to a user, but the purpose when retrieveing the vote is to find out which country it came from. Therefore you would need to query once to get the vote, and query the users table after that to get country.

Considering a large, many-times queried database, Is it better to just add a country column for the votes table and have it be a duplicate for the one in users or to just use the method above?

Upvotes: 0

Views: 51

Answers (3)

TommCatt
TommCatt

Reputation: 5636

The way you have explained it, country is an attribute of user: user "lives in" or "is citizen of" a country. Vote is an action that may be taken by users: users cast votes.

How is it that you have a vote under consideration without already knowing the user? How was this vote selected in the first place? There must be some other detail(s) that you have omitted.

If you are searching for aggregate values ("How many votes were cast by Canadians during July?") then you have to join the tables anyway -- filtering on users only in Canada and votes only during July. A query for "In which countries did any citizens cast at least one vote in July?" would be trickier to code, but still requires a join.

The join needed by the latter question could be eliminated by duplicating the country to the Votes table. But I don't think any performance improvement would be significant and you must remember that you will have made your database a little more complicated, a little less maintainable and a little less robust. It would have to be quite a large performance boost to make all that worthwhile.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Yes. No. Maybe.

The answer to your question depends on several things that you don't mention in the question. The first thing to note is that the query in VKP's answer is quite sufficient under most circumstances.

Second, if country is a full country name, then storing the full country name (which can be rather long) may greatly expand the size of the table. This increase in size may actually slow down certain queries, versus doing the join. Of course, this would be much less significant for 2- or 3- character codes or if the width of the records in votes is already several hundred bytes.

But, perhaps the most important consideration is whether you want the vote counted on the users current country or do you want the vote counted at the country assigned to the user when the vote was made? The first option says to always use a join to get the current value. The second is a very strong argument for including country in the votes table.

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select v.vote_id, u.country
from users u join votes v
on u.id = v.userid

If you need to see the country from which a vote was, you can join the tables and get it. Also, it is not suggested to include a country column in the votes table as it doesn't make sense.

Upvotes: 1

Related Questions