Re: How will schemas be affected by nested relations?

From: dawn <dawnwolthuis_at_gmail.com>
Date: 24 Jul 2006 06:32:11 -0700
Message-ID: <1153747931.670115.251380_at_i3g2000cwc.googlegroups.com>


michael_at_preece.net wrote:
> Marshall wrote:
> > How will schema design be affected by having the ability to use
> > nested relations? I have an intuition that it might not be that
> > much; that nesting is a *little* useful but not all that *much* useful.
> > However, I am concerned that I don't have a normal form to
> > inform design choices.
> >
> > When should relation schemas be nested?
> >
> > I have an idea that the answer might relate to when we use
> > ON DELETE CASCADE. If a logical entity has no existence
> > without the "enclosing" context of another entity, then it might
> > be nested. Another consideration is whether it needs to
> > be referred to directly.
> >
> > For example, an invoice line item without an associated
> > invoice doesn't make much sense. However, an invoice
> > is probably something we want to keep even if for some
> > reason we delete the associated customer. So I can see
> > invoice line items as being a candidate for nesting, but
> > not invoices.
> >
> > However I would be much happier if I had a more formal
> > treatment than this. Anyone have any references?
> >
> >
> > Marshall
>
> For a more in-depth treatment of this subject please see
> comp.databases.pick.
>
> ;)
>
> PS. G'day Bob.

Laughing. But what if the OP is not interested in knowing about "best practices" over the past 40 years in multivalued development environments? He might be looking for a mathematical treatment of when propositions should be modeled with nested structures rather than splitting out a single proposition into multiple propositions.

Of course it is not mathematics that told us that we need to explode a single proposition into many in the first place. Because the mathematics is simpler if we can stick to first order predicate logic, some decided that must be the way to go. If the goal is to model propositions so that the simplest mathematics can be applied, the answer would be different than if the goal is to use best practices in software development.

In case it is helpful, the rule of thumb is to identify the entities (leaving that undefined in this post) related to the requirements for a particular project, then keep properties (also left undefined) of those entities tucked under the entities. If some future project determines that one of the "things" previously identified as a property in our database schema should now be seen as an entity, then migrate/change the database solution (refactor).

For example, if the problem space includes women and the dress size of dresses they own, then that set of dress sizes can be modeled as a property of the woman entity within Project A. [Of course, the don't-use-null-RM community would have this in a separate Woman table, keyed by the PersonId, where I might be fine with dressSize as attribute of Person in a non-SQL-DBMS ;-) ]

If there are no dresses in our problem domain, then the size of the woman's dresses, like the color of her eyes, can be seen as properties of the woman within this context. Then if later, during Project G, if there is a requirement to ID and record every dress owned by each person and associate it with the person, it would make sense to have a new Dress entity and include the size as an attribute of that entity. The original dressSize would be transitioned to a virtual attribute of Person (still having a set value, but now getting that as the result of a more complicated function).

This is similar to what is done with single-valued attributes. We might model eye color as a property of a person until a project comes along that splits out the two eyes and associates each eye with its own color.

Does that help at all? Don't worry, I'll go back to lurking. --dawn Received on Mon Jul 24 2006 - 15:32:11 CEST

Original text of this message