[Twisted-Python] coding standard: plurals for database table names

[Justin Ryan]

> >   3. It is helpful to name foreign key columns as the
> >      same name as the table that they are coming from.
> 
> What if you have more than one foreign key to the same 
> table?  A very simple example (from my app ;^):  the 
> attributes "owner", "creator", and "modifier" all point 
> to the "persons" table.  As a rule of thumb, it's usually 
> best to name the foreign key column after the role that 
> the foreign key entity plays in the referring table.

A combination of both is best - i.e.

CONT_Contact
  CONT_ContactID FirstName LastName CONT_ContactTypeID

CONT_ContactType
  CONT_ContactTypeID Name

CONT_Organization
  CONT_OrganizationID Name Address

CONT_xref_Contact_Organization
  CONT_ContactID CONT_OrganizationID

CONT_ContactTypeID is a primary key on CONT_ContactType, and a foreign
key on CONT_Contact.  The xref table CONT_xref_Contact_Organization is
obviously a relationship between Contacts and Organizations, and
CONT_ContactID is obviously a foreign key to CONT_Contact.

If you structure tables / references like this, it's very difficult to
miss a bug where the wrong fields are being joined.  This still falls in
line with the recommendation that table names should be singular.

I agree that the Table-Row relationship is similar to the Class-Instance
relationship.  I think that the existence of object relational mappers
like t.e.row support this paradigm.  It is easy to understand the
mapping of programmatical information into the structure of the database
if you can name a class after a table.  Then you have instances of
CONT.Contact and references to a CONT.ContactType instance and one or
more CONT.Organization instances.

You can create higher level objects to further the abstraction, having a
Contact with an organization and type attribute.

> >   5. While there are astechic reasons for this (above)
> >      the resons become all the more important when 
> >      you want to build automatic tools for searching 
> >      over schemata, etc.  In this case, using plurals
> >      only adds one extra (unnecessary) step in the
> >      automated translation of plural table names to 
> >      singluar column and or class names.
> 
> I didn't think it was all that difficult.  I can send you
> the O-R mapping I use, if you like.

I would like to see the O-R mapping that you use, simply to compare
ideas..  I still think singular table names are more logical ;p

> > In short, I'd recommend singluar nouns, with M-M
> > link tables as one__two where one and two are 
> > tables being linked to.

I think that my version with the 'xref' in the name is more readable.

> One could reasonably argue that it is not important to have 
> a table-naming rule in the coding standard.  I don't really 
> care either way, but I happen to like using plural names for 
> tables, and I'll use it regardless of what the Twisted coding std 
> says anyhow, because I use my own O-R mapping instead of t.e.row 
> (mine is different from t.e.row in many ways, but it does map plural 
> table names to singular object names ;^).

well, I beleive it's in the coding standard for the purpose of modules
that rely on a database schema of some sort.  It never hurts to develop
your code in line with the standards of a large framework you're using,
and if it doesn't make sense, you could question it (as we're doing
now).

Cheers,

-j