Another option might be array_remove(…, NULL) (introduced in 9.3) if tags.tag is NOT NULL (otherwise you might want to keep NULL values in the array, but in that case, you can’t distinguish between a single existing NULL tag and a NULL tag due to the LEFT JOIN): SELECT objects.*, array_remove(array_agg(tags.tag), NULL) AS tags, FROM objects … 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
Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption. There is a number of normalization levels from … 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
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
Your pizza can have exactly three topping types: one type of cheese one type of meat one type of vegetable So we order two pizzas and choose the following toppings: Pizza Topping Topping Type ——– ———- ————- 1 mozzarella cheese 1 pepperoni meat 1 olives vegetable 2 mozzarella meat 2 sausage cheese 2 peppers vegetable … Read more
A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be “unknown” at the same time (in which case … Read more
Some more answers to your questions: 1) You’re pretty much on target for someone who is approaching a problem like this for the first time. I think the pointers from others on this question thus far pretty much cover it. Good job! 2 & 3) The performance hit you will take will largely be dependent … Read more
1NF is the most basic of normal forms – each cell in a table must contain only one piece of information, and there can be no duplicate rows. 2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said … Read more
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems: Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5 Can’t use foreign key constraints to link values to a lookup … Read more