database-design
Maintaining Referential Integrity – Good or Bad?
When discussing auditing, I would go back to the purpose behind it. It isn’t really a backup but rather a history of what has been. For example, for StudentScore, you would want to be sure not to lose the fact that the student originally had a 65% when they now have a 95%. This audit … Read more
How are bitmap indexes helpful?
A better representation of a bitmap index, is if given the sample above: Identifier Gender RowID 1 Female R1 2 Male R2 3 Male R3 4 Unspecified R4 5 Female R5 the a bitmap index on the gender column would (conceptually) look like this: Gender R1 R2 R3 R4 R5 Female 1 0 0 0 … Read more
Do link tables need a meaningless primary key field?
I would use composite key, and no extra meaningless key. I would not use a ORM system that enforces such rules on my db structure.
Why use a 1-to-1 relationship in database design?
From the logical standpoint, a 1:1 relationship should always be merged into a single table. On the other hand, there may be physical considerations for such “vertical partitioning” or “row splitting”, especially if you know you’ll access some columns more frequently or in different pattern than the others, for example: You might want to cluster … Read more
How do I implement threaded comments?
Storing trees in a database is a subject which has many different solutions. It depends on if you want to retrieve a subhierarchy as well (so all children of item X) or if you just want to grab the entire set of hierarchies and build the tree in an O(n) way in memory using a … Read more
What are the principles behind, and benefits of, the “party model”?
What are the core principles and motivating forces behind the party model? To the extent that I’ve used it, it’s mostly about code reuse and flexibility. We’ve used it before in the guest / user / admin model and it certainly proves its value when you need to move a user from one group to … Read more
What is the difference between 3NF and BCNF?
The difference between 3NF and BCNF is subtle. 3NF Definition A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in 3NF if for each functional dependency X ⟶ A in R, at least one of the following … Read more
Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well? [closed]
I’ve recently discovered DBeaver. It automatically detects relations between tables in a visual manner. You can move tables around, change colors, see foreign keys, etc. It’s extremely good. Fully compatible with PostgreSQL Querying and manipulating data using the GUI is extremely easy. Not only that you can use it with all major SQL and noSQL … Read more