Law Firm Database - How to manage people and cases

[ssanfu]

Also for database design.. we usually remove "s" off of tables.
we already know it is more than one fee.. but you are storing a fee. So the table name is Fee.

Everyone has a preference. I would name the table "Fees"; the table holds more than one Fee. BUT, I would have the PK "FeeID" (singular) because the PK refers to a single Fee. Same with the table "Cases". The table holds more than one "Case". I would name the PK field "CaseID" - the PK identifies one case.

Along that line, I would suggest choosing a naming convention and sticking with it. Again, everyone has their own style (which changes with time). There are many naming conventions - Google "naming convention".

Looking at the "Cases" table and the "Judges" table. Both table have a field named "JudgeID". In a query, form or report, it could get confusing as to which field you mean. To solve that, if the field is a PK, I add"_PK" to the end of the field name and "_FK" if it is a foreign key.
The PK for the judges table would be "JudgeID_PK" and the judge field in the table "Cases" would be "JudgeID_FK".

A friend and mentor showed me a different naming convention. Now it makes perfect sense to me.
Lets take the table "Cases" as an example. I would rename it "Court Case Management". Then the primary key field would be "ccm_CaseID_PK". The prefix (first three letters) is the initials of the table name, the suffix is "PK" and the underscore separates the names. All of the fields in the table for the cases would have a prefix of "ccm_". It avoids the reserved word problem and you can tell which table the field is from.
The judges table I might name "Judge_Name_Def" and the prefix would be "jnd_".
Linking tables would be "People_Cases_lnk" (lnk = Link). the prefix would be "pcl_". OK enough of that....

The main thing is to find a naming convention you like and try to stay with it.


As far as your structure, I think it looks pretty good. I see you have made more mods... you'll probable make a few more


So I have these comments:

In table "CaseActions" - "Date" is a reserved word. It also is not very descriptive. "Date" of what?
In tables "Cases", "CaseActions" and "People", I would think about having separate tables for the Notes. Would you ever want to enter more than one note for a case, case action or for a person? And I would add a date field to know when the note was entered.
No spaces in names - excellent!

Congratulations..... you are well on your way!

[alcapps]

ssanfu is correct there are many opinions on best practices. Yes stick to a name convention if you can.

one practice I use is in the key field for a table. I simply use ID therefore you don't have to add _pk to an obvious key.
or s to end of your table names.

but the beauty is it is your database so please do as you wish.

I think as long as the job gets done and you get what you want great..

Good luck to you. I hope you get your project completed..

[Mersad]

Thanks again. In regards to the naming I have to say again that English is not the language in which the database is being created, so all the problems with reserved words and naming conventions you guys pointed out are non-existent in the original database.

I will however think about the notes and date suggestion you guys pointed out for the tables "Cases", "CaseActions" and "People".

If I have further questions with the forms, etc. I'll come back here, but so far you have helped me out a lot! Thank you.

[Mersad]

I need a quick help actually. What is the proper builder code for the following:

I have [FirstName], [LastName] and [Company]

I want to create a builder that combines the three without commas, that would be

[FirstName] & " " & [LastName] & " " & [Company]

This works fine.

But when there is no First Name and Last Name then I get two spaces before the Company name starts.

I want to get rid of the spaces in the cases where there is no first and last name. Is there a code to do this?

Thanks in advance.

[alcapps]

[FirstName] & " " & [LastName] & " " & [Company]

Trim([FirstName] & " " & [LastName] & " " & [Company])

Trim removes spaces... on the left of a string.

[Mersad]

[FirstName] & " " & [LastName] & " " & [Company]

Trim([FirstName] & " " & [LastName] & " " & [Company])

Thank you for the fast reply, but the code you provided does the same thing as before, by which I mean, when there is no first and last name entry, only a company, I get two spaces before the name of the company starts.

[alcapps]

Sorry use rtrim()

[alcapps]

Darn early. Ltrim()

[Mersad]

Darn early. Ltrim()

Hmm this is still not working for me, when I type it into the builder. I looked up some templates where Microsoft uses this and it's a much more complicated string... With lots of IIf and ifNull etc. Can't really figure it out

[alcapps]

firstname	Lastname	Company	x
joe	smith	acme plumbing	joe smith acme plumbing
jane	jackson	IBM	jane jackson IBM
jack		first union	jack first union
	Williams		Williams
		Zero	Zero
jelly			jelly

SELECT Table1.firstname, Table1.Lastname, Table1.Company, LTrim([Firstname] & " " & [Lastname] & " " & [Company]) AS x
FROM Table1;

not sure what you're trying to do.. but take a look at this..

[Mersad]

firstname	Lastname	Company	x
joe	smith	acme plumbing	joe smith acme plumbing
jane	jackson	IBM	jane jackson IBM
jack		first union	jack first union
	Williams		Williams
		Zero	Zero
jelly			jelly

SELECT Table1.firstname, Table1.Lastname, Table1.Company, LTrim([Firstname] & " " & [Lastname] & " " & [Company]) AS x
FROM Table1;

not sure what you're trying to do.. but take a look at this..

Sorry if this is a dumb question, but where exactly am I entering this string?

I go to the table in Design view, go to the calculated filed, and then to General > Expression

Open up the expression builder.

But it doesn't let me enter this whole expression you wrote.

[alcapps]

make a new query.
goto the the sql view
paste in the query
then switch to design mode and look at what happens.

[pscray]

Would anyone be interested in creating a database for me, I can send you what I started. If so, please let me know your fees. Please see my relationship needs. The relationships are literally hindering me. I would like the ability to calendar also but I can play with that at a later date. Thanks in advance.

[ssanfu]

@pscray

You have done what is termed "High jacking a thread". Because this thread is from Feb 2013, you will probably not get any responses.

You should start a new thread (your own thread).