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


2d
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 ?
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
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
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
5
comment Personal and Global messaging database design
You should see my answer to this question on Stack Overflow, where I go into quite a bit of detail for essentially the same requirement: stackoverflow.com/questions/6541302/…
Jun
27
comment Different type of column
@yannis - Hmmm, hard to find a really great link. The terminology is what's used in James Martin's Information Engineering(TM) method. Some others call it a classifying attribute. The topic to read up is Entity Subtyping - this will cover the various ways that you could implement this model physically. The point of the partitioning attribute is to help enforce mutual exclusivity of subtypes. Each subtype is associated with a particular data value in the partitioning attribute.
Jun
26
comment Reviewing relational database tables
You need to think about (or state, if you know them) what your candidate keys are. What is the business key of your students_class table? It looks like it may not be normalized to me. Ask your friend what is the purpose of the additional table? All of the information it contains is already available in the other tables and can be retrieved through query or view.
May
29
comment Optimum design / structure for product data database?
Have a look at my answer to this question, which addresses your requirement for product attribute values: stackoverflow.com/questions/14194841/…
May
29
comment Optimum design / structure for product data database?
Also, see my answer to a very similar question on SO: stackoverflow.com/questions/11779252/…
May
29
comment Optimum design / structure for product data database?
There should be a third table containing attribute names so that a comparison can be made across products. This is called Entity Attribute Value and is generally frowned upon, but I consider it to be the perfect for product catalogs. See: stackoverflow.com/questions/11779252/…
May
29
comment Optimum design / structure for product data database?
This question has been asked before, and not necessarily appropriately, closed as being primarily opinion-based. See dba.stackexchange.com/questions/49432/…
May
29
comment Optimum design / structure for product data database?
possible duplicate of Which schema is better for a shopping project?
May
25
comment Should I split these fields or leave as one?
@LeoKing - Yes, precisely. If you have a separate column for house name then you can easily query that way. If on the other hand you kept everything in one combined name field, then you'd be down to using WHERE name like '%stark%' which is always problematic for various reasons.
May
25
comment Should I split these fields or leave as one?
@LeoKing - You could start with one table and then decide later if you want to create tables for additional details beyond the character, such as books and houses. Keeping these potential "foreign keys" separate from the outset will save you retyping your data later and it will simplify querying the data, such as "show me all the Starks".
May
25
comment Should I split these fields or leave as one?
@LeoKing - Blank space is a problem if you have many, many nulls per row or if you have an enormous number of rows or if you have tiny, tiny hard disks. For a table with a dozen columns and a few hundred rows (or even thousand) the amount of "wasted" space isn't going to be worth creating an elaborate technical work-around. I would say: trade off a few KB of hard disk for much simpler, more maintainable code.
May
24
comment I don't know how to read many to many relationships
It should be noted that the tutorial you cited gets the physical implementation wrong. The relationship from Project to ProjectEmployee should be 1:1,M not 1:0,M. The relationship from the intersection table to Employee is often read "is a".
May
22
comment How do I connect my MS Access database to the LAN?
This is not a duplicate question and it shows lack of understanding of the technology to mark it so. The referenced "original" question has one solution to what this question is asking, but it is by no means the only solution nor even necessarily, depending on the circumstances, a good solution.