{"id":322,"date":"2004-09-15T12:22:46","date_gmt":"2004-09-15T04:22:46","guid":{"rendered":"http:\/\/foxpro.ntsl119.com\/scr\/?p=322"},"modified":"2007-10-29T11:59:08","modified_gmt":"2007-10-29T03:59:08","slug":"database-normalization-part-001","status":"publish","type":"post","link":"https:\/\/foxpro.ntsl119.com\/scr\/archives\/322","title":{"rendered":"Database Normalization Part 001"},"content":{"rendered":"

This is a screenshot of the prototype of a new project of ours.<\/p>\n

<\/p>\n

To a give you a short overview it is a database program that monitors the children records in a certain place where parents can ‘safely’ leave their children so they can shop with comfort and peace of mind. <\/p>\n

These children will then unite with the other ‘temporarily abandoned’ children in that place to battle the forces of evil and squash the invading twelve-headed-iodine-squirting aliens while their parents comfortably shop for shoes and perfumes.<\/p>\n

(How I wish someone would come up with the idea for something like this where instead of children, the husbands are the main clients while their lovely wives do the shopping thing… (I would even wholeheartedly create a database program for free if that business pops up))<\/p>\n

Some of these children obviously do not win in this enormous battle so a database program is needed to monitor who came back ‘alive’ and who came back ‘replaced’. The TimeIn and TimeOut records of these brave kids are necessary to compute the wormhole coordinates use for their battle portals.<\/p>\n

Let us leave those alien battle-invasion details for now and proceed.<\/p>\n

If you can remember I posted this yesterday:<\/p>\n

If you are to choose between the second level of data normalization or an easier usable interface, what would you prioritize?<\/font><\/p><\/blockquote>\n

Going back to the prototype screenshot above, you see a very simple interface. There are textboxes and combo boxes for the regular information this application needs: ChildIDNO, ChildName, Nickname, Birthdate, FatherName, MotherName, Guardian, ParentsAddress, ParentsPhoneNumber to name a few.<\/p>\n

Easy flowing, right?<\/p>\n

Probably yes… IF you’re creating something and don’t give an aliens arse after you get paid. If you’re one of those irresponsible ‘database dudes’ out there who deserve to be devoured by sex-cravin’-tentacle-infested aliens.<\/p>\n

If you choose the easy path you’ll get a lame database that looks like this (let’s just select the important fields):<\/p>\n\n\n\n
_KIDS DATABASE<\/strong><\/font><\/td>\n<\/tr>\n
\n\n\n\n\n\n
ChildIDNO<\/font><\/td>\nChildName<\/font><\/td>\nFather<\/font><\/td>\nMother<\/font><\/td>\nEtc…<\/font><\/td>\n<\/tr>\n
0000001<\/font><\/td>\nX<\/font><\/td>\nFatherofX+Y<\/font><\/td>\nMotherofX+Y<\/font><\/td>\n <\/td>\n<\/tr>\n
0000002<\/font><\/td>\nY (X’s bro)<\/font><\/td>\nFatherofX+Y<\/font><\/td>\nMotherofX+Y<\/font><\/td>\n <\/font><\/td>\n<\/tr>\n
0000003<\/font><\/td>\nZ<\/font><\/td>\nFatherofZ<\/font><\/td>\nMotherofZ<\/font><\/td>\n <\/font><\/td>\n<\/tr>\n<\/table>\n<\/td>\n<\/tr>\n<\/table>\n

That is even the picture in the best case scenario. Re-entering the parent data again for another kid with the same parents would yield possible errors… extra spaces and more open for typo-related errors.<\/p>\n\n\n\n\n\n\n
For example:<\/font><\/td>\nFoo, John R.<\/font><\/td>\n<\/tr>\n
Can be entered as: <\/font><\/td>\nFoo, John <\/font><\/td>\n<\/tr>\n
 <\/font><\/td>\nFoo, John R<\/font><\/td>\n<\/tr>\n
 <\/font><\/td>\nFoo, John <\/font>_<\/font>R. (an extra space)<\/td>\n<\/tr>\n
 <\/font><\/td>\nFoo, Jonh R. (and countless versions of typos)<\/font><\/td>\n<\/tr>\n<\/table>\n

If Child_001<\/font> and Child_002<\/font> are even encoded to the database a number of days apart, the possibility of an error to occur increases. (Let’s say after a month when the second child, learning that one of his sibling was abducted, pledges to go on in a quest to rescue his big brother from aliens.)<\/p>\n

(By the way… one of the target readers for these blogs of mine are the ComSci\/IT students of our country who needs ‘database enlightenment’; for those who have been experts of this field already, you are free to move your arse out of here (for now) and read other more advanced stuff instead… probably about Source Control HowTo<\/a> or How windows exploit the hyperthreading technology<\/a>.)<\/p>\n

Now if you’ll look back to the fields above, ParentID, FatherName and MotherName, ParentAddress, ParentsPhone and the other Parents-related information are redundant. A definite no no in the area of database structure planning.<\/p>\n

So the first ‘level’ of improvement on this is to create a separate database for Kids and a different database for Parents. Something like this: <\/p>\n\n\n\n
_KIDS DATABASE<\/strong><\/font><\/td>\n<\/tr>\n
\n\n\n\n\n\n
ChildIDNO<\/font><\/td>\nChildName<\/font><\/td>\nEtc…<\/font><\/td>\n <\/td>\n <\/td>\n<\/tr>\n
0000001<\/font><\/td>\nX<\/font><\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n
0000002<\/font><\/td>\nY (X’s bro)<\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n<\/tr>\n
0000003<\/font><\/td>\nZ<\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n<\/tr>\n<\/table>\n<\/td>\n<\/tr>\n<\/table>\n\n\n\n
_PARENTS DATABASE<\/strong><\/font><\/td>\n<\/tr>\n
\n\n\n\n\n\n
ChildIDNO<\/font><\/td>\nFather<\/font><\/td>\nMother<\/font><\/td>\nEtc…<\/font><\/td>\n <\/td>\n<\/tr>\n
0000001<\/font><\/td>\nFatherofX<\/font><\/td>\nMotherofX<\/font><\/td>\n <\/td>\n <\/td>\n<\/tr>\n
0000002<\/font><\/td>\nFatherofX<\/font><\/td>\nMotherofX<\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n<\/tr>\n
0000003<\/font><\/td>\nFatherofZ<\/font><\/td>\nMotherofZ<\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n<\/tr>\n<\/table>\n<\/td>\n<\/tr>\n<\/table>\n

The KIDS database holds fields that are related to… well kids and the PARENTS database for PARENTS-related information such as contact number and the likes.<\/p>\n

Now… is that enough improvement and normalization already..?<\/p>\n

While it is better than the first table it is not that good enough yet. You can still see redundant records in the PARENTS table.<\/p>\n

You can improve this further by creating another field PARENTS_ID on the KIDS table linked to the same field in the PARENTS table.<\/p>\n\n\n\n
_KIDS DATABASE<\/strong><\/font><\/td>\n<\/tr>\n
\n\n\n\n\n\n
ChildIDNO<\/font><\/td>\nChildName<\/font><\/td>\nParents_ID<\/font><\/td>\nEtc…<\/font><\/td>\n <\/td>\n<\/tr>\n
0000001<\/font><\/td>\nX<\/font><\/td>\n0000001<\/font><\/td>\n <\/td>\n <\/td>\n<\/tr>\n
0000002<\/font><\/td>\nY (X’s bro)<\/font><\/td>\n0000001<\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n<\/tr>\n
0000003<\/font><\/td>\nZ<\/font><\/td>\n0000002<\/font><\/td>\n <\/font><\/td>\n <\/font><\/td>\n<\/tr>\n<\/table>\n<\/td>\n<\/tr>\n<\/table>\n\n\n\n
_PARENTS DATABASE<\/strong><\/font><\/td>\n<\/tr>\n
\n\n\n\n\n
ChildIDNO<\/font><\/td>\nParents_ID<\/font><\/td>\nFather<\/font><\/td>\nMother<\/font><\/td>\nEtc…<\/font><\/td>\n<\/tr>\n
0000001<\/font><\/td>\n0000001<\/font><\/td>\nFatherofX<\/font><\/td>\nMotherofX<\/font><\/td>\n <\/td>\n<\/tr>\n
0000003<\/font><\/td>\n0000002<\/font><\/td>\nFatherofZ<\/font><\/td>\nMotherofZ<\/font><\/td>\n <\/font><\/td>\n<\/tr>\n<\/table>\n<\/td>\n<\/tr>\n<\/table>\n

Now is this fine already..?<\/p>\n

Better than the first and second approaches but you can still improve things more.<\/p>\n

Also… notice that Child_002<\/font> disappeared in this scheme.<\/p>\n

But I’ll end things here for now and we’ll continue this series on another post which will include the solution.<\/p>\n

I also have to tackle yet the issue I posted above, the issue between Data Normalization and a more usable interface. That too on the continuation of this post.<\/p>\n","protected":false},"excerpt":{"rendered":"

This is a screenshot of the prototype of a new project of ours. To a give you a short overview it is a database program that monitors the children records in a certain place where parents can ‘safely’ leave their children so they can shop with comfort and peace of mind. These children will then […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,10],"tags":[],"class_list":["post-322","post","type-post","status-publish","format-standard","hentry","category-visual-foxpro","category-workblog"],"_links":{"self":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/posts\/322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/comments?post=322"}],"version-history":[{"count":0,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/posts\/322\/revisions"}],"wp:attachment":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/media?parent=322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/categories?post=322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/tags?post=322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}