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
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
Your senior colleague is a developer, not a data modeller. You are better off starting from scratch, without them. Normalisation is complicated only to those who will not read books. It is fair enough that he makes you think, but some of the issues are absurd. Your numbers: You need to appreciate the differences between … 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
The two most common reasons to denormalize are: Performance Ignorance The former should be verified with profiling, while the latter should be corrected with a rolled-up newspaper 😉 I would say a better mantra would be “normalize for correctness, denormalize for speed – and only when necessary”
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
Keep a friend table that holds the UserID and then the UserID of the friend (we will call it FriendID). Both columns would be foreign keys back to the Users table. Somewhat useful example: Table Name: User Columns: UserID PK EmailAddress Password Gender DOB Location TableName: Friends Columns: UserID PK FK FriendID PK FK (This … 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
Right, the problem is you want only one object of one sub-type to reference any given row of the parent class. Starting from the example given by @Jay S, try this: create table media_types ( media_type int primary key, media_name varchar(20) ); insert into media_types (media_type, media_name) values (2, ‘TV series’), (3, ‘movie’); create table … Read more