Left Joins / Inheritance / Plugins ...whatever...

Hard to come up with an appropriate subject for this
question..

Here's the issue:

(The site is a small-theater company management app)
I have a model 'Patrons' which encapsulates all of the
people that I want 
to track in my site.  A Patron is anyone who's done anything
with-or-for
the theater company.  In order that I don't have to keep
track of 
all kinds of information for all kinds of Patrons, I want to
have 
'plugins' which 'attach' to the Patron record.  For example,
when someone
signs up to audition for a show, they get an
"Auditioner" object
attached to their Patron object.  The "Auditioner"
object encapsulates
information that the director of the show will need (when,
voice type,
age, height, etc.) things that we normally don't care about
for most
patrons.  Most patrons will going to shows, so they will
have "TicketPurchase"
objects attached to their Patron object, keeping track of
all of the
ticket purchases that person has made.

My initial thought is something like this:

    class Patron(models.Model):
        last_name = models.CharField( ... )
        first_name = models.CharField( ... )
        addr = models.CharField( ... )
        .. etc ..


    class Auditioner(models.Model):
        patron = models.ForeignKey(Patron)

        audition_date = models.DateTimeField( ... )
        .. etc ..


    class TicketPurchase(models.Model):
        patron = models.ForiegnKey(Patron)

        num = models.IntegerField(...)
        show = models.ForeignKey(shows.Show)
        .. etc ..


Now what I want to display in a list view of patrons is:

    Last, First       ... Audition?  TicketPurchase?
    Cuddy, Michael           Y            Y
    Smith, John              N            Y
    ...

