Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Location
    Chicago
    Posts
    3

    Unanswered: Data Modeling Advice Needed

    Hi there, long-time lurker, first-time poster. I'm somewhat of an Access newbie, so hopefully some of you can get me on the right track!

    I'm working on an Access project at work, and I was hoping I could get some pointers/tips on my data modeling. Basically, attorney information is being kept on an Excel spreadsheet, and I ported it over to an Access database. About half of the firms have a contact attorney, the other half doesn't, but regardless of attorneys there is still data for each firm.

    In many cases, one firm can have multiple locations across the United States. Also, there are unique records that pertain to a firm that are spread out across each of their locations, and there is also information that is unique to each individual location within the same firm entity.

    Here are my tables:

    tblGlobalFirmAttributes
    FirmID
    Frim
    FirmURL
    TINNumber
    CurrentFeeAgreement
    FirmSpecialty
    FirmStatus

    tblFirmLocationAttributes
    FirmID
    LocID
    Address1
    Address2
    City
    State
    Zip
    Fax
    Phone

    tblAttorneyAttributes
    AttorneyKey
    LocID
    Title
    FirstName
    MiddleName
    LastName
    Surname
    AttorneyStatus
    AttorneySpecialty
    AttorneyRate
    AttorneyPreviousRate
    EmailAddress
    MobileNumber
    AttorneyNotes

    My thought process is the following. Each attorney has a location ID, pertaining to a location in tblFirmLocationAttributes (LocID). There is a FirmID key in tblFirmLocationAttributes that connects a location to the firm it belongs to, which is done via the FirmID key in tblGlobalFirmAttributes. Many attorneys can work in the same location, and there can be many locations that pertain to the same firm, and I set up my relationships accordingly.

    Attached is a picture showing my relationships. If you guys can offer some tips and/or advice, it would be greatly appreciated! This is my first time using Access extensively, so i'm all ears for suggestions/constructive criticism. Thanks!
    Attached Thumbnails Attached Thumbnails relationships.JPG  

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Looks pretty good to me. Only thing I see that looks slightly problematic is spaces in your field names. That's not a big deal at all, more of a "best practises" thing. Also make sure that there's no chance of a given attorney being affiliated with more than one office or changing offices at any given time. One database I've designed calculated commission fees for insurance agents. The commission check was sent to the office of the agent and split up from there, sometimes the agents would work at multiple offices or multiple agencies. Things can get tricky from there.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2005
    Posts
    5

    Data Modeling

    I concur with what Teddy said, especially about the spaces in the field names. Where that could cause problems in some future point is if you ever need to tie this database into a larger enterprise database. You would be surprised at how many people start their database thinking it will only be used in their department and then it ends up being tied into the company's database later.

    Also, if there is a chance you may have an attorney working for more than one location, you could add a junction table:

    tblAttorneyLocation
    AttLocID
    LocID
    AttorneyKey (for consistancy, I would suggest renaming this one in the manner in which you named your other key fields)

    Pam

  4. #4
    Join Date
    Sep 2005
    Location
    Chicago
    Posts
    3
    Teddy and Pam, thank you for the suggestions! However, i'm not quite sure I understand what you mean by name spacing. I noticed I left spaces between the "Date Entered" and "Date Expired" fields, but I don't think that's what you're referring to. Could you be a bit more specific and perhaps show me a field or two renamed in the more proper way?

    Chances are this database might actually be tied into our company's database, for now it's simply a department thing that I'm working on. So I'd like to get this done right the first time in order to make it "future-proof" so to speak.

    Thanks again!
    Last edited by ElVicioso; 09-22-05 at 11:21.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Pam and Teddy are talking about the fields:
    Date Expired
    Date Entered
    Folder In Drawer

    And I agree. I makes reading SQL easier when you don't have to add the square brackets to clarify that what you have is one field name and not two.

    I would also suggest changing you table names. My personal preference (take it or leave it) is to have related tables named so that it is more obvious that they are related. For example, I would suggest you have a tblFirm and a table tblFirmLocations. That way they will be listed, in alphabetical order, next to each other. I would then rename your other table tblAttorney or possibly tblFirmAttorney. Depends on how related you feel they are.

    But I would definitely lose the Attributes since it is a given that you will find attributes in an Access table.

    This is just a naming convention that I have developed over time. Helps me to group tables together and in a larger database it is a kind of self documentation. I tend to use the same kind of naming scheme for queries. If I have a process that uses more than one query to run, I will create a root name followed by a numeric and then anything to help remember what it is for. For example:

    qryRptNameCounts001SelectNames
    qryRptNameCounts005GroupNames
    qryRptNameCounts010TotalNames

  6. #6
    Join Date
    Sep 2005
    Location
    Chicago
    Posts
    3
    Ah, that makes a lot of sense. I'm still very much a newbie when it comes to databases, and your tips on a naming schema are great. It makes more logical sense to see the tables grouped similarly, as you have suggested. I've changed the table fields with the additional spaces, and have renamed the tables themselves to make more sense.

    Thanks for the help and tips! They've been very helpful and are much appreciated!

  7. #7
    Join Date
    Sep 2005
    Location
    Schaumburg, IL
    Posts
    28
    hi. I dont know if others will agree with me...

    It will be good if you have a naming convetion for fields which are Text, Memo, Number, Date/Time...

    Like example FirstName to be cFirstName
    c can represent that the FirstName is a Field with Text Data Type

    another is mRemarks

    m can represent that the Remarks is a Field with Memo Data Type..

    So if you mixed it with lets say VB and MSAccess, you can later on knew what specific Data Type you will assign to the Data Field.


    That's all. Have a nice day.

Posting Permissions

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