Re: Normalization Question
Date: Wed, 02 Feb 2005 15:43:22 -0600
Message-ID: <%PbMd.2670$7J.2313_at_okepread04>
Neo wrote:
>>1 >>Marshall]Lucas >>123 W. Somewhere >>73003 >>H]C]I >>14112131234]2318923]011123123445 >>LIST filename PHONE. LIST filename BY-EXP PHONETYPE="C" PHONE.
>
>
> Following script models above with an experimental db that allows each
> thing to have variable number of classifications, variable number of
> attributes and those attributes can have a variable number of values,
> all of which are automatically normalized by the db. GUI allows user to
> navigate data without having to perform joins or create views.
>
> // Create items in directory to classify things.
> (CREATE *person.item ~in = dir)
> (CREATE *firstName.item ~in = dir)
> (CREATE *lastName.item ~in = dir)
> (CREATE *address.item ~in = dir)
> (CREATE *street.item ~in = dir)
> (CREATE *zip.item ~in = dir)
> (CREATE *phone#.item ~in = dir)
> (CREATE *home#.item ~in = dir)
> (CREATE *cell#.item ~in = dir)
> (CREATE *international#.item ~in = dir)
>
> // Create a person named Marshall Lucas with address and phone#s.
> (CREATE *.cls = person
> & it.firstName = +Marshall
> & it.lastName = +Lucas
> & it.address = (CREATE *.cls = address
> & it.street = +"123 W Somewhere"
> & it.zip = +73003)
> & it.phone# = (CREATE *14112131234.cls = phone#
> & it.cls = home#)
> & it.phone# = (CREATE *2318923.cls = phone#
> & it.cls = cell#)
> & it.phone# = (CREATE *011123123445.cls = phone#
> & it.cls = international#)
> )
>
> // This query finds persons that have a phone#
> (SELECT %.cls=person & %.phone#)
>
> // This query finds persons that have a cell#
> (SELECT %.cls=person & %.phone#=(%.cls=cell#))
>
>
> Now suppose, we want Marshall to have a second cell#. Below script
> shows how with experimental db. How does your schema handle different
> number of values for home#, cell#, etc. For example, first person might
> have one cell number, while the next person has two cell numbers. If
> your schema encodes "14112131234]2318923]011123123445" how does it know
> there is 1 home#, 1 cell# and 1 international# versus 0 home#s, 3
> cell#s, 0 international#s?
>
> // Add second cell# to Marshall Lucas
> (CREATE (%.firstName=Marshall & %.lastName=Lucas).phone# =
> (CREATE *1112222.cls = phone# & it.cls = cell#))
>
>
> Now suppose, we want to add a work# that has an extention attribute and
> it is shared by two persons. Below script shows how Marshall and Mary
> can have the same work# which has the extention 123, without redundant
> data. Note, while the work# 5556666 appears several times in the
> script, it is stored only once in the db and Marshall and Mary have
> references to it.
>
> // Add work#
> (CREATE *work#.item ~in = dir)
> (CREATE *ext#.item ~in = dir)
> (CREATE *5556666.cls = phone#
> & it.cls = work#
> & it.ext# = +123)
>
> // Add work# to Marshall
> (CREATE (%.firstName=Marshall & %.lastName=Lucas).phone# = 5556666)
>
> // Add Mary with same work#
> (CREATE *.cls=person & it.firstName= +Mary & it.phone# = 5556666)
>
Notice the field just above the phone number, it's a flag that tells what the type of phone number is. You can have any combination you wish, only the front end code would control the type and quantity, not the back end database. Also, note that my LIST statement is somewhat less cryptic than your SELECT statement (only somewhat, not a lot). The nice thing with most MVDBSs is that I can create as many "Synonym" dictionary items as I wish that all point to the same field. So, I could have: LIST filename PHONE or LIST filename TELEPHONE or even create a dictionary that is "smart" called AREA.CODE that knows how to break down the phone number and return a proper AREA.CODE. I can also define a dictionary item that will pull data in from other files (tables) using a field, or fields, in the current file as the id to the secondary file. This is similar to an SQL join, but without the hassles. Also, since the data id is hashed, access is almost immediate when you have the id of the record.
Also, if, heaven forbid, a file (table) gets corrupted on the disk, I can usually rebuild it with only a few records lost and get back on the road quickly. Then I can take my time restoring backups or doing whatever I need to do to try and get those few records back. There are no intrinsic connections between the files (tables), so a missing record does not kill the whole database system. No loss of referential integrity as there is none to start with on the database manager level. It's all left up to the code to handle. While it may mean more programming work to be done, once a set of tools is built (which all "Pickies" have) you never worry about it again, just use the tried and true code that makes sure referential integrity is maintained where it is needed.
Also, I can maintain a large MVDBMS as a one man show many times, where as an SQL database of the same size I have seen 10 or more programmers on staff. I have seen a 10 to 1 ratio in many shops where both MVDBS and SQL servers are being used. Not to mention, the MVDBMS guy usually manages to be able to take long vacations without his system crashing, but the SQL guys are on call 24-7.
Marshall Received on Wed Feb 02 2005 - 22:43:22 CET