Results 1 to 2 of 2

Thread: Need easier way

  1. #1
    Join Date
    Nov 2013
    Posts
    1

    Question Need easier way

    What I need to do is create a database that maybe is not so crazy as the one I have created. Here is the idea behind the use of the database. ---- We have models. We are charged monthly by our Lawyers for different models and different reasons. (application process, payments, search in other countries etc.) We want to do an analysis of the cost of each model for the month and then what Lawyer and Employee is attached to that model that month. So here were the fields I used.

    Description of Model ID Number Country Process of model (New, Pending, Granted or General) Monthly charge Lawyer Name Lawyer Amt Lawyer 2 Lawyer 2 amt Employee Employee amt Employee 2 Emp2 amt Annuity General

    This is the information I am tracking. I create a new table for each month of the year. So to get yearly totals of information I had 12 tables to pull from.

    My question is there should be an easier way to create this and track. Any suggestions would be very much appreciated.
    My knowledge is advanced except for writing sql and vb.

    Thanks Sherri

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    first off a separate table for each month is NOT the way to do it. as you have alaready found poullign data from 12 tabels is tricky.. its even worse next year when you may have 24 tables and so on.

    its the same data so it should be in a single table. you cna always pull out based ion a date that identifies the row. I'd expect there to be some form of datestamp per row identifying when somethign happened

    whenever I see simialr column with differnet numbers it alwasy makes me suspicious that the design is wrong. there are cases wher xxx1, xxx2...xxxN make sense, but its rare.
    those too should be pushed to a single sub table. that way round when the scumbag, sorry parasite, sorry lawyer decides to stiff, sorry rob, sorry charge you for say 4 time in a month your model has the flexibility to handle as many payments as required/desired

    model process shoudl be a foreign key to another table called say ProcessTypes. that means if additional process types are required then they can be added as nd when with no further development issue

    storing such thigns as text is never clever computers can see differences between PENDING, pending, Pending, let alone spelling mistakes. using a Process Types table with a foreign key from your main table constrains the types in the main table to be one of the existing types.

    same as the lawyer, employee and so on, all those go into separate tables and are referenced as foreign keys from you current table

    one possible model is as per the PDF
    the PDF doesnt' attempto to identify all columns just the keys or pertinent data
    I'd suggest using the ISO 3 digit code for the country, but its your design if you'd prefer use some other value
    for lawyer and employee its up to you, you can sue an autonumber key (a surrogate key) or use say the employee/lawyer initials

    don't store derived data (unless you have good reasons to). derived data is where you could get the information from elsewhere in the db.
    ferisntance don't store an overall monthly charge if you know what the individual charges are for that month you can alwasy dewrive that at anytime using a query

    the table for accounting period is a bit arbitary. you could use month end dates (heck you could include the motnh end as an attribute of the accounting period).

    if you have separate charges for the same model but different country then you'd need to push the country to the charges tables

    one tghign to bear in mind this pdf is an interpretation of what you may require you'd need to mnake certain its assumptions match your busienss requirements, make certain its fit for purpose. and in any event you should understand what your model does, why it does things that way and so on.
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •