Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.glorb.com!newsfeed.stueberl.de!fu-berlin.de!uni-berlin.de!bumblbee.demon.co.UK!not-for-mail
From: Lemming <thiswillbounce@bumblbee.demon.co.uk>
Newsgroups: comp.databases.theory
Subject: Re: Pizza Example
Date: Sun, 04 Apr 2004 22:16:14 +0100
Lines: 90
Message-ID: <r6u070tf932cma03fn34cl85lg40dl6hib@4ax.com>
References: <c4nb1k$22h$1@news.netins.net> <c4nglq$7pt$1@news.netins.net> <QtLbc.26194$nr4.17312@twister.nyroc.rr.com> <c4o2at$frb$1@news.netins.net> <c0e3f26e.0404040326.7e59f8f9@posting.google.com> <c4p4q7$on1$1@news.netins.net> <c0e3f26e.0404041156.ae57634@posting.google.com> <c4pr5i$n3p$1@news.netins.net>
NNTP-Posting-Host: bumblbee.demon.co.uk (158.152.77.76)
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: news.uni-berlin.de 1081113354 90503915 I 158.152.77.76 ([16776])
X-Newsreader: Forte Agent 1.93/32.576 English (American)
Xref: newssvr20.news.prodigy.com comp.databases.theory:24995

On Sun, 4 Apr 2004 15:28:25 -0500, "Dawn M. Wolthuis"
<dwolt@tincat-group.com> wrote:

>"Tony" <andrewst@onetel.net.uk> wrote in message
>news:c0e3f26e.0404041156.ae57634@posting.google.com...
>> "Dawn M. Wolthuis" <dwolt@tincat-group.com> wrote in message
>news:<c4p4q7$on1$1@news.netins.net>...
>> > "Tony" <andrewst@onetel.net.uk> wrote in message
>> > news:c0e3f26e.0404040326.7e59f8f9@posting.google.com...
>> > > "Dawn M. Wolthuis" <dwolt@tincat-group.com> wrote in message
>> >  news:<c4o2at$frb$1@news.netins.net>...
>> > > > "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
>> > > > news:QtLbc.26194$nr4.17312@twister.nyroc.rr.com...
>> > > > >
>> > > > > "Dawn M. Wolthuis" <dwolt@tincat-group.com> wrote in message
>> > > > > news:c4nglq$7pt$1@news.netins.net...
>> >  <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.  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.  Constraining the database to only store up to a
>particular length means that the database needs to be changed if a new valid
>value for an attribute arises that is longer than what was anticipated.  The
>UI can permit scrolling to show only a limited number of characters, but
>scroll to show all.  A report writer can wrap long values to a certain width
>for practicality.  Unless there is a REAL business reason for limiting the
>number of characters for a value (or your DBMS product requires it or gives
>you better features if you do), don't do it.  From what I have seen of RDBMS
>tools, those who use them are inclined to put an attribute of "color" in the
>database (for example) with a max number of characters for the value.  All
>we need, then, is a new color to come available and you can bet that the
>name will be abbreviated now to keep it within that length.  How silly IMO.

Doesn't "ALTER TABLE ... " make this problem go away?

>> > So, now, if you don't make the assumption that every item that someone
>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 for
>what that is worth.
>smiles.  --dawn

Maybe I've missed the point, but it seems to me the above solution is
backwards.  The order_item shouldn't reference crusts at all ... you
need an intermediate table to resolve the possible many-to-many
relationship of order_item to crust.  Informally:

Table: crust (crust_id, 
			data ...)
Table: order_item 
		(order_id,item_id, 
			data ...)
Table: item_crust 
		(crust_id references crust (crust_id), 
		(order_id, item_id) references order_item(order_id, item_id),
			data ...)

Hope that makes sense, it's late ...

Lemming
-- 
Curiosity *may* have killed Schrodinger's cat.