6,780 reputation
1923
bio website mooseware.ca
location Mississauga, Canada
age 52
visits member for 3 years, 3 months
seen 12 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


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.
Apr
3
comment Handling expiration in a course registration or ticketing system
@MattB. - Your answer satisfies the two main guidelines that I've discussed.
Apr
3
comment Handling expiration in a course registration or ticketing system
@MattB. - I can't say for sure how a PAYMENT table might be different from your sale_header table. They might be the same thing, depending on how your table is actually used. My advice is to have a table that reflects as closely as possible the real-world activity of money coming in and out. It's good that you are logging data before deleting it, but that is less desirable than keeping it around in the first place and using queries or, as Erwin Smout suggested, views to filter completed registrations and incomplete registrations. This simplifies data maintenance and troubleshooting.
Apr
3
answered Handling expiration in a course registration or ticketing system
Apr
1
revised How do I design a database for a resource scheduling and allocation application?
Expanded to show queries to answer statistical questions using date range design
Apr
1
revised How do I design a database for a resource scheduling and allocation application?
Expanded again to include query information per OP's question in comments.
Mar
31
comment How do I design a database for a resource scheduling and allocation application?
@vzhernovoi - I've edited my answer to show what the ALLOCATION table would look like if you wanted to enforce the "one project at a time" rule through declarative constraints. If you have multiple resource types then you should probably have one allocation table per resource type table. There are other ways around this but it would involve making design decisions that could have unintended negative consequences. Google around for the term "entity-subtyping" for a discussion of how to track multiple types of an item in a relational database.
Mar
31
revised How do I design a database for a resource scheduling and allocation application?
Expanded answer to reflect follow-up question from OP in comments.
Mar
31
answered How do I design a database for a resource scheduling and allocation application?
Mar
20
comment In what data type should I store an email address in database?
To look at it from another perspective, perhaps you might want to make a business decision to say that you just don't want to do business with anyone whose email address is longer than 80 characters, because they are probably nuts.
Mar
19
answered Best relational database structure for this data
Mar
18
comment Multiligual database design
@Thomas - There could be an important difference between things you want to publish, e.g. product information, and things that your users (or third parties) supply to your systems, like messages. You can control what you publish but you can't force users to pick a language, or even to use just one, even in a single message. Unless you plan on doing a machine translation of user messages they will only ever be in the language the user chooses anyway. For user-supplied data you may be better off just ignoring the language. It will be what it will be.