1 2 Previous Next 15 Replies Latest reply on Jan 14, 2013 7:07 AM by philmodjunk

    Share 1 address table?

    EP

      Title

      Share 1 address table?

      Post

           Is it common for 2 tables in a solotuion to share 1 address table?  For example, you have a Contacts table that uses a separate table for addresses and you also have a clients table that shares the same address table; or should the contacts and clients table each have their own address table?

           Thanks

        • 1. Re: Share 1 address table?
          philmodjunk

               Why wouldn't the contacts and clients all be in the same table too?

          • 2. Re: Share 1 address table?
            EP

                 Different departments.  This solution is 1 file that spans across many departments in a business. It's not really Contacts and clients (I just used that to keep things simple)

            • 3. Re: Share 1 address table?
              philmodjunk

                   I would put different departments in the same table in many cases also.wink

              • 4. Re: Share 1 address table?
                EP

                     The departments are actually Contacts (marketing, part of the CRM solution) and medical Patients.  The marketing team shouldn't have access to patient records, hence the separate table.  With this being said, would you still use 1 address table, or an address table for each? Thanks

                • 5. Re: Share 1 address table?
                  philmodjunk

                       Use record level access control in Mange | Security and you can put your data in one table, but the marketing team can be kept from seeing the patient data without needing to put the data in separate tables.

                       I'm not trying to be difficult here. Data Modeling is one of the most challenging aspects of Relational database design and requires a complete picture of the data and the needs of the project before you can properly set up tables and relationships. And it's often an iterative process where you repeatedly review and modify the model as user needs and your understanding of the project changes.

                       Should you put all addresses in the same table? Probably, but much depends on how this data will be used and to what other data it needs to be connected.

                  • 6. Re: Share 1 address table?
                    EP

                         Phil, I'm too far into the solution to put patients and contacts together (it is live on a server).  Would I be better off rebuilding into 1 table (contacts and patients) and then importing the data from the appropriate table? I mean, will I have long term issues by having multiple tables?

                          

                         If I leave the tables as is (separate), I believe I would need to use separate address tables as well, to keep the _fk on the address tables from getting messed up, unless I use 1 address table with 2 different _fk's. 1 linked to the contacts _pk, and 1 linked to the patients _pk?

                         P.S. I know your not trying to be difficult!!! I appreciate your feedback, it is invaluable!

                    • 7. Re: Share 1 address table?
                      philmodjunk

                           My initial response to this is to repeat the 2nd to last paragraph of my previous post.

                           It's really a matter of looking at the overall structure of your systems and asking yourself whether or not it will meet the needs of your users efficiently. And whether different design options might or might not reduce the chance of incorrect data being entered into the system. I can't look at the description of just a few parts of the whole and give you a definitive answer.

                           Deciding whether to put similar but not identical data in the same table or two separate tables is one of those questions that does not have a simple answer.

                           Here are some questions (no need to post the answers) you can think about that may help you to decide:

                           1) Is the data you have in two separate tables ever going to appear in the same report? If so, a combined table will generally make this easier to do. If you can't imagine ever needing to create that kind of report, whether to put the data in one table is much less of an issue. If it's possible that someone may want to know how many contacts are also patients or to see a list of such...

                           2) How much overlap is possible in the data recorded in the two tables? You have patients and contacts. It would seem likely that a contact could also be a patient. With separate tables, a contact that is also a patient means that you put the same information into both tables. If the need arises to update that information--perhaps a name change, address change or an error is detected, you have to find and make those changes twice.

                           Insufficient reasons for putting the data into two tables:

                      To control access.
                           As previously stated, Manage Security can control access on a record by record basis to separate tables are not required to limit user access.

                      To get relationships to work. To simplify the relationships graph by reducing the "webbing" effect...
                           Since you can create any number of Tutorial: What are Table Occurrences? for the same table and organize them using the Anchor Buoy method, you don't need separate physical tables to achieve this result.

                      In one layout, you see records from both sets of data.
                           This one's more of an issue, but scripts, script triggers, scripted finds that contstrain the found set and custom menus can all be employed to keep a user from accidentally pulling up data from set A on a layout intended for accessing data from set B from the same table.

                      • 8. Re: Share 1 address table?
                        EP

                             Thanks Phil.  Makes a lot of sense.  My thinking was a bit different when creating the solution.  Since patients aren't contacts too often, I had figured that I would make a Contact called "Patient" and any time a patient refers another patient, the intake person would se,ect "Patient" as the referrer and the actual patients name making the referral would be input in the "Method" field.    This would allow for reports where we can see how many patients referred other patients, and I could always create a portal that would list the "method" / patient name.  

                             I'm considering merging the 2 tables after reading your posts, I just need to decide if it is worth the time it will take.  If I choose not to, can you shed some light on the question from my prior post:  If I leave the tables as is (separate), I believe I would need to use separate address tables as well, to keep the _fk on the address tables from getting messed up, unless I use 1 address table with 2 different _fk's. 1 linked to the contacts _pk, and 1 linked to the patients _pk?

                             Thanks

                        • 9. Re: Share 1 address table?
                          philmodjunk

                               In terms of how a user interacts with a database, both options will function pretty much the same. If there is a chance that at some point in the future, the patients and contacts tables may be merged, having the address records all in one table may save you a few steps in the merge process.

                               Here's another option that occurred to me:

                               Patients-------Contacts--------?Addresses      (I used ? here because you may or may not need more than one address for a given contact.)

                               Every patient would be a contact, but not every contact would be a patient.

                          • 10. Re: Share 1 address table?
                            EP

                                 Patients-------Contacts--------?Addresses

                                 Interesting idea.  In an existing system, how would I set this up with minimal "mess" occurring?  

                                 Also, not sure if you answered this previously, but if I leave the tables separate (as it is currently) can I share one address between the 2 tables by having separate foreign keys in the address table to link to the patients and contacts parent keys?

                                 thanks

                            • 11. Re: Share 1 address table?
                              philmodjunk

                                   That's why I posted this:

                                   In terms of how a user interacts with a database, both options will function pretty much the same. If there is a chance that at some point in the future, the patients and contacts tables may be merged, having the address records all in one table may save you a few steps in the merge process.

                              • 12. Re: Share 1 address table?
                                EP

                                     Great thanks.  I've set up the address table and linked it to both tables.  How exactly would I set up patients---contacts as you mentioned? Would I just link the tables via certain fields?

                                • 13. Re: Share 1 address table?
                                  philmodjunk

                                       There are multiple options you can evaluate to see which work best for you. The relationships might be:

                                       Patients::_fkContactID = Contacts::__pkContactID

                                       Contacts::__pkContactID = Addresses::_fkContactID---if there are many addresses for one contact

                                       or

                                       Contacts;;__fkAddressID = Addresses::__pkAddressID----if there are many contacts for one address

                                       and you'd use a join table between contacts and addresses if there are many contacts with many addresses.

                                  • 14. Re: Share 1 address table?
                                    EP

                                         Thanks.  I am going to experiment with this: Patients::_fkContactID = Contacts::__pkContactID 

                                         How would you implement this on the contacts layout?  I think this relationship will allow office users to see the entire contacts table, with patients mixed in when performing a portal search (I use name search portals) for finding contacts.  Is this correct? I also believe that the marketing team will not see patients mixed in with the contacts layout (which is preferable).

                                          
                                    1 2 Previous Next