14 Replies Latest reply on Jul 23, 2012 1:30 PM by jjfcpa

    Newbie relationship design

    jjfcpa

      I am creating a new solution for internal use. It's a document management system for tax return preparation. I have the tables and relationship completed but just wondering if I"m on the right track. Here is the basic layout of the relationships that I created.

       

      Tables

      Client - contains info about client

      Years - year that a return is filed for

      Returns - one record for each return that is filed for the client in any particular year

      Return_Copy - one record or more for each "copy" of the return filed (instead of keeping printed copies, this would be a PDF copy of the return)

      Documens - one record or more for each document supporting the return files (this may be split into two documents files, one that would be documents filed WITH the return and one that simply SUPPORTS the information in the return

       

      Relationships

      Client table is linked to Years table in a many-to-one relationship

      Years table is linked to Returns table in a one-to-many relationship

       

      Returns table is linked to a "Copy of the Return" table in a one-to-many relationship

      Returns table is also linked to a "Documents" table in a one-to-many relationship

       

      It's a fairly simple relationship where each client can file many returns any given year. And each return can have a copy of the return attached to it and one or more documents attached to it. Since this is a pretty complete system to create, I know the importance of getting the relationship right before I start.

        • 1. Re: Newbie relationship design
          comment

          jjfcpa wrote:

           

          Years - year that a return is filed for

           

          What fields would be in this table?

           

           

          jjfcpa wrote:

           

          Return_Copy - one record or more for each "copy" of the return filed (instead of keeping printed copies, this would be a PDF copy of the return)

           

          I don't quite see what purpose is  served by having a separate table here.

          • 2. Re: Newbie relationship design
            jjfcpa

            Michael

             

            The Years table is jsut three fields - ClientID (foreign key to link to Client table) and (YearsID which is its primary key) and the Year field.

             

            Each year can have multiple returns, such as, Federal, Nebraska, Iowa, etc., so the Returns table will contain certain summary information for each return filed, and the Returns_copy will be just a container field with a PDF copy of the return itself.

             

            The Returns to Returns_Copy is one to many because you may have a case where you create the return using two different applications, such as, Proseries and Superforms.  An example of this would be a state return, such as Nebraska.  You can create the basic Nebraska return using Proseries, but if you have certain tax credits - such as the Nebraska Advantage Act - Proseries doesn't createt the necessary forms so you'd use a "form" application, such as Superforms, to create the necessary forms.

            • 3. Re: Newbie relationship design
              comment

              Perhaps I am not following this through, but a year cannot have a ClientID foreign key, unless it belongs to a specific cient. So you would need a year record for each client/year combination -  and the table would still carry practically no information.

               

               

              Re Returns_Copy: it sounds like this would be better solved within a combined Documents table - where each document could be either an enclosure, or a supporting document, or a copy of the submitted return.

               

               

              You didn't say anything about the returns themselves. I presume you see it as a big bunch of dedicated fields - but it's also possible to see each amount as an individual record in a child table.

              • 4. Re: Newbie relationship design
                jjfcpa

                Michael

                 

                Thanks you for your reply.  Since I'm new to this, I really appreciate your comments.

                 

                So let me see if I can explain my relationships and perhaps you can provide some alternatives to my approach that would be more beneficial.

                 

                Each client can have many years tax returns.  So I see this as a one to many relationship.  Each year (for each client) can have many tax returns, hence another many to one relationship.  I see the years table as simply a join table.  I can't really go from clients to returns (which would be a one to many also) because I really want to tie all the returns for each year together - hence the years table.

                 

                The returns table contains just basic information about the specific return. 

                 

                I see what you mean about combined returns_copy and documents table, but I'd really like to present these in separate portabls so it is easier for the user to see these separately.

                 

                Another thought that crossed my mind that may help.  It's important to be able to select a client and a year and then see all the returns for that client and that year.  The more I think about this, the more complicated and confused I get.

                 

                Once again, I appreciate your comments and any direction you can provide.

                • 5. Re: Newbie relationship design
                  comment

                  jjfcpa wrote:

                   

                  Each client can have many years tax returns.  So I see this as a one to many relationship.  Each year (for each client) can have many tax returns, hence another many to one relationship.

                   

                  I am afraid that still doesn't make sense to me. IIUC, you're describing the following:

                   

                  Clients -< ClientYears -< Returns

                   

                  where you need to precreate a year for a client, before you can create a return for that year.

                   

                  As an aside, this is a parent -< child -< grandchild structure - there's no join table here. Still, recording the years is redundant, since there is nothing to record about them.

                   

                   

                   

                  jjfcpa wrote:

                   

                    I can't really go from clients to returns (which would be a one to many also) because I really want to tie all the returns for each year together - hence the years table.

                   

                  What do you mean by "tie all the returns for each year"?. If this is about viewing all client's returns in a selected year, it is very simple to achieve by filtering a portal or a relationship.

                   

                  The same applies to your concern about different types of documents. Just because they live together in the same table, they don't have to appear together in portals (or in found sets, for that matter).

                  • 6. Re: Newbie relationship design
                    jjfcpa

                    Michael. 

                     

                    Keep beating the drum, I think it's starting to get in.  I'm so used to structuring the data anyway I want and then using SQL to get what.  I'm starting to realize how weak my database design skills are.  So let's see if I'm starting to understand this a little better.

                     

                    So, at this point, I don't think I need a Years table.  I just need the "years" field in the "returns" table and I'll be able to filter out all the returns for a client for any given year?  You are correct, before you can add a return, you also need to open a year for that return.  So the year appears to be an simply an attribute of the return table.

                     

                    If I can broaden this perspective just a little, let's say I have a screen with a portal on it showing all the clients.  From this screen I can either add or edit client data ... or... go to another screen that shows all the years that I have returns for that client.  From there you can either add a new year or view the returns for an existing year. 

                     

                    Am I getting closer?

                    • 7. Re: Newbie relationship design
                      comment

                      jjfcpa wrote:

                       

                      I just need the "years" field in the "returns" table and I'll be able to filter out all the returns for a client for any given year? 

                       

                      Yes.

                       

                       

                       

                      jjfcpa wrote:

                       

                       

                      So the year appears to be an simply an attribute of the return table.

                       

                      Exactly.

                       

                       

                       

                      jjfcpa wrote:

                       

                      let's say I have a screen with a portal on it showing all the clients. 

                       

                      You could - although as there is no parent object of which all clients are children, you may just as well start with a list view of the Clients table.

                       

                       

                       

                      jjfcpa wrote:

                       

                      go to another screen that shows all the years that I have returns for that client.  From there you can either add a new year or view the returns for an existing year. 

                       

                      IMHO, you should have portal - on a form layout of the Clients table - showing all the returns for that client. And this portal could be filtered, so you could select a year (in a global field) and then only the returns for the selected year would be shown. Anyway, these are details that concern the user interface, not the data structure.

                      • 8. Re: Newbie relationship design
                        timwhisenant

                        Hi,

                         

                        What Michael is trying to explain is that, year is an attribute of return as is also return type, i.e. Federal, Nebraska, Iowa, etc.

                         

                        The Client à Returns, Client à documents, Returns à documents

                         

                        I see three tables here. Returns and documents each  would have a year attribute.

                         

                        Client = data about the persons (name, dob, most recent address)

                         

                        Returns = info for the tax return (year, filing status, exemptions, deductions, address used on the return, notes, etc) relate to supporting items in the documents table

                         

                        Documents = all documents that need to be stored ( source docs, research docs, return copies, etc)

                         

                         

                         

                        In practice, the tricky part is tracking taxpayers. MFJ filers are actually 2 taxpayers allowed to file together. In today’s environment, they will not always file together. Because of this, make provisions for the ever-changing client ( married today, not married tomorrow ).

                         

                         

                         

                        My 2cents,

                         

                        Tim

                        • 9. Re: Newbie relationship design
                          jjfcpa

                          Michael

                           

                          Thanks for the quick reply. 

                           

                          When I started the Clients layout, I quickly realized that a portal is not appropriate - as you pointed out - and changed to a list view. 

                           

                          I think the DB design is pretty clear thanks to your insight.

                          • 10. Re: Newbie relationship design
                            jjfcpa

                            Tim

                             

                            The tables I ended up with are Clients, Returns, Documents, so I think I am in agreement with Michael and your advice.

                             

                            Yes, I see what you mean about the MFJ one year and MFS the next year.  Interesting problem with as yet and unresolved solution, but I'll give it some thought. 

                             

                            This is almost a case where the "joint" return is a different client.  Perhaps a way to relate one client to another - MFJ to MFS would be one way to do it. 

                             

                            Any ideas?

                            • 11. Re: Newbie relationship design
                              timwhisenant

                              Yes, client is person, return is join table where persons file together. Since no more than two persons can file together on a single return, in the return table have three fields (primary taxpayer, secondary taxpayer, taxpayers (calculated field = primary taxpayer & ¶ & secondary taxpayer).  For taxpayer id do not use SSN( security risk, possibly a violation of your privacy notice and on rare occasions they can change), use a uuid or some other record identifier. Populate the primary taxpayer field and secondary taxpayer field with this record identifier. To see which returns a client is a party to connect the client:id = returns:taxpayers field. This way the relationship will find all returns regardless of the which position the client is in (primary or secondary).

                               

                               

                               

                              This should add to your food for thought.

                               

                              Tim

                              • 12. Re: Newbie relationship design
                                comment

                                timwhisenant wrote:

                                 

                                Returns and documents each  would have a year attribute.

                                 

                                Just a small nitpick: a document has no independent year attribute  - it's taken from its parent return.

                                • 13. Re: Newbie relationship design
                                  timwhisenant

                                  Accountants sometimes need images of driver license, social security cards, other permanent/periodic documents which would be a direct child to the client table and not the return table. So maybe not  a year attribute, rather an expiration date attribute.

                                   

                                   

                                   

                                  Tim

                                  • 14. Re: Newbie relationship design
                                    jjfcpa

                                    Tim

                                     

                                    I have to admit that in the 30+ years as a CPA, I've never asked to see a taxpayers driver's license or social security card; however, there may be other documents that should be attached to the client record, such as, copies of prior year returns prepared by another accountant.  Nevertheless, I see your point.