1

I'm converting from using an Excel spreadsheet to an app with a MySQL database in a pool league I manage.

I understand about eliminating/minimizing redundancy to avoid data corruption. So in a spreadsheet where I have 3 columns for Bonus1, Bonus2 & BonusT, where BonusT=Bonus1+Bonus2, in the table I would just have Bonus1 & Bonus2 and if I want to use BonusT i just calculate on the fly.

But suppose the calculated field is a lot more difficult to calculate, involving data from many other records. For example I'd like to keep track of a player's number of games played in the league because I calculate the change in handicap after every match and do it differently depending on how many matches he has played.

I could add a record to the user_meta table (this is WordPress) for his number of games, and increment it when adding a new match. Or, I could do some sort of nested query to get this info every time.

I'm just wondering how complex the subqueries (or other methods I'm new to this) have to get before "cheating" normal form is justified and becomes appropriate?

3 Answers 3

1

In principle, I'd say that you should avoid "cheating" when it comes to normalization. Focus on 1NF, 2NF, 3NF - these are probably all you need.

I think that - for beginners - the steps for achieving 2NF are hardest to understand. However, if you get this right, you will remove a lot of complexity from your queries.

An example can be found here: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.ddi.doc/ids_ddi_186.htm

Another example, probably more important for you - In the third paragraph of your question, you are describing a particular problem, namely keeping track of the amount of games a player played in a league. If each GAME has one or more PLAYERS and each player can play one or more games, then you need to model (and code) an intersection entity/table. The COUNT() of entries there will give you the amount of games played by a player (quite easily).

Also, it would be a good idea to learn about how to use VIEWS. When using them, you can create virtual tables as it were. Views will allow you to SELECT from them, and they can also be combined (with other views and queries).

Regarding the question in your last paragraph: Considering that you are new to database design, it is probably the "other way round": if your (sub)queries become too complex, you have "cheated" already, and need to amend the model.

If you are working with MySQL, you can use MySQL Workbench for modelling. This software will also allow you to "reverse engineer" ie create a model from DDL code (SQL CREATE and ALTER statements that are used to describe the structure of your database).

2

I suggest you go with the normalized data model and use JOINS. In practicality, sometimes there is no need to drill down to atomic level, you can stop the level of aggregation based on the requirement. Also, if the historical data is important for you, you could consider the historical table instead of updating the meta data information. For this you can refer, Slowly Changing Dimensions (SCD).

If calculation involved complexities, understand the relationship between respective entities and capture them in table. This you can achieve by creating stored procedures or functions.

I hope, this answer will help you to get start. Thank you.

0

So in a spreadsheet where I have 3 columns for Bonus1, Bonus2 & BonusT, where BonusT=Bonus1+Bonus2, in the table I would just have Bonus1 & Bonus2 and if I want to use BonusT i just calculate on the fly.

So t'row when you hv Bonus 3 then wht you gonna do ?

So there will be only one Bonus column. Similarly you keep asking such question on each step.

Yes no need of bonus 3,since calclation is easy you can sum the bonsu column on the fly.

Yes if calculation is complex then you can store the calculated value in column. But it depend upon example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.