6,412 reputation
1921
bio website mooseware.ca
location Mississauga, Canada
age 52
visits member for 3 years
seen 9 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


Jan
20
comment Normal form for a relation where the number of tuples having the same value in a column is the same for all the possible values in that column
Normal forms are about how columns in a table relate to each other (and to columns in other tables). Normal forms don't address how rows relate to each other.
Jan
13
comment Best way to handle column that doesn't conform to one particular type
@lee - There are lots of good books - too many to be on topic for StackExchange. Try Googling around for technical books that include the word "Cookbook" in the title. That's a good indication that it's a collection of patterns/applications/best practices.
Jan
13
answered Best way to handle column that doesn't conform to one particular type
Jan
10
comment Store last elements in a dedicated table
Having less data doesn't necessarily mean it will be significantly faster. You have to think about all of the extra reading and writing that your idea would involve. A properly indexed read is extremely fast, even in huge tables. It is a mistake to assume poor performance without actually testing it out.
Jan
9
comment Benefits of using Staging Database while designing Data Warehouse
This question is cross-posted at stackoverflow.com/questions/21010045/…
Jan
9
comment Inventory Management DB Design
@IanAuld - I've expanded my answer as you requested. NOTE: You do not want to use your Shelf_Contents table, unless you mean to allow a bin to sit on two shelves simultaneously! It is enough to have the Shelf_ID on the Bins table. Each bin sits on exactly one shelf but each shelf can hold many bins.
Jan
9
revised Inventory Management DB Design
Expanded answer to include info from comments as per OP's request.
Jan
9
answered Benefits of using Staging Database while designing Data Warehouse
Jan
8
comment Inventory Management DB Design
@IanAuld - It's important to get the terminology clear. When you say "bin" you mean a box without a lid that sits on a shelf. In a lot of inventory situations, a "bin" is a space on a rack where you could put a skid or one or more shelves. As long as we're clear, you want a hierarchy of SHELVES each of which has many BINS each of which can have many BIN_CONTENTS each of which is an intersection between BIN and ITEM and which has a quantity. Don't store quantity on your ITEM table, store it on the intersection table, that way you can have multiple bins of the same item.
Jan
7
answered Inventory Management DB Design
Jan
4
answered How does this database design look?
Jan
2
answered Deciding when to extend to child tables
Jan
2
answered creating a historical database, need help making a decision
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?