Re: Pizza Example

Lemming <thiswillbounce_at_bumblbee.demon.co.uk> wrote in message news:<r6u070tf932cma03fn34cl85lg40dl6hib_at_4ax.com>...
> On Sun, 4 Apr 2004 15:28:25 -0500, "Dawn M. Wolthuis"
> <dwolt_at_tincat-group.com> wrote:
>
> >"Tony" <andrewst_at_onetel.net.uk> wrote in message
> >news:c0e3f26e.0404041156.ae57634_at_posting.google.com...
> >> "Dawn M. Wolthuis" <dwolt_at_tincat-group.com> wrote in message
> news:<c4p4q7$on1$1_at_news.netins.net>...
> >> > "Tony" <andrewst_at_onetel.net.uk> wrote in message
> >> > news:c0e3f26e.0404040326.7e59f8f9_at_posting.google.com...
> >> > > "Dawn M. Wolthuis" <dwolt_at_tincat-group.com> wrote in message
> news:<c4o2at$frb$1_at_news.netins.net>...
> >> > > > "rkc" <rkc_at_yabba.dabba.do.rochester.rr.bomb> wrote in message
> >> > > > news:QtLbc.26194$nr4.17312_at_twister.nyroc.rr.com...
> >> > > > >
> >> > > > > "Dawn M. Wolthuis" <dwolt_at_tincat-group.com> wrote in message
> >> > > > > news:c4nglq$7pt$1_at_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?

Yes, but programmers like Dawn seem to think DBMSs are fiendishly difficult to use, so would avoid requiring ALTER TABLE statements like the plague ;-)

> >> > 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 ...)

Yes, that is exactly what I meant. My "order_item_crust" table is the intermediate table between my order_items and crusts tables. Received on Mon Apr 05 2004 - 11:17:36 CEST