1 2 3 4 Previous Next 57 Replies Latest reply on Aug 24, 2009 8:46 AM by philmodjunk

    New database project, struggling to find a table/relationship structure that fits



      New database project, struggling to find a table/relationship structure that fits


      Hi everyone,

      I'm relatively new to FMP but have worked with databases & tables before - unfortunately not experienced enough to know the answer to my question, so I need everyone's help! Somebody handed me this project after they had a crack at it, they had no experience with databases at all and had watched a few videos and suddenly reached their limitations.  So the tables & layout are already in place, but the database isn't live so I can carve it up however I need to.  If my description of the fields sounds a bit weird, it's because I've had to change a few details for confidentiality reasons.

      The simplest way to describe the database is to say that it starts out as a simple contact database - a Person table with fields like name, address, nationality, photo, next-of-kin, etc etc.  Additional information stored per person is passport, visas held, current annual leave details, and radios 1 & 2 (some of the people on this database carry 2-way radios).  Because this database is for managing a team of staff & security which travel around a lot, its main function will be to manage the travel paperwork, visas, declarations, etc for the group of people, associated staff and security people.  Currently all the travel paperwork for a team of 30 people has to be filled out every few days, and the idea of using an FMP database is to have this paperwork produced with a few mouse clicks instead, as well as making it easier to track, report on, etc.

      So the database design currently consists of a Person table with those records, as well as a primary key field PersonID.   There is also a Radios table containing fields serial_num, make_model, battery_type, and PersonIDfk as the foreign key.

      Anyway so my basic problem is this:  in the case of security staff, they may carry 1-2 radios as part of their duties.  These radios get reassigned from time to time, but a person will typically keep their radios for the duration of the overseas trip.  So you can say that each record in the Person table may have 0, 1 or 2 radios.

      The original author of the DB had created a Radios table and used a 1:many relationship, and in the Person layout they had tried to tie the two radio "slots" (by which I mean, sets of fields (serial number, make_model, etc) for radio 1 and radio 2) into that Radios table.  However this doesn't work for a multitude of reasons, as I found out - I even tried creating a second table-occurrence of Radios in the relationships graph, but that didn't behave as required, you still end up with two slots displaying the same radio - presumably because, in the case of a 1:1 relationship (with two table-occurrences of Radios) FileMaker looks through the Radios1 occurrence for a PersonID=PersonIDfk match and pulls out, say, radio #123 and so your first radio slot on the Person layout now shows the info for radio 123; it then looks in the Radios2 occurrence for PersonID=PersonIDfk match but of course it finds the first match (123) instead of finding the second radio assigned to that person, say radio #456.  So both radio slots on your Person form now show the info for radio 123.

      It seems that it's quite easy to make a DB that could handle one radio per person, just set up a 1:1 relationship between your radios table and your Person table.  Likewise with your typical Customers and Orders DB, you have a 1:many relationship because a customer can have between 1 and infinity orders.

      But I am really struggling to find a way to implement a 1:2 relationship, e.g. a 1:many relationship where the Person cannot have more than two Radio records associated with them (and there has to be uniqueness, e.g. you can't assign radio#123 twice to Person A, or radio #123 to both Person A and Person B).  I did try an implementation where the radio fields are simply stored within the Person table (i.e. a single table approach), but after consulting with the end customer it turns out that radios do get reassigned between security staff fairly often, so it does make more sense to hold this information in a separate table, so that reassignment is merely a case of altering links between a Person record and the associated Radio records.

      I hope I've explained myself well, and I'm hoping that there's an easy answer to this.  As you may have gathered, I understand tables and fields and records reasonably well but don't have a lot of experience in DB design, and my project is not something which easily fits any of the included templates or training examples out there.

      It could even be that I need to start from scratch with this, and find a better way to work with this information.  For example, what if the radios assignments were done on a separate layout which worked directly with the Radios table, and this information was only *displayed* (read only) via a "Look up related records" area on the Person form?  Or would I still come up against the same issue in this separate radios layout..?


      Any suggestions welcome!





        • 1. Re: New database project, struggling to find a table/relationship structure that fits

          Look up "Portals" in your on line help. A portal is a tool for displaying a list of multiple related records in a "window" or subform on your layout. By placing your related radio records in a portal, you thus will have a scrolling list of all related radio records.


          In your task bar there is a Portal tool. Click it.


          Now use your mouse to draw a rectangle on the screen where you want to place your portal.


          A wizard will pop-up that takes you through the steps for setting up your portal.


          If, after that, you have more questions about portals or another part of your DB, feel free to come back here and post more questions.

          • 2. Re: New database project, struggling to find a table/relationship structure that fits

            Hi there,


            Firstly thanks for your reply.  Thanks for the pointer regarding portals, I think that's half the battle won, but I also believe that my main problem is more a structural issue.  A portal will allow me to easily list the other table's fields but I don't think it will address the error condition where there are more than 2 radios assigned to a person, and allow easy reassigning of radios... It is vital that no one person is assigned more than 2 radios simultaneously, but they are reassigned for each trip.  In a way, I need 2 x 1-to-many relationships between Person and Radios... or, I need a 1:many relationship which is somehow limited to 2 radios.  Half-way between 1:1 and 1:many really...?


            I hope I'm explaining myself well enough - one of the pitfalls of newbies, we don't always ask the right questions!  Any further ideas?


            thanks again



            • 3. Re: New database project, struggling to find a table/relationship structure that fits

              There are a variety of ways to limit the number of assigned radios to a set number. Here's a real basic approach that might work: Are the radios always assigned directly from the "Persons" Layout?  If so, you could limit your portal to show only two related records. (Enter 2 in the number of rows box in Portal Setup... Don't choose the vertical scroll bar option.)


              There are very good reasons why that might not work for you. It depends on the design of your database and how you want your interface to work.


              If that won't work, let me know why and we can look at more sophisticated approaches.

              • 4. Re: New database project, struggling to find a table/relationship structure that fits

                Hi there,


                Thanks again for your response, as always it is much appreciated.  I have given it a quick try - a two-row portal into the Radios table, and here's what I've observed so far..


                Firstly, it's a fairly natural way enforce the 2-radio limit per person.  You'd have to manually mess with the radio table to get into a position of >2 assignments (perhaps at an advanced stage I can have a small script which checks this, triggered at start-up or something).


                Secondly, I'm having a bit of a problem selecting existing radios.  Let's say I have 3 radios in my radio table, serial numbers 123, 456 and 789.  When editing someone's Person record, someone who has no radio assigned to them, I enter "123" into the "serial number" box.  My serial number validation warning box comes up, of course, because it's as if I'm trying to create a new radio with serial number 123, which conflicts with an already existing radio.  So a portal is allowing me to either view existing related records, or create new records, but not recall an existing record by selecting its serial number (or typing it in).  Good for viewing, not good for reassigning.


                It may be that I'm doing something wrong here, or that I have unreasonable/naive expectations?


                I will wait eagerly for your answers :-)


                thanks again



                • 5. Re: New database project, struggling to find a table/relationship structure that fits

                  It sounds like you've made a good start.


                  I'd modify your validation process as follows:


                  Create a table to list all your radios and use it as a "sign out" sheet for checking to see which radios are currently available and which have been "checked out".


                  At the very least, you'd need two fields in your table:

                  Serial Number (to uniquely identify each radio)

                  UserID   (The ID of the person to whom the radio is currently assigned).


                  With the above two fields, a blank UserID field would indicate that the radio is unassigned and available. Chances are, however, that the above approach is too simplistic. You might need to schedule the same radio far enough in advance that you need to know that "Joe Smith" will have the radio from 6/1/09 - 6/20/09 and "John Doe" will have it from 6/25/09 - 7/2/09. In that case you would add two date fields to your radio table to record the date interval. Your validation calculation would then have to compare date fields as well as check to see if the selected radio is unassigned (has a blank UserID field).


                  Not only can you set this kind of validation rule if you set up the right relationships, you can also define a value list of available radios that won't show a radio in the list unless it is available during the time interval you need.


                  Let me know what you think, and I'll dive into the details with you if you need the help.

                  • 6. Re: New database project, struggling to find a table/relationship structure that fits


                    Thanks again for taking the time to continue helping me out, I really appreciate it.


                    Can I just pause on that portal idea for a second and tell you something I've tried out.  The whole thing I've still been bothered by, is the seeming lack of ability to define a 1:many relationship which limits the 'many' to '2' maximum (or two 1:1 relationships).  What I've struck upon, is a pretty bulletproof idea, by going right back to the table structure and finding a way to have a Radios table which allows me to use relationships to define not only who the radio is assigned to, but which 'slot' of that person it is (since each Person will have 0, 1 or 2 radios).  In essence, I wanted to create in table Person some sort of 2-dimensional key value which describes not only the Person record number, but the Radio slot (e.g. 1A refers to Person record 1, radio slot A).


                    What I've done is fairly cunning (well, for a newbie like me :).  Firstly my Person table now looks like this:


                    PersonID (number, auto-enter serial, must be unique)

                    PersonIDA (calculated value = PersonID & "A")

                    PersonIDB (calculated value = PersonID & "B")

                    Radio table has an extra field, but I'll list all the pertinent ones here so we don't get confused:


                    PersonID2Dfk (must be unique)


                    Then I created two table occurrences of Radio in the relationships graph, each with a relationship back to table Person (I've used Table : Field notation with a space on either side of the colon otherwise it shows up like this :P )


                    RadioA, 1:1 relationship with Person table, criteria is Person : PersonIDA=PersonID2Dfk

                    RadioB, 1:1 relationship with Person table, criteria is Person : PersonIDB=PersonID2Dfk

                    So RadioA will return one row only and that is the radio assigned to PersonIDx in slot A, and so forth for RadioB/slot B.  Not only that, but because PersonID2Dfk has validation for uniqueness, it's impossible to assign more than one radio to Person[x] slot A, and same with slot B.  Result = a person can't have more than 2 radios assigned to them.  Serial_number uniqueness validation ensures that you can't assign the same radio to two separate table rows.  So now my Radios table looks like this (column/field order: PersonID2Dfk, PersonID, serial_number, make_model):

                    01A    01    123    Motorola GP330
                    01B    01    456    Motorola GM380
                    05A    05    999    ICOM XYZ123
                    05B    05    987    Kenwood ABC987
                    16B    16    555    Samsung Widget2001

                    In my Person layout, I have fields like this:


                    Radio A:       Serial number: [display data from related table RadioA, field serial_number]
                    Radio B:       Serial number: [display data from related table RadioB, field serial_number]

                    And it works really well, I suppose there's other ways I could have done it... but there's just one problem...

                    It would be *nice* if the Person layout allowed you to select a radio from a dropdown box (serial number).  But I can't get this to work, if I set the serial number control to "Dropdown" and use a value list which comes from the Radio table's "serial number" field.  Why?  Well, I reckon it's because the Serial Number field on the Person Layout is set to display a field from a related table.  Dropdowns just don't work, and I speculate that FileMaker Pro won't let a display-field-from-related-table behave as a dropdown control.

                    So, I'd really like to know firstly what you think of my structural solution to the problem of wanting either a 1-to-many (limited to 2) or 2*1-to-1 relationship... if you've come across this before, if you think in your experience that it's an elegant solution without too many (or any) pitfalls etc.  

                    Secondly, I'd like to know your thoughts on the dropdown issue.  Am I ever going to be able to edit those fields?  Or should I merge your idea and my idea and put a 1-row portal (display table occurrence RadioA) in Person layout (radio slot 'A') and a 1-row portal (tab.occ. RadioB) in Person layout (radio slot 'B')?  Would that allow me more edit-friendly functionality?

                    This is starting to get really fun!

                    thanks in advance,


                    • 7. Re: New database project, struggling to find a table/relationship structure that fits

                      Hmm, Where do I start with a response to this?


                      Obviously, what you've set up works and you understand how and why it works and that's no small thing.


                      Ironically, this is approach I was specifically trying NOT to recommend! :smileywink: Here's why:


                      Reason 1: 


                      Let's say your business practices change and now you find that you need to assign 3 radios to a person instead of 2. With your approach, you'd have to define additional calculation fields and additional relationships/table occurrences as well as layout changes in order to implement the change. With a portal limited to just 2 rows, and corrected validation checks to avoid the issue you've described, you'd just have to open up the portal setup dialog to specify 3 rows instead of two and then adjust your layout, if necessary, to accommodate a slightly larger portal. Few, if any other, changes are likely to be required.


                      Reason 2:


                      A single relationship, with a single table occurrence, is much easier to work with when designing reports.


                      So, I'm not recommending against your approach because it won't work, but rather suggesting my original approach as being more flexible.


                      With regards to value lists:


                      Filemaker can build a value list from any indexed field in any table in your database system. If it doesn't, then some detail in the design of your tables is giving you results different from what you expect. I'd need to know more about how you defined your value list, what you expect to see in the drop down list and what you expect to happen when a value is selected, before I can begin to suggest why it "didn't work".


                      Hang in there, it's good to see you are figuring how things work. If you want to continue with your approach, go ahead, just be sure to understand the trade offs you are making with your approach.

                      • 8. Re: New database project, struggling to find a table/relationship structure that fits

                        Hi again,


                        Thanks for that good reply - funnily enough I had just been having this similar discussion with a friend of mine who's an experienced Java developer.  He had also recommended against a rigid 'back-end' structure and prefers a flexible structure with any enforcement of the kind I need (2 radios per person) done via the layout/interface/form.


                        I think maybe I was obsessing about the relationship aspect because I was fascinated by the concept and was sure there must be a way to do it using relationships.  But I also agree with you (and my developer friend - who says that 50% of his work is updating or changing his own code or software, so he knows what a pain it is when he's asked to change an application's functionality, and finding out he has to change the database structure, perhaps destructively).  Now that I've satisfied my urge to master relationships sufficiently well :-) I feel much more open minded and quite keen to try your suggested approach...


                        So I've gone back to the original database I started with, wiped the Radios tab on the Person layout and stuck in a portal.  So far, so good - it displays the 2 radios that are assigned to the current Person.  Now I just need to find a natural intuitive easy-to-use way of allowing radio assignments and swaps.  It could be as simple as a table-view which allows you to find a radio in the list and reassign it to a person.


                        Now, with things as they are, it's a simple portal with two rows, no editing allowed at this stage.  In your mind, what do you think would be a good way of assigning radios to people?  I should mention that out of the 50-70 or so entries in the Person table, only 10 - the security teams - will ever carry radio(s), so whatever ideas you have, it'll only need to operate on Person records which have the "Carrying radio(s)" radio-button to "Yes".  It's most likely that it'll just be 5-7 people who swap radios around.


                        I look forward to your suggestions - even if you only have time to punch out a few words to nudge me in the right direction, I'm more than happy with that, to go off exploring that direction and learn along the way.  This whole project is a great learning opportunity and I'm really enjoying it.







                        • 9. Re: New database project, struggling to find a table/relationship structure that fits

                          I'd suggest reading up on how to do value lists in Filemaker. I think you could define a value list that only lists radios that are available. As in most programming tasks, the devil is in the details and these details depend on your business practices.


                          Consider, how far in advance do you assign radios?


                          Do you have to plan so that "George" gets radios 1 and 2 for June 1 through June 20, and then "Jane" can use them from June 22 through July 1?


                          I suspect that's the case. If so, you'll need to set things up with some availability dates to keep track of who has reserved which radio and for what time period. I think we can set things up in your radio assignment portal so that you enter a pair of dates for the time the person needs a radio, and then you can click into a drop down menu that lists all available radios for that date interval. It may take some testing to make sure that the value list updates correctly, but I am hopeful we could make that work for you.

                          • 10. Re: New database project, struggling to find a table/relationship structure that fits

                            Ok, have watched all the tutorial videos and stuff regarding value lists.  One thing which just occurred to me, and it's probably because I haven't described things well enough...


                            There are no spare radios, and no advance knowledge of dates of swapping.  Using example quantities, there are going to, say, be 5 people who carry radios, and 10 radios.  So if on the current trip Bob currently has radios with serial numbers 123 and 456, and Pete has radios 567 and 890, but on the next trip Bob's going to take radio 890 from Pete and Pete will have 456, how do we deal with that?


                            My feeling is that this sort of swapping (which the customer has told me, is the way these guys swap) suddenly makes this a bit more complex (although the lack of advanced knowledge makes it slightly less complex as we don't have to involve availability-date fields).


                            This is why I have been saying recently that we might be better to look at a separate layout or mechanism for assigning radios, because I knew in my head - but hadn't conveyed it properly to you - that we are talking about swaps here, as opposed to always having a pool.  *Although*, once a staff member releases his 2 radios in order to swap them, you now have a temporary pool of 2.  So the swap process could consist of (1) person A releases one or both of his radios and (2) person B also does that and (3) you then select from a dynamic value list, which radios person A and person B have assigned to them.  Is this what you were getting at?


                            I'm also waiting to hear back from the customer, for some clarification on how he normally handles the swaps.  Is it a case of everyone standing in a room and physically swapping (and don't think that I haven't fantasised about telling the customer "just tell them to stop swapping their %^!£$% radios!!! ;) and then he starts with a clean sheet and enters all their details.... or is it a case of 1 or 2 people swapping and telling him they've swapped, and he simply crosses out and swaps those two numbers on his sheet.  This might give me some direction in terms of what interface the customer would find easiest to use.


                            Let me know what you think about that 2nd-last paragraph.  I believe that we need to look at what a swap actually is - a multi step process of one person releasing an object, storing it in a temporary buffer, the other person doing the same, and then each person in turn taking an object from that temporary buffer.  OR we simply reassign a radio to another person and have validation throw up a dialogue box - "already in use by person B, do you want to reassign?" to which the user can answer "Yes", but then the user has to complete the swap by going to Person B and assigning their radios.  If you're looking at a 3-way or 4-way swap rather than a swap between two people, then you end up chasing from record to record several times before you reach the point where your data is correct.  Perhaps a table view is best, so the customer can see Name vs radio at a glance, put a tick next to the 5 people who are swapping, click a button to clear their assignments, then use a dropdown box (value-list of serial numbers) to assign.


                            Sorry about all the thinking-out-loud... but I figured it was worth letting you know how I'm thinking about things... looking forward to your feedback, as usual!



                            • 11. Re: New database project, struggling to find a table/relationship structure that fits

                              Matching a system design to support current human behavior is one of the biggest challenges in database design. You often have to approach the responsible person with questions and suggestions and help them deside whether they need to change their behavior (It does happen once in a great while :smileywink: ) or whether you need to modify your intended design. I was just dealing with that same issue with one of my projects yesterday.


                              "...you then select from a dynamic value list, which radios person A and person B have assigned to them.  Is this what you were getting at?"

                              That's it exactly, but from what you describe, making that happen may be difficult to impossible. I assumed that since each radio user has a known itinerary, you'd be able to enter a pair of dates that represent the start and end of their trip. Those could then be used to help control what radios appear in your dynamic value list when you set up a new record and assign the user some radios.


                              Without some way to tell whether a radio is availiable for assignment by putting data in a field, you can't make that happen. You may be able to simply use a check box field labeled "in use" and your value list would list all radios where the "in use" field is blank, but again, that depends on the human behavior issue.

                              • 12. Re: New database project, struggling to find a table/relationship structure that fits
                                   Is there a way for me to message you privately?  There's aspects to this project which are confidential, if I could clue you in then it would be easier to discuss the constraints of what we're working with. :-)
                                • 13. Re: New database project, struggling to find a table/relationship structure that fits
                                     Click my forum name. You'll see an option to send me a private message. Please make sure to clearly describe what aspects are to be kept confidential. That will help keep me from accidentally referring to them in any subsequent posts to this thread.
                                  • 14. Re: New database project, struggling to find a table/relationship structure that fits
                                       I've sent you a reply, check the upper right corner for the envelope icon and click it. After we hash this out, I think I can post a brief, safe, summary here and we can return to the thread format if necessary.
                                    1 2 3 4 Previous Next