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.
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!
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.
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:
AttorneyKey (for consistancy, I would suggest renaming this one in the manner in which you named your other key fields)
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.
Pam and Teddy are talking about the fields:
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:
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!