Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.glorb.com!postnews1.google.com!not-for-mail From: andrewst@onetel.net.uk (Tony) Newsgroups: comp.databases.theory Subject: Re: Pizza Example Date: 4 Apr 2004 04:26:18 -0700 Organization: http://groups.google.com Lines: 81 Message-ID: References: NNTP-Posting-Host: 80.3.32.6 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1081077978 13132 127.0.0.1 (4 Apr 2004 11:26:18 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sun, 4 Apr 2004 11:26:18 +0000 (UTC) Xref: newssvr20.news.prodigy.com comp.databases.theory:24978 "Dawn M. Wolthuis" wrote in message news:... > "rkc" wrote in message > news:QtLbc.26194$nr4.17312@twister.nyroc.rr.com... > > > > "Dawn M. Wolthuis" wrote in message > > news:c4nglq$7pt$1@news.netins.net... > > > I'll try to see if I can get the essense of the display of data in the > pizza > > > example to be clearer by removing the first few columns so it doesn't > wrap. > > > I hope this displays better. Thanks. --dawn > > > > > > Size....Crust.......Sauce.....Cheese.........Topping > > > 16" Chicago Tomato Mozzarella Pepperoni > > > Feta Black Olives > > > Parmesan > > > > > > Start with your desired 'display' and apply the rules of normalization. > > You seem to bright to not understand that, so you must be trying to > > make some other point. > > Ah, so not all readers of the list have Bob B's opinion, eh? Actually, it > is neither a) that I don't know how to perform a strict normalization > process on the data as I have written it nor b) that I'm trying to make > another point here. > > It is that I suspect that someone who thinks in terms of normalized data > would not think about the problem domain in the same way. For example, > perhaps someone would decide that the number of combinations was a > relatively small finite number at this point in time and for the foreseeable > future and might make a table of all possible combinations of pizzas with a > generated candidate key. Then that would be placed as a foreign key in the > ORDER_ITEM table. I'm sure there are other possibilities too. I'm guessing > that it would be the rare RDBMS DBA that would actually implement a strictly > normalized version of the data described in my example, but I could be wrong > about that. I would hope that you are wrong about that. I hope that most RDBMS DBAs would know that a normalized database is the right approach. > So, you are right, I'm not simply asking for this data to be normalized, but > for it to be understood so that a relational database implementation way of > thinking is presented. I was going to simply use this as an example of the > difference between how an XML/PICK model of the data would look compared to > a relational model and when I normalized the data, I looked at it and > thought that it was unlikely that anyone would actually implement it that > way. So, how would you do it? 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): 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!