6 Replies Latest reply on Dec 15, 2010 9:08 AM by philmodjunk

    Help with what tables I need to build

    JohnAnthony

      Title

      Help with what tables I need to build

      Post

      Thanks so much for the help so far.  I can easily design a database to do basic stuff but I would prefer if I am going to do it I may as well learn it the right way.  Let me give you a run down of what I am designing.

      I collect judgments in the court houses.  The Debtor and Creditor and the Judgment itself are all linked together.  On occasion I have the same Creditor going after different Debtors, but for my purposes its best to keep it separate and treat it as a completely different creditor.  So let's treat those 3 as the same.

      I have built the general layout, and I was able to build a table with the court names to automate things better.  Now I am getting into the money side of it, and I was hoping those with experience may be able to help guide me in the though process of how each table should be linked together.

      I have Judgments (which include the judgment, creditor, and debtor in one)

      I have one for courts (so i can have a list of courts to choose from)

      i have one for Assets (this isnt money its just a list so i can add stuff at will)

      ---------

      what I need to do is take payments from debtors, then send a % of that payment to creditors 

      I need to send form letters to both

      I have court expenses (these do not have to be tied to the courts, not necessary)

      and the biggest one of all is sending a monthly report that shows payments from only that month (I will need to do interest calculations and such along with keeping a runnign total of princiapk and interest payments)

      --------

      What tables would you recommend that I build to do all of this.  I hope I dont seem like I am asking someone to build it, I'm not, I will learn it.  But those with experience may have a better idea of how where I should start it so it doesnt get out of control. 

        • 1. Re: Help with what tables I need to build
          philmodjunk

          Your setting up a pretty ambitious project here for someone that appears to be new to FileMaker and possibly new to databases. Figuring out on paper exactly how you want/need this to work and then developing your database to match will not be a simple process.

          I'm going to tackle just one key issue with this post:

          The Debtor and Creditor and the Judgment itself are all linked together.  On occasion I have the same Creditor going after different Debtors, but for my purposes its best to keep it separate and treat it as a completely different creditor.  So let's treat those 3 as the same.

          I don't see any advantages you in pursuing that design approach.

          Not only might you have the same Creditor with more than one Debtor, you may have the same creditor with more than one judgement and the reverse is true with debtors. And a debtor in one judgement might be a creditor in another.

          Treating the three all as one record forces you to enter the same information repeatedly with significant chance of confusion and error that can be greatly reduced with a proper design to your tables.

          I'd use two tables  with three table occurrences for this part of the system:

          Creditors----<Judgements>----Debtors

          Creidtors and Debtors would be different table occurrences of the same contacts table where you'd store the name, phone, address etc of each individual.

          If Table Occurrence is s new term, see this link:  Tutorial: What are Table Occurrences?

          • 2. Re: Help with what tables I need to build
            JohnAnthony

            Yeah I'm aware it's a pretty major endeavor, but I am willing to learn.  Thats why I asked whats the best way to start.  I think for now I will build the database, I will most definitely look into the above, but use it for data and forms, leaving the financial part out for now, as I can still manage that in Quicken.  I will just use this DB for basic recording of numbers for now.

            • 3. Re: Help with what tables I need to build
              philmodjunk

              Please note that the above suggestions were not for the financial part of the system but just the means of documenting each judgement and linking it to records with contact data for each creditor and debtor. The financial details would require additional tables if and when you decided to add that capability.

              • 4. Re: Help with what tables I need to build
                JohnAnthony

                Oh yes I am aware, thats why I thought maybe I should start off small.  Build the database the way it should be and slowly add the more complicated stuff, thanks again

                • 5. Re: Help with what tables I need to build
                  JohnAnthony

                  Hmm, I read up on that and tried it out as the example says but I'm not sure what the use of this will be for me.  Debtors are never creditors (not in the last 5 years for me) or vice versa, and though I do occasionally have a Creditor with multiple debtors, I do not want to run reports for all of them or anything, every judgment has its own creditor and debtor and they are always together, I never want to mix any of them.  I know this doesn't make sense for many business designs but this is unique.

                  • 6. Re: Help with what tables I need to build
                    philmodjunk

                    I didn't suggest mixing them, just a design that avoids having to enter the same contact information on an individual (may he be creditor or debtor), more than once. Putting them in the same data source table is an option that simplifies some of your design. FileMaker is quite capable of keeping Creditor info and Debtor info separate even when the data is recorded in the same table.

                    That said, there's no law that says you have to do it that way, just be aware that you may find that you have sacrificed some future flexibility by putting all this data in the same table.

                    Even looking over my original post reveals that I missed a point: Using my suggestion, you'd need one more table, Judgements, for recording Judgement data and the table I named "Judgments" would be a three way join table linking a Judgement record to a debtor to a creditor in order to fully enable the many to many relationships implied by your original description of your project and that, admittedly, may be more than you want to take one with this project.