I am trying to fetch the Names and Ages of the users who have a maximum of 2 children between the ages stored in 'min_child_age' and 'Max_child_age' in each row.
- No. of children
I tried using the query below. But it does not give the correct answer. When I try to use a COUNT() in the where clause, the SQL becomes invalid.
SELECT DISTINCT Name, Age
WHERE (No_of_children <= 2
AND ((CHILD_AGE1 >= CHILD_MIN_AGE
AND CHILD_AGE1 <= CHILD_MAX_AGE)
OR (CHILD_AGE2 >= CHILD_MIN_AGE
AND CHILD_AGE2 <= CHILD_MAX_AGE)
OR (CHILD_AGE3 >= CHILD_MIN_AGE
AND CHILD_AGE3 <= CHILD_MAX_AGE)
OR (CHILD_AGE4 >= CHILD_MIN_AGE
AND CHILD_AGE4 <= CHILD_MAX_AGE)));
Better normalization would involve adding another table for the parent-children relation, I suppose. But the requirement is such that at any time there would only support a maximum of 4 and my take is that adding another table would make the already 'broad' ER relation broader. Apart from that perspective I am inclined to believe that there would not be further reaching consequences?
Even if we were to allow another table the issue would still persist, would it not and there is a plan to move the later releases onto Hibernate which I think allows for 'one-table-per-class' approach which would allow for this data model and it would also make the associations/mappings simpler?
I would like to hear your thoughts as if not for now I'm sure they would certainly come in handy sooner than later.
Paul is correct, this is a situation where normalization would buy you alot. For example, if you needed to store the names, birthdates, or other information for each child you would have to add 4 columns per field which would get ugly fast. As you've already seen, queries against this data is overly complicated. Performance would likewise be compromised in your current model. Finally, there is the obvious limit that a parent can have only 4 children that a normalized solution would resolve.
Yes, there is a cost associated with moving to a more-normalized data model AND I don't always recommend normalizing every field, but IMHO you've already hit the point it should have been done long ago.
I prefer your new structure although many people would tell you to delete "no of children" since it can be looked up from the child table. Now this is a more interesting normalization question! If writes are far less common then reads (IE, people aren't adding/removing children all that often) it may make sense to have the children count duplicated in this table if its read often since it would avoid the table join.