Where the 'Y' (or 'N') indicates whether or not the Patron
has that 
particular kind of data attached to their Patron object (and
in a real site
would be links to go look at / modify that data).  Not all
patrons
will have all plugins associated (Also, ideally, the view
for patrons
would not know 'ahead of time' what all of the plugins were
-- new ones should
be able to be added at a later date and not disrupt the
existing data.

The number of patrons will run into the thousands. The
number of 'plugins'
is initially planned to be around 10-15.

My zero'th thought was some kind of object inheritance, but
django
doesn't support that, and it's messy, and, and, and ... so I
didn't really
think about that too much 

My first thought was LEFT JOIN'ing the tables, but that has
a number of 
issues:  first, it drops to raw SQL and kind of violates the
django-ness (!?)
of the site.  Second, LEFT JOINs with more than a couple of
tables 
starts to be a real performance bottleneck.

My next thought was that since the primary access for lists
of
people will be through the patron model (frankly, most of
the queries 
will be alphabetical or searching for a specific patron by
name), 
so when generating a view of patrons, it's probably not that
bad
to just do the individual queries for each record displayed
on a 
particular page hit.  I could probably come up with a
'patron_id__in = [..]' 
type query and run that across the plugins which would allow
me to do one 
query per plugin once the set of Patrons to show on a
particular page has 
been determined; and that's not too bad.

However, I'm interested in any ideas anyone else might have
for this.

One more (maybe) cog in the works:  some plugins (like the
aforementioned 
"Auditioner" and "TicketPurchase" will
have many entries per Patron.
Some plugins, like "Staff", the one which holds
login auth information
will be one-entry per Patron.

--
Mike Cuddy (mcuddyFensEnde.com), Programmer, Baritone,
Daddy, Human.
Fen's Ende Software, Redwood City, CA, USA, Earth, Sol
System, Milky Way.

    "The problem with defending the purity of the
English language is
    that English is about as pure as a cribhouse whore. We
don't just
    borrow words; on occasion, English has pursued other
languages down
    alleyways to beat them unconscious and rifle their
pockets for new
    vocabulary." -- James D. Nicoll

       Join CAUCE: The Coalition Against Unsolicited
Commercial E-mail.
                          <http://www.cauce.org/>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Mike,

What comes to mind is an intermediary table, a Many-to_many
table.

Design 1:
This design depends on a separate Model that lists the
available  
plugins, but would require a manual join of the
plugin_table_id to  
the appropriate model/table. The plugin model tells you
which type of  
link you are doing.

PatronPluginLink
	patron = ForeignKey(Patron)
	plugin = ForeginKey(Plugins)
	plugin_table_id = IntegerField()

Design 2:
This is easier to model, but more restrictive. The downside
is you  
have to add fields to this table when you add plugins.

PatronActivities
	patron = ForeignKey(Patron)
	auditioner = ForeignKey(Auditioner)
	ticketpurchase = ForeignKey(TicketPurchse)
	...


But these are just off the top of my head. Hope they could
help.

Corey


On Mar 29, 2007, at Thu Mar 29, 8:35 PM, Michael Cuddy
wrote:

>
>
> Hard to come up with an appropriate subject for this
question..
>
> Here's the issue:
>
> (The site is a small-theater company management app)
> I have a model 'Patrons' which encapsulates all of the
people that  
> I want
> to track in my site.  A Patron is anyone who's done
anything with- 
> or-for
> the theater company.  In order that I don't have to
keep track of
> all kinds of information for all kinds of Patrons, I
want to have
> 'plugins' which 'attach' to the Patron record.  For
example, when  
> someone
> signs up to audition for a show, they get an
"Auditioner" object
> attached to their Patron object.  The
"Auditioner" object encapsulates
> information that the director of the show will need
(when, voice type,
> age, height, etc.) things that we normally don't care
about for most
> patrons.  Most patrons will going to shows, so they
will have  
> "TicketPurchase"
> objects attached to their Patron object, keeping track
of all of the
> ticket purchases that person has made.
>
> My initial thought is something like this:
>
>     class Patron(models.Model):
>         last_name = models.CharField( ... )
>         first_name = models.CharField( ... )
>         addr = models.CharField( ... )
>         .. etc ..
>
>
>     class Auditioner(models.Model):
>         patron = models.ForeignKey(Patron)
>
>         audition_date = models.DateTimeField( ... )
>         .. etc ..
>
>
>     class TicketPurchase(models.Model):
>         patron = models.ForiegnKey(Patron)
>
>         num = models.IntegerField(...)
>         show = models.ForeignKey(shows.Show)
>         .. etc ..
>
>
> Now what I want to display in a list view of patrons
is:
>
>     Last, First       ... Audition?  TicketPurchase?
>     Cuddy, Michael           Y            Y
>     Smith, John              N            Y
>     ...
>
> Where the 'Y' (or 'N') indicates whether or not the
Patron has that
> particular kind of data attached to their Patron object
(and in a  
> real site
> would be links to go look at / modify that data).  Not
all patrons
> will have all plugins associated (Also, ideally, the
view for patrons
> would not know 'ahead of time' what all of the plugins
were -- new  
> ones should
> be able to be added at a later date and not disrupt the
existing data.
>
> The number of patrons will run into the thousands. The
number of  
> 'plugins'
> is initially planned to be around 10-15.
>
> My zero'th thought was some kind of object inheritance,
but django
> doesn't support that, and it's messy, and, and, and ...
so I didn't  
> really
> think about that too much 
>
> My first thought was LEFT JOIN'ing the tables, but that
has a  
> number of
> issues:  first, it drops to raw SQL and kind of
violates the django- 
> ness (!?)
> of the site.  Second, LEFT JOINs with more than a
couple of tables
> starts to be a real performance bottleneck.
>
> My next thought was that since the primary access for
lists of
> people will be through the patron model (frankly, most
of the queries
> will be alphabetical or searching for a specific patron
by name),
> so when generating a view of patrons, it's probably not
that bad
> to just do the individual queries for each record
displayed on a
> particular page hit.  I could probably come up with a 

> 'patron_id__in = [..]'
> type query and run that across the plugins which would
allow me to  
> do one
> query per plugin once the set of Patrons to show on a
particular  
> page has
> been determined; and that's not too bad.
>
> However, I'm interested in any ideas anyone else might
have for this.
>
> One more (maybe) cog in the works:  some plugins (like
the  
> aforementioned
> "Auditioner" and "TicketPurchase"
will have many entries per Patron.
> Some plugins, like "Staff", the one which
holds login auth information
> will be one-entry per Patron.
>
> --
> Mike Cuddy (mcuddyFensEnde.com), Programmer, Baritone,
Daddy, Human.
> Fen's Ende Software, Redwood City, CA, USA, Earth, Sol
System,  
> Milky Way.
>
>     "The problem with defending the purity of the
English language is
>     that English is about as pure as a cribhouse whore.
We don't just
>     borrow words; on occasion, English has pursued
other languages  
> down
>     alleyways to beat them unconscious and rifle their
pockets for new
>     vocabulary." -- James D. Nicoll
>
>        Join CAUCE: The Coalition Against Unsolicited
Commercial E- 
> mail.
>                           <http://www.cauce.org/>
>
> >


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

hello,

I myself hink that your solution with foreign keys is good.
the fact
that django requires raw sql to handle left joins is its
limitation
but not that of your design.

another solution that comes to mind is to have a table of
generic
"attributes" for each patron. this way you will
have two tables, one
with patrons the other with attributes.

model Attribute(models.Model) :
    owner = models.ForeignKey(Patron)
    type = ...field for attribute type perhaps...
    value = ...field for attribute value...

it is difficult though to have attributes of different
types, data,
blobs, integers, srings because you will have to have
separate fields
for that.

konstantin


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---