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.
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
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.