Re: Pizza Example

"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>
> > This inevitably leads to further questions. Apparently "pizza" is not
> > the only orderable item, but all orderable items are similar to pizza
> > in that they have size, crust, toppings etc? Assuming that to be true
> > (as it seems to be assumed by your definition):
>
> Bingo -- that is one of the issues. No, the "Garlic Bread" or other items
> that could be ordered might have a subset (including null set) of these
> items, but these includes all attributes that we are putting on the sum
> total of all items. So you can think of the attributes listed as the set of
> all possible attributes for an item. I chose to at least have one item that
> does use all of them, but a typical PICK implementation (from what I have
> seen) includes attributes for sub-types.
>
> > create table item_types( item_type_name varchar2(30) primary key );
> > create table cheeses( cheese_name varchar2(30) primary key );
> > create table toppings( topping_name varchar2(30) primary key );
> > create table sauses( sauce_name varchar2(30) primary key );
> > create table crusts( crust_name varchar2(30) primary key );
> > create table sizes( size_inches integer primary key );
> > create table order( order_no integer primary key );
> > create table order_item
> > ( order_no integer, item_no integer
> > , size_inches references sizes
> > , crust_name references crusts
> > , sauce_name references sauces
> > , primary key (order_no, item_no)
> > );
> > create table order_item_cheeses
> > ( order_no integer, item_no integer, cheese_name references cheeses
> > , foreign key (order_no, item_no) references order_items
> > , primary key (order_no, item_no, cheese_name)
> > );
> > create table order_item_toppings
> > ( order_no integer, item_no integer, topping_name references toppings
> > , foreign key (order_no, item_no) references order_items
> > , primary key (order_no, item_no, topping_name)
> > );
> >
> > This is a very quick & dirty design, so please don't quibble about
> > column names or whether 30 chars is appropriate for a cheese name!
>
> That's helpful, in particular because it verifies my claim that those
> working with an RDBMS do typically put size constraints on data even when
> the problem domain gives no indication of such constraints being required.
> I'm guessing you opted to size these attributes because you size everything
> because that's the way RDBMS's like it (for performance?)

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.

> 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). Received on Sun Apr 04 2004 - 21:56:57 CEST