Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jan 2011
    Posts
    14

    (Yet Another) Database Design Help Request

    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:
    Attached Thumbnails Attached Thumbnails erd.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's start here --
    Each "Client" has one or more "Matters" associated with it.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by r937 View Post
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by etonblue View Post
    I've just got them flipped?
    yup

    Quote Originally Posted by etonblue View Post
    What about the 1 to 1 relationship between Parcel and Appeal?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2011
    Posts
    14
    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!
    Attached Thumbnails Attached Thumbnails erd.JPG  

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by rdjabarov View Post
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by etonblue View Post
    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?).
    "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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by r937 View Post
    "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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by r937 View Post

    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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by etonblue View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2011
    Posts
    14
    Okay, third try's the charm. I added types as well (I was thinking of using OpenOffice Base). Thoughts?
    Attached Thumbnails Attached Thumbnails PTD_erd3.jpg  

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by etonblue View Post
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2011
    Posts
    14
    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?

Posting Permissions

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