On Jul 18, 2014, at 6:08 PM, Ken Roberts <alisonk...@gmail.com> wrote:
> I'm having fun trying to visualize this in my head, but I have 4 tables, 3 > are basic relations, but the 4th table links to table 2. > > Should I take the 4th table and have a relation join with all 3 tables or can > I just relate to table 2 and have table 2 relations do the rest? > > Tables 1-3 are basic equipment relationships: > > 1 - manufacturer > 2 - model (foreignkey manufaturer.id) > 3 - sources (foreignkey model.id) (sources = inputs available) > > manufacturers_table = Table(u'manufacturer', metadata, > Column(u'id', Integer, primary_key=True), > Column(u'name', String(50)) > ) > models_table = Table(u'model', metadata, > Column(u'id', Integer, primary_key=True), > Column(u'manufacturer_id', Integer, ForeignKey(u'manufacturer.id')), > Column(u'name', String(50)) > ) > sources_table = Table(u'source', metadata, > Column(u'id', Integer, primary_key=True), > Column(u'model_id', Integer, ForeignKey(u'model.id')), > Column(u'label', String(20)), > ) > > > The 4th table keeps track of equipment installed at the premise. > > 4 - projector (foreignkey model.id) > > projector_table = Table(u'projector', metadata, > Column(u'id', Integer, primary_key=True), > Column(u'model_id', Integer, ForeignKey(u'model.id')), > ) > > > Tables 1-3 will be updated separately, the projector table will only join > with the other tables for reading. > > The question(s) would be on the joins: > > 1 ) Use projector table with join to model table (and model table will take > care of the joins with manufacturer and sources) > 2) Add table 1 and 3 to projector: > > projector_table = Table(u'projector', metadata, > Column(u'id', Integer, primary_key=True), > Column(u'manufacturer_id', Integer, ForeignKey(u'manufacture.id')), > Column(u'model_id', Integer, ForeignKey(u'model.id')), > Column(u'sources_id', Integer, ForeighKey(u'sources.id')) > ) > > > Hope I'm making myself understood - databases with multiple relations are not > my strong suit. you want option #1 here, at least for manufacturer_id. In #2, by introducing synonymous foreign keys on projector_table you introduce the fact that these two identifiers must be kept synchronized, and the fact that your database can be in an invalid state if this process isn't followed. This is a basic tenet of normalization. When you introduce manufacturer_id to the projector table, this is a value which is specific to "model_id", and not "projector.id". Storing columns that are keyed to another column that isn't part of the primary key violates third normal form: http://en.wikipedia.org/wiki/Third_normal_form. For sources_id, it's a little different because there can be many valid sources_id values for a single model_id value. In that way, projector_table acts a bit like an association table to key a specific model_id and source_id together, *but*, if that were how this were arranged, you probably wouldn't have "model_id" on "sources". Because again, projector can store a model_id/sources_id combination that is not valid given a particular source.model_id value. It suggests that "projector"'s primary key is in fact specific to sources_id and not model_id, which would again be a 3rd normal form violation. Basically 2nd and 3rd normal form guarantee that your DB can never have two pk/fk associations in a conflicting state (and it also compels you to really think what entities really correspond to which). It is not necessarily *wrong* to have dupe FKs in a model, but when a non-normalized relationship pattern is introduced it is typically for performance reasons, and the part of the model that is "denormalized" should be noted; the application, or even better DB level triggers, should ensure that the "denormalized" sets of columns are always kept in sync with their "normalized" partners. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
