6,855 reputation
1923
bio website mooseware.ca
location Mississauga, Canada
age 52
visits member for 3 years, 5 months
seen 5 hours ago

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.

@joelabrown

google

facebook

linkedin


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?
Apr
25
answered One to at most one relation or multiple null columns?
Apr
24
comment Is this table in 3NF?
@MikeSherrill'Catcall' I guess it depends a little bit on your perspective. I've worked in address data quality (amongst many other things) for nearly two decades. Having dozens of Springfields could be considered an argument in favour of city_id. While the string token "SPRINGFIELD" doesn't determine the state, the actual municipality called Springfield (whichever one it happens to be) does. Whether or not that functional dependency matters will vary from system to system. If it doesn't matter in a particular system, then I couldn't agree with you more, city_id is be a bad idea.
Apr
23
comment Is this table in 3NF?
@MikeSherrill'Catcall' - The argument goes that country depends on state and state depends on city. Since the table shows these columns as being the key columns of the presumed geography entities, you're quite right that the strict definition of 3NF is preserved. However, for practical purposes address data is notoriously bad (spelling/typos/etc). The OP's table doesn't need to have geography normalized out as per this answer, but it probably should if the intention is to do any analysis on geography - not because 3NF demands it, but for the sake of sanity.
Apr
16
answered Database schema design help needed
Apr
14
comment Db design for historical option data (~200GB)
@ChrisSaxon - Quite so, it isn't denormalization technically. I sometimes fall into the trap of using the term in a less correct way as many others seem to do. The term fits (loosely) insofar as it involves deliberately introducing data redundancy for the purpose of improving performance. I think the problem is that we don't have a good jargon term for "keeping an extra copy of something for performance purposes" as opposed to the more technical definition of denormalization (copying non-key fields from parent tables to child tables).
Apr
14
answered Db design for historical option data (~200GB)
Apr
7
answered Adding users to a list and sort by category?
Apr
3
comment Handling expiration in a course registration or ticketing system
@MattB. - Your queries look fine, notwithstanding any minor issues of SQL syntax. Depending on which RDBMS you're using there may be a couple things to sort out, but the ideas are good. I know the views look a bit complex, but the objective is to move the head scratching into the design and testing phase and to keep the ongoing data maintenance as simple as possible while keeping the details around to be able to handle new or ad hoc requests.