Re: [sqlalchemy] Multiple tables and foreignkey constraints
I would probably do: # Keep track of manufacturer names t_manufacturer = Table(u'manufacturer', metadata, Column(u'id', Integer, primary_key=True, Column(u'name', String(20)) ) # Keep track of model names t_model = Table(u'model', metadata, Column(u'id', Integer, primary_key=True, Column(u'manufacturer_id', Integer, ForeignKey(u'manufacturer.id')), Column(u'name', String(20)) ) # Keep track of inputs available to models t_source = Table(u'source', metadata, Column(u'id', Integer, primary_key=True, Column(u'pjlink', String(2)), Column(u'text', String(20)) ) # Foreign key table for model/sources t_model_2_source = Table(u'model_2_source', metadata, Column(u'model_id', Integer, primary_key=True, ForeignKey(u'model.id')), Column(u'source_id', Integer, primary_key=True, ForeignKey(u'source.id')), ) # Persistent storage for installed projectors # model_id would be an index for model_table.id t_installed = Table(u'installed', metadata, Column(u'id', Integer, primary_key=True), Column(u'model_id', Integer, ForeignKey(u'model.id')), Column(u'name', String(20)), ) -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
If I'm understanding correctly, something like the following: # Keep track of manufacturer names manufacturers_table = Table(u'manufacturer', metadata, Column(u'id', Integer, primary_key=True, Column(u'name', String(20)) ) # Keep track of model names models_table = Table(u'model', metadata, Column(u'id', Integer, primary_key=True, Column(u'name', String(20)) ) # Keep track of inputs available to models sources_table = Table(u'source', metadata, Column(u'id', Integer, primary_key=True, Column(u'pjlink', String(2)), Column(u'text', String(20)) ) # Foreign key table for mfgr/model/sources projector_table = Table(u'projector', metadata, Column(u'manufacturer_id', Integer, primary_key=True, ForeignKey(u'manufacturer.id')), Column(u'model_id', Integer, primary_key=True, ForeignKey(u'model.id')), Column(u'source_id', Integer, primary_key=True, ForeignKey(u'source.id')), ) # Persistent storage for installed projectors # model_id would be an index for model_table.id installed_table = Table(u'installed', metadata, Column(u'id', Integer, primary_key=True), Column(u'name', String(20)), Column(u'model_id', Integer) ) Or am I still off in left field? -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
I agree with what Mike said, but I would just suggest renaming projector_table to something like purchased_table or inventory_table. Everything in models is a different model of a projector, so the table names are a bit confusing. In non-database terms, a good way to visualize relationships is to think of a form on a webpage. If you expect to select items from a dropdown select menu, then those things are usually best as something your foreign-key into their own table (or perhaps some sort of enumerated value, but that's another topic). If something is a bit of freeform text you enter, then it belongs in a column. You can also think of ways you'd want to search or display data. You might want to show projectors that only match a specific MFG, or have a specific type of source. Both of those requirements suggest having a seperate table with a foreign key might be a good idea ( vs a fulltext search ) That said, I'm unclear about what sources are. If a source is an AV input, then the source types would be global and shared. So a more normalized DB would have the sources in: sources_table = Table(u'source', metadata, Column(u'id', Integer, primary_key=True), Column(u'label', String(20)), ) model_2_source_table = Table(u'model_2_source', metadata, Column(u'model_id', Integer, primary_key=True, ForeignKey(u'model.id'), Column(u'source_id', Integer, primary_key=True, ForeignKey(u'source.id' ), ) If a source is a local input in your building though, then you would want to associate the 'source' with the inventory item you are tracking. -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
On Monday, July 21, 2014 8:38:54 AM UTC-7, Jonathan Vanasco wrote: I agree with what Mike said, but I would just suggest renaming projector_table to something like purchased_table or inventory_table. Everything in models is a different model of a projector, so the table names are a bit confusing. snip Short answer: manufacturer/model tables are projectors, and sources are the video inputs available for that particular model. The sources table is going to be used to keep track of what the manufacturer has listed in their documentation for selecting a particular video source. The projectors_table is persistent storage use for what projectors that the user can control via networking. Basic information is some text columns that the end-user can assign for their own (short) notes, but the model_id field is used so that the text can be matched with the projector that they have control over. Longer answer. Column 1 is PJLink code for selecting that input, the rest of the line is text. Example for Eiki model LC/XL200 projector: (Text is from Eiki webpage control) 11 RGB (pc analog) 12 RGB (Scart) 13 RGB (PC Digital) Example Hitachi CP-X2514: (Text is from PJLink user manual from Hitachi) 11 Computer IN 1 12 Computer IN 2 13 Component As noted, different manufacturers may have different text for the same inputs, so the sources table is just keeping track of the text for the input source - hopefully text that the end-user does not find too confusing :) This is not an inventory program. The part I'm looking to add is basic projector control to a program that will send some output via a second computer video output to a projector. One point is that there may be multiple computers connected to a single projector via multiple inputs (sources). I was thinking of having manufacturer/model/source tables so the end-user doesn't have to re-enter the information if they just happen to have multiple projectors with the same model - as a side possibility, also having an xml file with this information available that can be imported into those tables. When the end-user adds a projector to the program, they can select the projector by manufacturer (Eiki) - model (LC/XL200) - then the sources (video inputs) would be added to the projector class so they can then select the video source to display. Since using PJLink codes would be confusing (What does input 11 select?), the text pulled from the sources table would then let them use the familiar text (documented in their projector user manual - like RGB (pc analog) ) to select the source. An example xml file for importing would look something like: projector manufacturer='Eiki' model name='LC/XL200' source pjlink='11'RGB (PC analog)/source source pjlink='12'RGB (Scart)/source source pjlink='13'RGB (PC digital)/source /model model name=.'...' /model /projector With the importing in mind, there still has to be the option for the end-user to manually add an entry for projector manufacturer/model/sources (technical note, with PJLink, I can retrieve the manufacturer name, model name, and the available sources via the network, just not the text for the sources). With that, then Jonathan's suggestion of removing the foreign_key on the sources table and create a 4th table that keeps track of the model-sources constraints. As for the projectors_table, instead of a foreign_key just use an integer column as an index into the models_table would be the suggestion? projector_table = Table(u'projector', metadata, Column(u'id', Integer, primary_key=True), Column(u'model_2_source_id', Integer) ) The way things are looking, looks like I'm going to have multiple selects. Not an issue, since they will only be used on program startup, not during normal operations. -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
Forgot to mention that during typical operation, the only time the database will be accessed would be during down time (add/delete) or program startup (retrieve list of projectors to control) - not during a presentation. -- 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.
Re: [sqlalchemy] Multiple tables and foreignkey constraints
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.
