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


Feb
24
reviewed Approve suggested edit on Getting the exact edited data from a column in SQL Server
Feb
20
answered Database throughful design vs Quick and dirty design comparison
Feb
8
answered Why does data get corrupted in “large” text files - Reasons to use database
Feb
7
answered Recipe Health risk Database ERD structure
Jan
30
comment Table design for departments with duplicate department numbers
@jeff - The issue is about normalization based on functional dependencies. For a logical model you probably would have separate director and VP tables. That doesn't mean your physical model needs them. It may be, based on what you say about employee-department mapping that the PK of department is a composite of dept_id and manager_id. That would also work and it might solve your problem of how to join employee in. I assume your employee table has a manager_id and a dept_id so that would solve your problem.
Jan
30
answered Table design for departments with duplicate department numbers
Jan
26
comment What's a good way to model user authorization to hierarchical data?
@a_horse_with_no_name - True enough, although I think there is room for discussion about how easily anything can be done with CTEs and there's nothing difficult about maintaining nested sets. I think adjacency list vs. visitation number is really more a matter of personal preference. I expect one could argue endlessly about their relative merits, which they surely have, but in the end it will come down to one's particular situation or priorities.
Jan
25
answered Db Design of a Room Booking System
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