Years - year that a return is filed for
What fields would be in this table?
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.
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.
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.
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.
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.
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).
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?
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?
So the year appears to be an simply an attribute of the return table.
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.
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.
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 ).
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.
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.
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.
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.
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.
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.