Reputation
7,110
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
1 9 23
Newest
 Explainer
Impact
~151k people reached

Jun
23
answered Can a database detect that data is out of sequence if each datapoint has a sequential ID
Jun
14
answered Auto-increment key and Foreign Key as composite for relational database
Jun
9
answered User roles and permissions
Jun
4
comment Am I wrong in table design or wrong in selected index when made the table?
@achy - Your solution shows that the lack of a selective index on stat_valid and mark_as_trash is the culprit in your performance issue. I would assume you were getting a table scan because of this, which can really slow things down, especially if it's done too soon in the execution plan.
Jun
4
answered Am I wrong in table design or wrong in selected index when made the table?
Jun
3
answered financial transactions with and without commissions
May
27
answered Record versioning and promotion
May
21
awarded  Constituent
May
20
comment Database design: Two 1 to many relationships to the same table
@dendini - You solution number 2 doesn't fit with any of the solutions I outlined. This is because it doesn't fit with the requirement of an account belonging to one legal entity. The way you've defined the primary keys of the intermediate tables, they are many-to-many intersections. If the primary keys were just corporation_id and person_id then you would essentially have the sub-typing solution, except that the super-type table would have been split into two and the foreign key will have been inverted, so people couldn't hold multiple accounts. This kind of defeats the purpose.
May
18
answered Database design: Two 1 to many relationships to the same table
May
16
answered Table Design for user-specific and user-agnostic criteria
May
14
comment How to share resources among accounts?
@zx_wing - Have a look at my expanded answer to see if it helps with matching the design to tables.
May
14
revised How to share resources among accounts?
Expanded answer to include ERD and recommendations for implementation.
May
14
answered How to share resources among accounts?
May
14
comment Database Design: separating reusable & instance data
What are you going for with subcategories? Is the idea that if you pick a category all of its descendants come along automatically, but sometimes you want just a subcategory and not a whole category? Also, how were you planning on distinguishing between a template and an instance of a checklist in your second model?
May
13
awarded  Caucus
May
3
comment Many:Many with Shared Relation
@Shannon - A candidate key should include only those columns needed to determine the non-key columns. Adding composer_id to a surrogate key in Composition and Anthology breaks this rule. IF composition_id for example, weren't an IDENTITY but just unique within composer_id then everything would be OK. As far as denormalization goes, PK columns propagate to child tables. This is not denormalization. Propagating non-key columns to child tables is denormalization. So either way you're breaking or at least bending a rule. This is why I said the solution was controversial.
May
2
revised Many:Many with Shared Relation
Simplified based on comments.
May
2
comment Many:Many with Shared Relation
@Shannon - You want to have the composer be part of the composite primary keys on each branch so that the composer key is propagated to the intersection table. You could always denormalize it anyway, but keeping it in the PK brings DRI to bear on the problem. You are quite right that there don't need to be two distinct composer keys, although that is what you'd get if you were using a modeling tool to gen your schema. Answer edited to remove the red herring.
May
2
comment Many:Many with Shared Relation
@Shannon - Please see my expanded answer to address your question.