Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Pizza Example

Re: Pizza Example

From: Dawn M. Wolthuis <>
Date: Mon, 5 Apr 2004 11:19:56 -0500
Message-ID: <c4s0vq$ddp$>

"Tony" <> wrote in message > "Dawn M. Wolthuis" <> wrote in message news:<c4pr5i$n3p$>...
> > "Tony" <> wrote in message
> >
> > > "Dawn M. Wolthuis" <> wrote in message
> > news:<c4p4q7$on1$>...
> > > > "Tony" <> wrote in message
> > > >
> > > > > "Dawn M. Wolthuis" <> wrote in message
> > news:<c4o2at$frb$>...
> > > > > > "rkc" <> wrote in message
> > > > > > news:QtLbc.26194$
> > > > > > >
> > > > > > > "Dawn M. Wolthuis" <> wrote in message
> > > > > > > news:c4nglq$7pt$
> > > > <snip>

> >

> > >
> > > SQL DBMSs, not RDBMSs as such. But I didn't have to do that. I
> > > could use e.g. Oracle's CLOB datatype, which allows up to 4GB of data.
> > > Or I could have said varchar2(4000). RDBMS designers tend to like to
> > > impose some kind of discipline on the data being stored, it isn't
> > > really a performance issue - at least not from the DBMS's point of
> > > view. But if you allow 4000 character pizza names in the database,
> > > then you must allow for that in your user interface, which means
> > > declaring 4000-character variables - something of a memory drain,
> > > perhaps? Frankly it would just seem rather stupid to allow unlimited
> > > length for every data item.
> >

> > I see no logic in this.

> Why doesn't that surprise me? ;-)

Hey Tony, you could cut me some slack -- I'm at least asking question in an honest effort to learn. Some of the relational theorists on this list are open to learning other approaches, while many seem to be happy to live with their relational tunnel vision.

I understand now from Paul's response that the reason folks like to restrict the size of fields is not for performance reasons, but because they want the database, not just software applications, to kick out data that is clearly wrong. I suspect that in many cases, by the time data reaches the DBMS a software developer has already found a reason to apply constraint logic (coded outside of the database) to the transactions because they needed to for the GUI or to provide an immediate response to a web services transaction, but this locks down the database further so if a programmer doesn't catch something, the database will.

It does seem to me that it might make more sense, then, for these lengths to give warnings in many cases, rather than be fixed constraints if they are checking for the likelihood of good data, but I don't know if there is a clean way to do that.

Otherwise, allowing the database to store every data element as variable length unless there is a clear business reason not to, but ensuring that all points of entry of data -- GUI or web service, for example -- apply constraint logic with warnings in cases where unlikely data is entered makes more sense to me.

> > The database need not constrain the size of data
> > that is stored and the UI can either constrain it or not, as the
> > designer/user see fit.

> No it NEED not, as I said above.  But it makes a lot of sense to do so
> - see Paul's post for integrity reasons.  Another reason would be to
> impose some predictability on data items.  Obviously, if you have an
> attribute like "customer_opinion_on_pizza" then you would allow for
> plenty of text - though even then you may prefer to limit to 2000
> chars (e.g.) rather than let some verbose customer fill 4GB of waffle
> about it.  But if EVERY column allowed "unlimited" length, how would
> you design your screens and reports?  Scrollable text fields for
> everything presumably.

> > Constraining the database to only store up to a
> > particular length means that the database needs to be changed if a new
> > value for an attribute arises that is longer than what was anticipated.
> How terrifying!  More likely of course, the user will come up with a
> suitably abbreviated name.  We are all familiar with such abbreviated
> names from till receipts, credit card statements etc.

Yes, exactly the state of data I have seen. Instead of the easy, but not completely without risk, task of altering a table, the user simply figures out how to work around the constraints. I think that is a bit of a shame, but I guess I can see where the cost of both end-users and software developers figuring out how to live with the initial constraints for the life of a software application rather than "fixing" them could be considered part of the cost of the software. Hopefully neither the user or the developer will go to extremes in this regard and will request the required changes when needed (but we have all seen cases where they have, right?)

> In any case, let's be clear: this argument is a side-line that has
> NOTHING to do with choosing an RDBMS over Pick or whatever.  It is a
> design issue; the fact that relational database designers are more
> likely to choose to constrain data lengths than you are is perhaps
> just a symptom of our more disciplined approach to database design.
> >

> > > > So, now, if you don't make the assumption that every item that
> > could
> > > > buy would have each attribute (such as Sauce), then how would you do
> > > > t? --dawn
> > >
> > > In a SQL database you could just allow NULLs in those items. In a
> > > true RDBMS that doesn't allow nulls you could either create a table
> > > for each subtype of order_items or you could move the attributes like
> > > crust_name into separate tables like:
> > >
> > > create table order_item_crust
> > > ( order_no integer, item_no integer, crust_name references crusts
> > > , foreign key (order_no, item_no) references pizza_order_items
> > > , primary key (order_no, item_no)
> > > );
> > >
> > > (Note that this table only allows a maximum of one crust per pizza).
> >
> > Yes, this does look like an RDBMS approach to the problem. I don't
think it
> > is a "natural" way to view the problem, but it follows a set of rules
> > what that is worth.
> What it is worth is a LOT, since computers work better with data
> structured according to rules rather than "unstructured" or "natural"
> data.  Who cares if it seems "natural" to you?  If you are a database
> designer, you better learn to see beyond the "natural" if you are
> going to be useful, and if you are an end-user accessing the database
> via an application you can be protected from needing to understand the
> relational way of doing it.

But if there is a structure and set of rules that align better with the natural structure of language, then we could possibly have a cost savings over the life of software (and that is what the anecdotal evidence suggests is happening, but I have nothing to prove it). I am NOT at all opposed to structure. I am alsol pro-constraints and would like to see the constraints that are important for data integrity be applied at the proper points in an application -- that might mean both in a GUI and when the data are stored.

What I have seen with RDBMS's is that the size constraint is placed on everything while more important constraints related to the allowable values are not as often applied, especially when it is a "yellow flag" type of data value for an attribute rather than obviously not permitted. I'll put this in a separate thread at some point (but have several other questions prior to that one).

Thanks. --dawn Received on Mon Apr 05 2004 - 11:19:56 CDT

Original text of this message