Re: Can we solve this -- NFNF and non-1NF at Loggerheads

<lauri.pietarinen_at_atbusiness.com> wrote in message news:1107807726.330299.215610_at_l41g2000cwc.googlegroups.com...
> Dawn M. Wolthuis wrote:
>>
>> So, why do you write "only in the result" rather than "only in the
> logical
>> model"? Why do you or the computer need the logical model to include
>
>> multiple relations with this particular set of predicates? It seems
> to me
>> that the DBMS software and you could both be successful with your
> work with
>> a model that includes only one relation, rather than three, in this
> case.
>> Your thoughts? Thanks. --dawn
>>
>
> The reason I write "only in the result" is that I would not want
> to model the PERSON table so that it has nested tables within and
> the reason is that it gives me more flexibility.

Oddly enough, even though a key purpose of relational theory is that it gives flexibility for maintenance, my experience tells me that there is more flexibility in the model where child tables that are components (those whose life cycle is tied to the parent) can be nested.

> Suppose I want to just go over the email addresses see how
> many acme.com -domains there are. If this information was
> embedded in the PERSON-table I would have to dig it from there.
> I don't know what the query would look like, but it sure would
> be more complex than
>
> SELECT *
> FROM EMAIL
> WHERE email_txt LIKE '%_at_acme.com'

Again, as odd as it might seem, the query is equally as simple -- it looks like this (I'll use the plural since that is somewhat popular in this model - you can see the query reads well that way)

LIST PEOPLE WITH EMAIL LIKE ..._at_acme.com

In the case of my model, there would be only one output "record" for each person, while in yours you might still have to group by the person ID if you don't want a count greater than 1 for any particular person

> And suppose I wanted to keep track of the spam mail
> the system has sent out.
>
> I could have the table
> EMAIL_LOG(emlog_id (PK), email_id (FK), time_sent, etc...)

You are right -- if you model it my way, then you would model a log table for a particular e-mail address with a primary key that includes both the person's ID and the e-mail address -- no big deal, however.

> I can reference the email simply by it's id. What if it was
> embedded?? I have no idea how I would refer to it!

With the parent id, then the child -- like referring to a database schema with the host/port and the schema name, or a folder name and the file name inside it -- it is amazing how much sense it makes to the human brain when you are working with it

> In short, by keeping them in separate tables I
> keep my options open, because I don't know what
> needs I will have in the future.

That is true in theory, but doesn't work out so well in practice. In the model I prefer, if in 1993 you modeled e-mail address as a single attribute (which many did, I suspect) and add it to a person relation, then in my model when you find that you need to keep track of multiple e-mail addresses, you simply alter the cardinality from being single-valued to multivalued and keep right on moving. It was amazing for me to see this when I started working with it -- the programmers changed the logical dictionary, often adding a new definition for the field if not simply changing the one that was there and then adding a flag for multiiplicity to the input screens so that instead of asking for one value that single input field on the screen turned into a rolling window (it still showed only one value at a time, unless they redesigned the screen, but it allowed the user to scroll through a window of multiple values).

> If I embed the information I loose flexiblity.

I can see where one would think that, especially given the sermons on that in the industry all these years, but that is not what I have seen.

> I hope this clarifies my stand.

It sounds like you are reading from the relational theory bible, and anyone who has never worked in a more productive environment than that would naturally tend toward these beliefs. I know I haven't proven anything other than this at this point, but I do hope to at some point. I anticipated when I started my research (compared to "my practice") that I would be swayed again to relational theory and would come up with some abberation that would explain why my experience did not synch with theory. However, I have seen that the theory is not exactly airtight and I suspect that is why data modelig approaches, such as will be done for XML document design, will end up booting relational theory from the king-of-the-hill position it has been enjoying for a couple of decades -- just an opinion, of course. cheers! --dawn

> regards,
> Lauri Pietarinen
>
Received on Tue Feb 08 2005 - 03:08:19 CET