Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!news3.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.pipex.net!news.pipex.net.POSTED!not-for-mail NNTP-Posting-Date: Sun, 30 Oct 2005 06:50:48 -0600 Reply-To: "Roy Hann" From: "Roy Hann" Newsgroups: comp.databases.theory References: <1130610162.744817.239070@g14g2000cwa.googlegroups.com> Subject: Re: Modelling objects with variable number of properties in an RDBMS Date: Sun, 30 Oct 2005 12:49:57 -0000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 Message-ID: Lines: 143 NNTP-Posting-Host: 195.112.44.40 X-Trace: sv3-Gpe5QO0YLlgZs4uac1DpozrBW9raFSnugNLax+MoaO3cJ3Vpo7Ey86C/laLSMvOuRqcQRQHURXwLNwt!RbQMojgOY22j8bZJkt90ji/DXZSDTR+PK53OUkEQpSI8jyMmJ+70N6aDAdjb/Kchabx7HEZhpdv3!dP69dQ== X-Complaints-To: abuse@dsl.pipex.net X-DMCA-Complaints-To: abuse@dsl.pipex.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.32 Xref: dp-news.maxwell.syr.edu comp.databases.theory:34134 wrote in message news:1130610162.744817.239070@g14g2000cwa.googlegroups.com... > i've apparently hit a brick wall in my understanding of table > relationships. Say I have the following: > > widgets: > > id widget > ---------------------- > 1 sprocket > 2 cog > 3 hammer > 4 thingamajig No. Let's say you don't, because this is where your problems start. Unless you tell me different, I am inclined to think that the "id" is a label you are lending to your widgets, not an intrinsic attribute of a widget. > qualifiers: > > id qualifier > ---------------------- > 1 red > 2 blue > 3 green > 4 smooth > 5 crunchy > 6 sharp Same argument here. > widgets_qualifiers: > > widget_id qualifier_id > ---------------------- > 1 1 > 2 3 > 2 5 > 3 2 > 3 6 This can be replaced by: widget qualifier ------ --------- sprocket red cog green cog crunchy etc. So far, so much simpler. For one thing, you can now tell at a glance what it is saying, without having to go off and look up what the codes mean. (How come everyone I meet in the real world also does just what you did, but then they can turn around and try to tell me that they need to denormalize for performance?! But I digress...) But this is not a good table. It (perversely) ignores any reality of interest. One can certainly say that it represents the concept that widgets have attributes, but that is trivially true. We make exactly the same assertion about absolutely everything in the universe! To do anything interesting (and useful) you need to say *what* attributes widgets have, and what they mean, and how they are limited. By doing that you would be saying how different kinds of widgets are distinguished by the kinds of attributes that they have and what you can do with them. > Basically I have a many-to-many relationship between > widgets and qualifiers, so that I can assign a number > of different properties to an entry in the widget > table, i.e. a smooth, blue hammer. (Let's ingore for > the moment that with this setup, it's possible to > have a widget be both red and blue which makes no sense) Actually no, let's not. What you have re-invented here is the tired old entity-attribute-value (EAV) design (much beloved of medical research wonks for some reason). What you are asking to ignore is *the* killer argument against EAV. Whether you realize it or not yet, you are undertaking to write *all* the code to implement and enforce any rules about what makes sense. (Or else you are planning to do without those rules, in which case you can just go ahead and do as you like but don't come back here looking for sympathy later. ;-) > I understand that so far. What I want now is the ability > to assign a unique id to a widget posessing specific qualifiers. > IOW, a way to reference a "green, smooth cog" as well as a > "red, crunchy cog" uniquely. So in another table I can reference > this unique identifier in a one-to-many relationship. (For > example in an "open orders" table where we're waiting for one > specific widget for the order to be complete. Not the most > realistic example, but good enough for explanation purpose) > This is where I get stuck. I hate to be negative (this group is often really unpleasantly negative) but there is virtually nothing salvagable at all in what you are thinking at the moment. Your whole mental framework is wrong or non-existent. Sorry. > Because of the possibility of variability of the number of > qualifiers of any particular widget, I don't see how I can > normalize this design. I think I'm on the right track with > the many-to-many relationship, No. You really couldn't be more wrong. > any suggestions on how i can uniquely identify a widget with > varying properties? or is this not even possible? One can do what you did, but it's pointless. If you can't enumerate what properties certain kinds of widgets have, you can't do anything with them. I suspect you might intend to write a program to discern which properties they have at run-time. But to do that you will still have to first specify what properties widgets have (otherwise you can't write the code). But if you can do that then why not save all that coding and just specify the properties widgets have directly, with the database design? (I suppose another alternative might be that you just intend to dump a load of text on the screen and hope the user knows what to do with it, but that's not data management--that's just photocopying without the paper.) What you need to do first is understand the reality of interest. Once you do that you will be able say there are widgets of this type and that type and the next type and so on. Widgets of a particular type can be recognized by having particular attributes of particular type with particular semantics (or constraints). In a real world situation there will be finitely many widget types, usually not very many, probably no more than dozens. You then create tables for each type and apply as many constraints (to encode meaning) as your technology will allow. If you do that then you have something a computer can get its teeth into and you can start doing computation instead of regurgitation. The ability to introduce new entity types at the drop of a hat is a goal of the relational model. Use it. Tables aren't rationed; use as many as you like. I don't think it is a coincidence that your widgets are meaningless fictions. Had you chosen a "real" reality of interest, you might not be so troubled by this. Maybe it is precisely because there is no meaning to model that you are having trouble seeing how to capture it. It is actually a measure of the weakness *not the power* of your EAV design that it admits meaningless fictions as readily as it might admit real facts about real entities. Revisit the problem using some real examples and see how you get on. Roy