Results 1 to 12 of 12

Thread: ERD Critique

  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    ERD Critique

    This ERD is what we were working on yesterday and I have pretty much completed the basics. One thing that I forgot to include in my example yesterday was an "account" table. I see that account should also be a superclass.

    The account table will store the company information. A company will have a phone and email which I also have on the "person" side.

    Should I superclass the phone and email tables also or just create an account_phone and account_email table?

    I can see that the address is repeated in the person and account tables and was wondering if that should also be superclassed.

    Thanks,

    Frank
    Attached Thumbnails Attached Thumbnails export.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    frank, step back from the detail, you cannot see the forest for the trees

    you cannot "superclass" the phone and email tables

    regarding the supertype/subtype structure, the way to think about it is that each subtype is a particular kind of a supertype

    so in the person-employee-complainant situation, an employee is a type of person, and a complainant is a type of person

    to avoid three more days of back and forth trying to hammer this idea home, i'm going to jump ahead and suggest that your supertype should be called "party" and that it should have two subtypes: companies and people

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    regarding your city_state and zip tables, just remove them, okay?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    regarding the supertype/subtype structure, the way to think about it is that each subtype is a particular kind of a supertype
    Ok, I understand that when I think about it in those terms and you were right, I could not see the forest for the trees. I kind of got into a mode where I was just looking to have 1 theme per table I guess.

    Quote Originally Posted by r937
    i'm going to jump ahead and suggest that your supertype should be called "party" and that it should have two subtypes: companies and people
    Ok. Thanks Rudy. I really appriciate the help and I will remove the city_state and zip tables.

    **scratches head and whispers** were they modeled wrong?
    Last edited by Frunkie; 07-03-08 at 09:46.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    ... and I will remove the city_state and zip tables.

    **scratches head and whispers** were they modeled wrong?
    if i had to answer yes or no, i would have to say yes, they were modelled wrong

    you do not care about them as entities, they are just attributes of an address

    remember the surname table -- you don't automatically create a surname table just because it's possible for two Smiths to show up in your database!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    remember the surname table -- you don't automatically create a surname table just because it's possible for two Smiths to show up in your database!!
    Now that statement really hits home with me. I understand. I also understand why my idea of superclassing the email and phone tables wouldn't work.

    Example: Lifeform would be a superclass to person, mammal, etc. A vehicle is superclass to color, engine, wheels, etc. See? I got it...



    Thanks Rudy.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    Example: Lifeform would be a superclass to person, mammal, etc. A vehicle is superclass to color, engine, wheels, etc. See? I got it...
    yes you do, more or less

    you keep using the word superclass, which might have some meaning i'm not familiar with

    (the only thing i know about OO is that it's pronounced "uh oh")

    but as for supertype/subtype, remember, the trick is whether you can construct a meaningful sentence using the verb is

    so a person is a mammal, and a mammal is a lifeform

    but a colour is not a vehicle, an engine is not a vehicle

    those relationships are typically called a bill of materials or parts explosion (both of which terms you could google)

    and interestingly enough, they are implemented in the same table structures

    but they are not supertype/subtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    you keep using the word superclass, which might have some meaning i'm not familiar with
    Its possible that I am talking about something different than Supertype and Subtype.

    I have a book with all of 3 total pages that describes an "EER" diagram referencing Superclass and Subclass with disjoint and nondisjoint type constraints. I will have to do a little research to understand the differences.
    Quote Originally Posted by r937
    but as for supertype/subtype, remember, the trick is whether you can construct a meaningful sentence using the verb isso a person is a mammal, and a mammal is a lifeform
    The verb is the key then. I can see that it "flows" when you read it. That seems easy enough.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    That seems easy enough.
    it is easy, and not just when you read it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    ... referencing Superclass and Subclass with disjoint and nondisjoint type constraints. I will have to do a little research to understand the differences.
    disjoint: a person can only be an employee or a complainant, not both

    non-disjoint: a person can be both

    see how easy this is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Yes... I see. It really is easy. Here look..

    Supertype Car
    Subtype Convertable
    Subtype Wagon
    Subtype Compact
    Subtype Sedan

    Then we could have shared subclasses for each of the subtypes.

    Thanks for the clarification on the disjoint and nondisjoint. I was still reading and trying to understand from this book. Why can't people just put things in easy terms when trying to describe complex things? Its like they have to impress themselves or something and it only confuses the reader trying to gain the understanding.

    By the way Rudy, you were, once again correct. The terms are Supertype and Subtype and not superclass/subclass.

    Are you ever wrong about anything???


  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Color choices and styles for lederhosen!

    -PatP

Posting Permissions

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