6,287 reputation
1821
bio website mooseware.ca
location Mississauga, Canada
age 51
visits member for 2 years, 11 months
seen 10 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


Dec
28
comment Best practices for ranking records in flexible way
In addition to the "organic" ranking criteria that apply automatically to all 10,000 records you can also have an override ranking for some products, which takes precedence over the organic rank if it exists. This could be stored on the product table and maintained manually or it could be stored in another transaction table and be edited automatically based on a payment page - there are lots of options. Your question is much too vague to get an answer on DBA.SE. You need to give much more specific information about your problem is you expect specific suggestions.
Dec
27
answered Sql schema design advice
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.