6,262 reputation
1821
bio website mooseware.ca
location Mississauga, Canada
age 51
visits member for 2 years, 11 months
seen Jul 20 at 11:35

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


Dec
27
comment Best practices for ranking records in flexible way
What is the ranking based on? Is the ranking arbitrary? If so, you need to store the ranking on the product table. Is the ranking based on something intrinsic to the product (like alphabetical by vendor, product number, or ascending price)? If so you already have something stored. Is the ranking based on something you can calculate (like frequency of purchase or user views)? If so you need to track that data in detail somewhere and then decide if you want to summarize it periodically for performance reasons. You need to give more information about your requirements if you expect an answer.
Dec
27
answered Choosing the correct database type for single type of entry
Dec
23
comment Shopping cart schema with multiple product tables
This is known as "entity-subtyping" and is the appropriate way to handle the OP's situation. The product table in such a scenario is often referred to as a SKU (Stock Keeping Unit).
Dec
9
comment Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?
Those who argue for natural keys typically do so from a position of philosophical purity, as is the case in the Simple Talk article you cited. In my (25+) years of experience, people who argue from this position are long on education and short on real-world experience. In the real world just about every natural key I've ever come across is subject to duplication and/or redefinition. There are very few cases outside of smallish code tables where it is practical to take the philosophical high ground regarding natural keys.
Dec
9
answered Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?
Nov
30
comment Database design for threading messages
@Benjamin - Depending on what your database engine is, you probably have some kind of automatically incrementing integer type column which you can use for surrogate keys like THREAD.ID. Look into your database engine's documentation for terms like IDENTITY or AutoIncrement. Thread could have other attributes too, depending on what you want. The sample data I used was deliberately minimal for clarity. The point of the THREAD table is to give a header that all of the messages in the thread can share. A thread class may not have any members other than child collections and an ID.
Nov
30
answered Database design for threading messages
Nov
14
comment Does my ERD account for my business logic
@Ciwan - I'm not sure you understood my point about resolving your m:n relationship into a full-blown intersection table. Please see my updated answer with an Entity Relationship Diagram for what I'm suggesting.
Nov
14
revised Does my ERD account for my business logic
Added ERD to clarify the advice.
Nov
12
comment Does my ERD account for my business logic
@Ciwan - A separate table for CC, BCC and TO would probably be a bad idea. Instead you should have a RECIPIENT table with a foreign key to CUSTOMER a foreign key to EMAIL and a TYPE column with values of {"TO", "CC", "BCC"} (or codes of your choice). The primary key of the RECIPIENT table would be all three of these columns. Using one table makes it much easier, for example, to find all of the emails sent to one customer, since you only have to read one table, not three.
Nov
8
comment Does my ERD account for my business logic
@Ciwan - Different template: how do you know what template is used by an email? There is no template table for the email to point to. How do you know what information is in the email template? (i.e. what data-driven fields?) Where is the list of available fields? Where is the mapping of fields to templates? Re: point C. storing multiple delimited strings in a big string field probably does not conform to the rules of normalization and is probably a bad practice, except in certain specific circumstances (which are documented well on this site and are beyond the scope of a comment).
Nov
6
answered Does my ERD account for my business logic
Nov
4
comment Relation that combines entries with their subsequent entries, in order of date
@Imray - I'd suggest googling around for "correlated subquery relational algebra" - Someone's probably figured it out.
Nov
4
answered Relation that combines entries with their subsequent entries, in order of date
Oct
29
comment SQL Server: Database integration and synchronization
@ced - I'm suggesting you do away with database B and just keep one database (A - your data warehouse).
Oct
28
comment SQL Server: Database integration and synchronization
My suggestion is that you create views in the main database instead of using tables in multiple databases. This would allow many users to have distinct subsets of your master data without having two data sources to get out of sync.
Oct
26
answered SQL Server: Database integration and synchronization
Oct
25
comment Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
@zdxai - You're welcome. We're all here to learn and help each other. If you're not comfortable yet with how to use the site and what the expectations are, I suggest you check out the FAQs, they're short and helpful.
Oct
25
comment Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
@zdxai - There is no data structure that can prevent a user saying a Prius is a Pickup Truck, but 3NF can prevent your database from the likelihood of these kinds of errors by limiting the number of times anyone says what type of vehicle a Prius is to just one record. If you had to do it on every Car record, then the likelihood of some records being right and some being wrong would be much higher. Consistency minimizes data entry, which also minimizes the chance of bad user input. Re: duplicate model names: yes a PK has to be unique, by definition.
Oct
25
comment Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
@zdxai - As long as your tables have a PK, it won't matter for the exercise whether the PKs are natural or surrogate (i.e. a string or a sequential number). You can make the names the PKs of their respective tables if you like, or you can make the keys integers to avoid the issue that David Cummins pointed out in his original answer.