Let me preface this by saying I am neither a programmer/designer nor a student looking for help on an assignment. I am an attorney working for a large law firm who occasionally likes to break away from the practice of law to learn new things. One of my colleagues, knowing my inclinations, asked me if I could "design a user-friendly multi-user database" that would allow his group to ditch the unwieldy excel spreadsheet they are currently using to track property tax appeals. I accepted the challenge, but am finding myself stumped at the normalization phase. The details are as follows:

Each "Client" has one or more "Matters" associated with it.
Each "Matter" has one or more "Parcels" associated with it.
Each "Parcel" has one or more "Years" associated with it.
Each "Appeal" is for a particular "Parcel" for a particular "Year" (which is related to a particular "Matter" that is related to a particular "Client").

Each category has a variety of other information associated with it (e.g. Client_Name, Matter_Attorney, Parcel_County, Year_AssessedValue, etc.).

For example, a particular Client could have two Matters, each Matter could have ten Parcels, and each Parcel could have 3 Years. This results in 60 distinct Appeals that need to be tracked.

The purpose of all of this is to be able to generate reports, by Year, by Parcel, by Matter and by Client not only for internal use (e.g., calendering purposes) but also to provide to the various clients.

I've spent a decent amount of time trying to educate myself about database design and normalization and have taken several stabs at designing the database, but none of them feel "right" to me, so I'm finally giving in and asking for help. I would appreciate any and all thoughts and suggestions.

Here is my most recent attempt:

let's start here -- your diagram has the crow's feet drawn correctly beween Client and Matter, however, the FK is in the wrong table

you repeat this error in other relationships, too

I've just got them flipped? So Client_Id should be a FK in Matter, Appeal_Id should be a FK in Client and Parcel, and Parcel_Id should be a FK in Year?

What about the 1 to 1 relationship between Parcel and Appeal? Should I be combining the Parcel and Appeal tables as the only real distinguishing characteristic the the differing years?

yup

yes, one-to-one relationships can always be collapsed into a single table

and as for Year, a better name should be chosen if it relates to a particular parcel

Okay, I've revised the erd. Do I need to keep the PKs in Appeal_Year and Matter? And by collapsing Parcel into Appeal am I losing the ability to run a report based solely on Parcel Number? (e.g., if I wanted to see all of the appeals in play for a particular Parcel?). I want to be able to run reports by Client, by Matter, by Parcel and by Year and also by some of the sub fields (e.g. Bill_P or Rev_A, Filing_Date, etc.).

I just want to make sure that the original structure is as flexible as possible.

Thanks for your help!

I don't think you need Appeal_ID in Client table, otherwise you'll get a circular dependency, - you can't enter an appeal without the client, and you can't enter a client without an appeal...

Ah, yes. Oops. I meant to put Client_Id as a FK in Appeals, not Appeals_Id as a FK in Client.

"all of the appeals for a particular parcel" suggests quite strongly that it isn't a 1-to-1 relationship at all

rather, parcel to appeal would be 1-to-many

I guess that is the issue I have most been struggling with conceptually. A Parcel may have multiple appeals, but each appeal would be for a particular year.

For example:

Client: 001, Matter: 001
Parcel #0001 (appealing 2007, 2008 & 2009)
Parcel #0002 (appealing 2007)
Parcel #0003 (appealing 2008 & 2009)

Client: 001, Matter: 002
Parcel #0004 (appealing 2007 & 2008)
Parcel #0005 (appealing 2009)
Parcel #0006 (appealing 2007, 2008 & 2009)

an so on. So,

Appeal 1 = Client 001, Matter 001, Parcel #0001, 2007
Appeal 2 = Client 001, Matter 001, Parcel #0001, 2008
Appeal 3 = Client 001, Matter 001, Parcel #0001, 2009
Appeal 4 = Client 001, Matter 001, Parcel #0002, 2007
. . .
Appeal 12 = Client 001, Matter 002, Parcel #006, 2009

So the only way to define a unique appeal is by virtue of a combination of the Parcel and the Year.

I'm sure there are multiple ways to structure this, I just want to make sure whichever way I end up with leaves me with some flexibility.

excellent illustration

it's a classic hierarchy, a tree, each level is a one-to-many expansion

client has zero to many matters, matter has zero to many parcels, parcel has zero to many appeals

so that much is clear, we know that appeal must carry a FK to parcel, and parcel must carry a FK to matter, and matter must carry a FK to client

simple structure, but the tough part comes next, deciding on what to use as keys

client number seems obvious, and you probably already have a numbering scheme in use

identification of the other entities beyond this would be guesswork on my part

Ack. This is what I was hoping the database-design hive mind could help me with. How exactly do you decide what to use as keys? I don't need to tie this database to anything currently existing, so I'm not married to, for example, the current client number (although I may want to store it in a field). Can't I just use some sort of incrementally generated # for all of the PKs? or am I misunderstanding what you are saying?

yes you can, and no you aren't

data modellers will tell you to look for a natural key and don't stop until you find one

database administrators will tell you to use a surrogate key (e.g. autoincrement) in all cases

i'm somewhere in the middle

Okay, third try's the charm. I added types as well (I was thinking of using OpenOffice Base). Thoughts?

i don't see any types

and your keys make me squeamish

it's one thing to make a choice between a natural key and a surrogate key, but it's another matter (no pun intended) to parcel them both (no pun intended) into a table

can i appeal?

Maybe I'm using the nomenclature wrong, but I was under the impression that int and varchar and the like were types (at least that's what Base calls them).

And I'm not sure what you are getting at about parceling both natural and surrogate keys into the table. I thought I was using all surrogate keys (all PKs are auto-increments), which one is a natural key?

I suppose I could try using natural keys- fields like Client_Number and Parcel_Number would be unique, but I wouldn't be able to uniquely identify Matter or Appeal except by reference to Client and Parcel (e.g., Matter_Number, which I just noticed that I forgot to put in the table, is a four digit number that would not be, in and of itself, unique (like 0001) and I'm not seeing a natural key in Appeal). As all of the tables, except for Client, have a foreign key, don't I need a primary key in each?