12 Replies Latest reply on May 1, 2014 10:04 AM by philmodjunk

    Extra Eyes on database diagram

    PatriciaAngelini

      Title

      Extra Eyes on database diagram

      Post

           Greetings.

           I would appreciate it if you could look over this database diagram. I want to make sure my interim tables make sense. Thank you in advance for your help.

           I originally uploaded an older file... Here is the correct one.

           Thank you.

           PJ Angelini

      2scematic_for_cinema_pacific.jpg

        • 1. Re: Extra Eyes on database diagram
          PatriciaAngelini

               I originally uploaded an older version... this one is correct.

               Thank you, again.

               PJ

          • 2. Re: Extra Eyes on database diagram
            philmodjunk

                 Without at least a name for each table to provide some clue as to their intended function, there's not much one can deduce from your diagram.

                 If you are not already familiar with it, you may find this article on AnchorBuoy helpful when it comes time to turn the theoretical structure of your database into actual tables, table occurrences and relationships: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

            • 3. Re: Extra Eyes on database diagram
              PatriciaAngelini

                   Thank you for the link. The table names are in color. Are they not descriptive enough?

                    

                   PJ

              • 4. Re: Extra Eyes on database diagram
                PatriciaAngelini

                     Ok... per suggestions I have reworked it as the "anchor bouy" example. I am not sure how I can make interim table connect here... but your thoughts are welcome.

                • 5. Re: Extra Eyes on database diagram
                  philmodjunk

                       I cannot see any uploaded image in your preceding post. There can be a delay in how quickly an uploaded image appears but since several hours have elapsed since you posted, something kept the image from appearing. If you are using the "upload an image" controls located below the misnamed "Post a New Answer", make sure that you upload an image file of the correct file type. If you upload a file of a type other than gif, jpg or png, the upload appears to work, but no image will appear in your post for others to see.

                       Please note that I did not suggest that you make changes to your original relationships map. Often such a map, either on paper or in Manage | Database | relationships, represents a good "conceptual map" of how you need a database to function. What I suggested was that as you actually implement your design, to organize each part of your interface design around smaller, simpler table occurrence groups that replicate portions of  the function of your "conceptual design" of your relationships.

                       In your original image, I missed the faint highlights behind the table names the first time that I opened and zoomed in on your relationship map. I don't have the time now to take another look, but the table names are there to be seen.

                  • 6. Re: Extra Eyes on database diagram
                    PatriciaAngelini

                         I will try again. I have been uploading them as jpg. This one uses the buoy style.

                    • 7. Re: Extra Eyes on database diagram
                      philmodjunk

                           Does the "kinda pink" highlight labeled "many to many table" supposed to identify the join tables used in many to many relationships?

                           Does that apply to the upper set of TO's?

                           I ask because the MultipleAV and Travel tables are not set up as Join tables and the Presentor and Transporter tables are set up as join tables if I can decode the field names correctly here.

                      • 8. Re: Extra Eyes on database diagram
                        PatriciaAngelini

                             Yes, the kinda pink identifies the join tables. Multiple AV and Travel are not set up as join because of the buoy style. Travel and Multiple IV should be interim/joined tables because of the many-to-many relationship.

                              

                              

                        • 9. Re: Extra Eyes on database diagram
                          philmodjunk

                               But there is no such many to many relationship shown in your diagram Buoy or otherwise. If Anchor Buoy has separated the two parts of the many to many relationship, then I should see a second occurrence of these two tables and I do not see them.

                               Suggestion:

                               It is a good idea to adopt a naming convention that clearly identifies primary and foreign key fields.

                               I use this convention:

                               ParentTable::__pkParentTableID = ChildTable::_fkParentTableID

                               to define a relationship of one record in ParentTable to possibly many records in ChildTable. pk means primary key. fk means foreign key. I use two underscores before pks so that they alphabetically sort to the beginning of a list of fields and a single _ in front of fk fields to make them the first group after the pk field. Note how I also repeat the name of the parent table as part of the field name. In some parts of the FileMaker system, you'll see fields without the table or table occurrence designation and then this helps identify what relationships might use this field as a match field.

                          • 10. Re: Extra Eyes on database diagram
                            PatriciaAngelini

                                 Ok. I didn't know how to show this is a buoy table. Are you saying that I should list a table twice in the design if it is pat of a many-to-many relationship?

                            • 11. Re: Extra Eyes on database diagram
                              philmodjunk

                                   What I am saying is that a join table links two other tables in relationships such that there are at least two one to many relationships from the other tables to the join table.

                                   This looks something like this without Anchor Buoy:

                                   Table1-----<Join>----Table2   (----< means "one to many")

                                   Table1::__pkTable1ID = Join::_fkTable1ID
                                   Table2::__pkTable2ID = Join::_fkTable2ID

                                   With Anchor Buoy, you might have:

                                   Table1-----<Join

                                   Table2------<Join 2

                                   where Join and Join 2 are occurrences of the same data source table. But most of the time, even with Anchor Buoy, you'll see both tables linked to the Join table as you usually need that additional relationship to get things to work right.

                                   So, for example, I see a table in your chart named Travel that is identified by color as a "join" table, but I do not see it linked to more than one table--thus it cannot be a join table. And I see a table named MultipleAV that while linked to two tables, does not have the needed pair of fk fields to be a join table.

                                   This may not be a case of wrong design. It may be that you've simply identified two tables as Join tables that aren't really join tables. But the MultipleAV table looks like it may be missing a match field.

                              • 12. Re: Extra Eyes on database diagram
                                philmodjunk

                                     However, I see another potential issue that is of much greater concern.

                                     You appear to have these relationships:

                                     Event-----<Presenter>------Presentation

                                     Event::ID = Presenter::EventID
                                     Presntation::ID = Presenter::PresentationID

                                     This relationship would seem to indicate that while several presenters might team up to provide one presentation, a given presenter can only provide one such presentation. Couldn't a presenter provide more than one presentation at the same event? Looks like you need a join table here.

                                     And since you have a table of events, what data will you put in presenter when the same presenter participates in more than one event? (You may want to split the data in this table into two parts, one for the presenter's name and contact info and one for the presenter's participation in a given event (honoraria, presentation id, etc...) )