Re: Pizza Example
Date: Sun, 4 Apr 2004 09:06:57 -0500
"Tony" <andrewst_at_onetel.net.uk> wrote in message
> "Dawn M. Wolthuis" <dwolt_at_tincat-group.com> wrote in message
> > "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?)
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 Received on Sun Apr 04 2004 - 16:06:57 CEST