Joel Brown

4,260 reputation

1619

bio	website	mooseware.ca
	location	Mississauga, Canada
	age	50
visits	member for	1 year, 9 months
	seen	16 hours ago
stats	profile views	135

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

	4,260 reputation	bio	website	mooseware.ca	visits	member for	1 year, 9 months
1619 badges		location	Mississauga, Canada		seen	16 hours ago

summary answers questions tags badges favorites bounties reputation activity

166 Comments

suggestions reviews revisions comments badges posts accepts all

Jun 10	comment	Help with Multi-Restaurant Menu database design I think item should be a top-level entity, i.e. it should have no foreign keys. Also, attributes (and foreign keys to item) which depend on the specific restaurant should be moved to `menu_category_item`. The price of an item depends on the restaurant. It also depends on the category and menu. Salad as an appetizer is less expensive than salad as a meal. Also ratings of food depend on the restaurant.
Jun 10	comment	Help with Multi-Restaurant Menu database design Thanks for expanding your question, but it still doesn't ask about specific concerns or describe the business rules you are trying to model. It will be difficult for people to give advice without a more specific information about what you need to achieve. Some questions I have include why are you storing calculated values on `item_rating` instead of actual user rating transactions? How is the delivery timing supposed to work, especially if hours are split into lunch/dinner? What do you do if different restaurants have different prices for the same item? (hint: is `menu_category_item` 3NF?)
Jun 10	comment	Help with Multi-Restaurant Menu database design Welcome to DBA.StackExchange. Is your model good enough for what? We don't know your business rules so it is not possible to comment or critique. Please read the FAQ (dba.stackexchange.com/help) especially with respect to asking questions. Do you have some specific concerns with your model?
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.
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 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	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 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	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.
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 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 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.
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 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 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.
Mar 18	comment	Multiligual database design @ChrisTravers - I thought about that too, but I went with the assumption that URL has a language element. Still, if that were easily predictable (like a specific query string component) then for sure it would be better to normalize it out.