The main reason is to eliminate repetition of data, so for example if you had a user with multiple addresses and you stored this information in a single table the user information would be duplicated along with each address entry. Normalisation would seperate the addresses into their own table and then link the two using … Read more
I have a few fairly simple rules of thumb I follow when designing databases, which I think can be used to help make decisions like this…. Favor normalization. Denormalization is a form of optimization, with all the requisite tradeoffs, and as such it should be approached with a YAGNI attitude. Make sure that client code … Read more
A philosophical answer: Sub-optimal (relational) databases are rife with insert, update, and delete anomalies. These all lead to inconsistent data, resulting in poor data quality. If you can’t trust the accuracy of your data, what good is it? Ask yourself this: Do you want the right answers slower or do you want the wrong answers … Read more
Generally speaking, data integrity is more important than performance, so denormalize1 only if you have performed measurements on representative amounts of data and the results indicate a strong need for better performance. More often that not, a normalized schema will perform just fine, especially if you got your physical design right (such as indexing). In … Read more
SELECT * FROM t1 WHERE JSON_EXTRACT(data,”$.series”) IN … Using a column inside an expression or function like this spoils any chance of the query using an index to help optimize the query. The query shown above is forced to do a table-scan. The claim about “efficient access” is misleading. It means that after the query … Read more
If all the candidate keys of a relation contain multivalued attributes: Introduce a surrogate attribute for at least one multivalued attribute. For each attribute you deem “composite” (having heterogeneous components, like a tuple): For each attribute component that can be missing: Add a relation with attributes of some multivalue-free candidate key and an attribute for … Read more
Database Normalization is all about constructing relations (tables) that maintain certain functional dependencies among the facts (columns) within the relation (table) and among the various relations (tables) making up the schema (database). Bit of a mouth-full, but that is what it is all about. A Simple Guide to Five Normal Forms in Relational Database Theory … Read more
It’s true that it’s not a good idea to denormalize into JSON, but sometimes you need to deal with JSON data, and there’s a way to extract a JSON array into rows in a query. The trick is to perform a join on a temporary or inline table of indexes, which gives you a row … Read more
We write S -> T and say that a set of columns S functionally determines another set T. S -> T in a table value when each subrow for the first always appears with the same subrow for the second. S -> T in a table variable/schema when S -> T in every value that … Read more
Another common Name for this model is the Supertype Model, where one has a base set of attributes that can be expanded via joining to another entity. In Oracle books, it is taught both as a logical model and physical implementation. The model without the relations would allow data to grow into invalid state and … Read more