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


11h
comment How to store user's language knowledge (150+ languages) in mysql DB
Correlated sub-query is the way to go as far as clarity of code is concerned, but are your sub-queries correlated? Shouldn't it be more like: from dbo.user A and WHERE user_id = A.user_id ?
1d
reviewed Approve suggested edit on DB2 Materialised Query Table performance problems
2d
awarded  Yearling
Aug
23
comment Is one-to-one relationship normalized?
@Gili - re: the cost of joins: There is no right answer to that question aside from "it depends". Join cost is impacted by many factors. Whether they are negligible is even harder to answer, because that is ultimately subjective. The best way to answer you question is to mock up some test data and do volume testing. Try it both ways and see if you can tell the difference using real world data volumes (whatever that implies for your application).
Aug
22
comment Is one-to-one relationship normalized?
@Gili - If your assumption were true then this case wouldn't apply. Segregating large and infrequently needed columns allows more rows to fit on a page, thereby allowing faster retrieval of the commonly used columns. Obviously reading the segregated columns along with the commonly used columns would be slower since a join is necessary.
Aug
12
answered What's the use of assigning foreign keys on MySQL
Aug
6
answered Split date time, with only time, into minutes and seconds
Aug
4
reviewed Approve suggested edit on SSIS - Multiple data source and single excel destination, causing SqlDumper error
Aug
4
reviewed Approve suggested edit on sql-server-2012 tag wiki
Aug
4
reviewed Approve suggested edit on sql-server-2005 tag wiki
Aug
4
reviewed Approve suggested edit on IF IS NULL then SINGLE else keep the value of the field
Aug
4
reviewed Approve suggested edit on select rows where column contains same data in more than one record
Aug
2
answered Messaging Database - How to handle multiple recipients
Aug
1
comment Struggling with normalisation (database design critique)
The way you've pictured it works, as long as clients and sites can potentially have more than one contract and contracts are only ever for one site or one client. You also need to add constraints or logic to enforce that a contract is for either a client or a site. If these cardinality rules aren't what you intend, then you might need to change your design.
Jul
30
comment Conceptual model vs Logical model vs Physical model
What you're referring to on the Wikipedia page is the DDL (data definition language) that defines the physical schema in minute detail. DDL is one way to express the physical model - probably a very good way, since it is very precise. However, it is not the only way. You can express a physical model graphically, for instance.
Jul
28
comment Conceptual model vs Logical model vs Physical model
I don't think you're on the right track, exactly, especially with the conceptual model. See this answer for something which holds up pretty well, I think: stackoverflow.com/questions/4279089/…
Jul
28
reviewed Approve suggested edit on Decode Function in Oracle
Jul
14
revised Enforce referential integrity between two groups of data
Corrected spelling for clarity, especially in the title to improve searchability.
Jul
14
comment Enforce referential integrity between two groups of data
The problem is that a foreign key needs to reference all of the columns in the key. You are trying to reference part of a candidate key. Obviously that doesn't make sense. If your intersection table is truly referencing just the group IDs, then you need to normalize the rule and action tables to make their group IDs unique without reference to other columns.
Jul
12
reviewed Approve suggested edit on what is '#' in create table statement?