Thread: [SQLObject] a general db design quesiton
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Daniel F. <fet...@go...> - 2007-10-13 11:55:14
|
Hi list, This might be slightly off-topic since I'm about to ask a general design question, not in any way sqlobject specific but please bear with me. I have 3 kinds of objects and each kind is stored in its own table. Using the primary key of each table I can refer to each object in that particular table. Now what I would like to have is a kind of primary key for all 3 tables so that by refering to a single key I would be able to select 1 object out of the 3 tables. Right now selection out of the 3 tables goes by first picking a table and then selecting an object from that table but I would like to have an over-arching id for any object. What would be the best strategy for this? |
From: Brian C. <br...@te...> - 2007-10-13 12:07:41
|
A forth table that has four columns: its own primary key; three foreign keys, one for each table. It is comparable to a many-to-many relationship, but with three tables instead of just two. class FourthTable(SQLObject): first = ForeignKey("FirstTable") second = ForeignKey("SecondTable") third = ForeignKey("ThirdTable") Not sure why you would want to do this. Perhaps there are relations between any two table that should be flushed out first? -Brian On 10/13/07, Daniel Fetchinson <fet...@go...> wrote: > Hi list, > > This might be slightly off-topic since I'm about to ask a general > design question, not in any way sqlobject specific but please bear > with me. > > I have 3 kinds of objects and each kind is stored in its own table. > Using the primary key of each table I can refer to each object in that > particular table. Now what I would like to have is a kind of primary > key for all 3 tables so that by refering to a single key I would be > able to select 1 object out of the 3 tables. Right now selection out > of the 3 tables goes by first picking a table and then selecting an > object from that table but I would like to have an over-arching id for > any object. > > What would be the best strategy for this? > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. > Still grepping through log files to find problems? Stop. > Now Search log events and configuration files using AJAX and a browser. > Download your FREE copy of Splunk now >> http://get.splunk.com/ > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Daniel F. <fet...@go...> - 2007-10-13 12:12:44
|
> A forth table that has four columns: its own primary key; three > foreign keys, one for each table. It is comparable to a many-to-many > relationship, but with three tables instead of just two. > > class FourthTable(SQLObject): > first = ForeignKey("FirstTable") > second = ForeignKey("SecondTable") > third = ForeignKey("ThirdTable") > > Not sure why you would want to do this. Perhaps there are relations > between any two table that should be flushed out first? Thanks a lot for the reply! The reason I want to do this is a kind of commenting system. There are 3 types of objects with totally different rows and methods each in its own table. But they share the feature that they can all be commented on. Comments are stored in a separate table but that table has to have a row which refers to the object the given comment belongs to. And this object can be in any of the 3 tables. So how would I connect a comment to the object it refers? |
From: Gregor H. <gh...@gr...> - 2007-10-13 12:27:26
|
Daniel Fetchinson schrieb: >> A forth table that has four columns: its own primary key; three >> foreign keys, one for each table. It is comparable to a many-to-many >> relationship, but with three tables instead of just two. >> >> class FourthTable(SQLObject): >> first = ForeignKey("FirstTable") >> second = ForeignKey("SecondTable") >> third = ForeignKey("ThirdTable") >> >> Not sure why you would want to do this. Perhaps there are relations >> between any two table that should be flushed out first? > > Thanks a lot for the reply! The reason I want to do this is a kind of > commenting system. There are 3 types of objects with totally different > rows and methods each in its own table. But they share the feature > that they can all be commented on. Comments are stored in a separate > table but that table has to have a row which refers to the object the > given comment belongs to. And this object can be in any of the 3 > tables. > > So how would I connect a comment to the object it refers? > I use so called SubObjects for excactly this purpose: http://paste.turbogears.org/paste/1684 -- Greg |
From: Brian C. <br...@te...> - 2007-10-13 12:38:24
|
> I use so called SubObjects for excactly this purpose: > > http://paste.turbogears.org/paste/1684 Very neat solution. But it begs for the title of the original posters subject. Is this good database design? If you rename your objects, you have to repair your data in the database. -Brian |
From: Gregor H. <gh...@gr...> - 2007-10-13 12:44:23
|
Brian Cole schrieb: >> I use so called SubObjects for excactly this purpose: >> >> http://paste.turbogears.org/paste/1684 > > Very neat solution. But it begs for the title of the original posters > subject. Is this good database design? If you rename your objects, you > have to repair your data in the database. > OK. And what's the problem in renaming it? Gregor |
From: Daniel F. <fet...@go...> - 2007-10-13 13:02:39
|
> > I use so called SubObjects for excactly this purpose: > > http://paste.turbogears.org/paste/1684 > Greg, assuming that if you delete an object that has been commented on you want the comments to get deleted automatically then how do you do this? You over-write the destroySelf method of the object in question? |
From: Gregor H. <gh...@gr...> - 2007-10-13 13:42:23
|
Daniel Fetchinson schrieb: >> I use so called SubObjects for excactly this purpose: >> >> http://paste.turbogears.org/paste/1684 >> > > Greg, assuming that if you delete an object that has been commented on > you want the comments to get deleted automatically then how do you do > this? You over-write the destroySelf method of the object in question? At the moment I do this manually, but patches are welcome :-) Greg |
From: Daniel F. <fet...@go...> - 2007-10-13 21:31:29
|
> >> I use so called SubObjects for excactly this purpose: > >> > >> http://paste.turbogears.org/paste/1684 > >> > > > > Greg, assuming that if you delete an object that has been commented on > > you want the comments to get deleted automatically then how do you do > > this? You over-write the destroySelf method of the object in question? > > At the moment I do this manually, but patches are welcome :-) > > Greg Greg, I don't quite get how you use your code. Using a mixin class doesn't seem to work for me as expected, the magic method _get_xxxxx does not get converted into attribute access in the same way as a similarly named method defined directly in the class (and not the mixin) would: ------------------------------------------------------------------------------------- from sqlobject import * from sqlobject import connectionForURI sqlhub.processConnection = connectionForURI( 'sqlite:///:memory:', debug=True ) class mixin( object ): def _get_something( self ): return 1 class foo( SQLObject, mixin ): def _get_somethingelse( self ): return 1 bar = IntCol( ) foo.createTable( ) f = foo( bar=3 ) print f.bar # works print f.somethingelse # works print f._get_something( ) # works print f.something # does not work ------------------------------------------------------------------------------------ So it seems only those _get_xxxxxxxxx methods get converted to ordinary attribute access which were defined directly in the class. In your code you have _get_comments which I would have guessed is named such because it will be used as ordinary attribute access, .comments, but it doesn't work, only as ._get_comments( ). Am I doing something wrong or you call it as a method, ._get_comments( ), all the time and don't worry about accessing it as .comments? |
From: Daniel F. <fet...@go...> - 2007-10-13 12:16:26
|
> > A forth table that has four columns: its own primary key; three > > foreign keys, one for each table. It is comparable to a many-to-many > > relationship, but with three tables instead of just two. > > > > class FourthTable(SQLObject): > > first = ForeignKey("FirstTable") > > second = ForeignKey("SecondTable") > > third = ForeignKey("ThirdTable") > > > > Not sure why you would want to do this. Perhaps there are relations > > between any two table that should be flushed out first? > > Thanks a lot for the reply! The reason I want to do this is a kind of > commenting system. There are 3 types of objects with totally different > rows and methods each in its own table. But they share the feature > that they can all be commented on. Comments are stored in a separate > table but that table has to have a row which refers to the object the > given comment belongs to. And this object can be in any of the 3 > tables. > > So how would I connect a comment to the object it refers? And I forgot to say that I would like to have a solution such that if new objects are added (i.e. new tables are added) that also can be commented on then I don't want to redesign anything. In the ideal case I would set something on the new table that indicates that this table (object) can be commented on and that should be it. |
From: Brian C. <br...@te...> - 2007-10-13 12:31:14
|
> And I forgot to say that I would like to have a solution such that if > new objects are added (i.e. new tables are added) that also can be > commented on then I don't want to redesign anything. In the ideal case > I would set something on the new table that indicates that this table > (object) can be commented on and that should be it. Ah, then ignore the previous email. What you really need is a ForeignKey pointing to your comment table in each of your other objects. Then if you want an object to be able to have comments, just add a "comment_id" column to that object/table. class FirstTable(SQLObject): comment = ForeignKey("Comment") class SecondTable(SQLObject): comment = ForeignKey("Comment") The difficult part becomes going back from the Comment object itself to the object it is associated with. This would be pretty messy to do purely with SQL and database schemas. But with python it is fairly straight forward. Note: this also assumes a comment will only be associated with one object in one other table. class Comment(SQLObject): _objectTypes = [FirstTable, SecondTable] def getOriginalObject(self): for t in self._objectTypes: for r in t.selectBy(commentID=self.id): return r raise KeyError, "This comment is not associated with anything!" You could come up with some SQLObject machinery that dynamically creates the _objectTypes list, but the concept would be the same. -Brian |
From: Daniel F. <fet...@go...> - 2007-10-13 12:40:16
|
> > And I forgot to say that I would like to have a solution such that if > > new objects are added (i.e. new tables are added) that also can be > > commented on then I don't want to redesign anything. In the ideal case > > I would set something on the new table that indicates that this table > > (object) can be commented on and that should be it. > > Ah, then ignore the previous email. What you really need is a > ForeignKey pointing to your comment table in each of your other > objects. Then if you want an object to be able to have comments, just > add a "comment_id" column to that object/table. > > class FirstTable(SQLObject): > comment = ForeignKey("Comment") > > class SecondTable(SQLObject): > comment = ForeignKey("Comment") > > The difficult part becomes going back from the Comment object itself > to the object it is associated with. This would be pretty messy to do > purely with SQL and database schemas. But with python it is fairly > straight forward. Note: this also assumes a comment will only be > associated with one object in one other table. > > class Comment(SQLObject): > _objectTypes = [FirstTable, SecondTable] > def getOriginalObject(self): > for t in self._objectTypes: > for r in t.selectBy(commentID=self.id): > return r > raise KeyError, "This comment is not associated with anything!" > > You could come up with some SQLObject machinery that dynamically > creates the _objectTypes list, but the concept would be the same. The problem with this approach is that a potentially large number of tables results in a potentially large number of queries so there might be a performance hit there. In addition, an object that can be commented on will typically have quite a few _get_XXXX type of methods such as date of last comment, total number of comments, etc, and with your approach all these methods have to be defined for every table which is quite redundant. Gregor, thanks for your link, I'll look into that example, looks like it is something I could use. |
From: Gregor H. <gh...@gr...> - 2007-10-14 04:17:34
|
Daniel Fetchinson schrieb: >>>> I use so called SubObjects for excactly this purpose: >>>> >>>> http://paste.turbogears.org/paste/1684 >>>> >>> Greg, assuming that if you delete an object that has been commented on >>> you want the comments to get deleted automatically then how do you do >>> this? You over-write the destroySelf method of the object in question? >> At the moment I do this manually, but patches are welcome :-) >> >> Greg > > Greg, I don't quite get how you use your code. Using a mixin class > doesn't seem to work for me as expected, the magic method _get_xxxxx > does not get converted into attribute access in the same way as a > similarly named method defined directly in the class (and not the > mixin) would: I am sorry I missed to copy & paste one line: class CommentMixin(BaseMixin): additional_attributes = ["comments"] def _get_comments(self): return self._select_sub_objects(Comment, Comment.q.id, False) comments = property(_get_comments) The _get methods are only converted to properties if the object is inherited from SQLObject. Gregor |
From: Daniel F. <fet...@go...> - 2007-10-14 08:07:31
|
> >>>> I use so called SubObjects for excactly this purpose: > >>>> > >>>> http://paste.turbogears.org/paste/1684 > >>>> > >>> Greg, assuming that if you delete an object that has been commented on > >>> you want the comments to get deleted automatically then how do you do > >>> this? You over-write the destroySelf method of the object in question? > >> At the moment I do this manually, but patches are welcome :-) > >> > >> Greg > > > > Greg, I don't quite get how you use your code. Using a mixin class > > doesn't seem to work for me as expected, the magic method _get_xxxxx > > does not get converted into attribute access in the same way as a > > similarly named method defined directly in the class (and not the > > mixin) would: > > I am sorry I missed to copy & paste > one line: > > class CommentMixin(BaseMixin): > > additional_attributes = ["comments"] > def _get_comments(self): > return self._select_sub_objects(Comment, Comment.q.id, False) > > comments = property(_get_comments) > > The _get methods are only converted to properties if the object is > inherited from SQLObject. > > Gregor Thanks, now everything works as expected. Cheers, Daniel |