meta user
network profile
| bio | website | mooseware.ca |
|---|---|---|
| location | Mississauga, Canada | |
| age | 50 | |
| visits | member for | 1 year, 9 months |
| seen | 15 hours ago | |
| stats | profile views | 132 |
I'm a professional software developer with more than twenty years of experience across many industries and the entire systems development lifecycle. I'm the principal consultant at Mooseware Limited.
|
2d |
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. |
|
May 2 |
revised |
Many:Many with Shared Relation Expanded with diagrams and two alternative approaches depending on business need. |
|
May 2 |
answered | Many:Many with Shared Relation |
|
Apr 27 |
awarded | Nice Answer |
|
Apr 27 |
answered | Database for opening times of locations |
|
Apr 27 |
answered | Defining system scope and boundary |
|
Apr 25 |
revised |
One to at most one relation or multiple null columns? Expanded answer to take into consideration extra information provided by OP. |
|
Apr 25 |
answered | Is it better to use multiple one-to-many relationships, or polymorphic? |
