Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Model question and advice please

    I have either had a major revelation with modeling or I'm really really lost. I have a current model that I have been working on in my spare time consisting of about 45 tables.

    I have a single 15" monitor that I am working on to view all of these tables and it dawns on me that I am really in need of a huge flat screen or something. What do you guys use when you have a lot of tables to model?

    I started looking at some of these tables and their relationships and it is really becoming unwieldly. I noticed where I completely forgot to relate 3 major tables to a parent table.

    My question is:

    I have tables that will contain data on several different types of people such as employees, customers, etc. Since they are all people and have a lot of the same attributes, *why* can't I use a single table and call it "players" or "people" and have a lookup table indicating their role in this db?

    As far as I am concerned, all of these people have many of the same attributes with the exception of the role they play (Hence players). I'd just like to lump them into one table. It seems it would save me a lot of different relationships.

    I could use other distinguishable attributes not directly related to "players" or "people" (Whatever you want to call it) and place those attributes into other tables with the PK from the players table.

    I am thinking like superclass/subclass. What are your thoughts on this. Any insight is well appriciated. Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fjm1967
    What do you guys use when you have a lot of tables to model?
    Same as when I don't have many - paper & pencil. If I have lots I might make use of sticky tape.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fjm1967
    I am thinking like superclass/subclass. What are your thoughts on this. Any insight is well appriciated. Thanks!
    The thread "next" to this one is on the same theme and discussed in reasonable detail. Have you read it?:
    http://www.dbforums.com/showthread.php?t=1618800
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by pootle flump
    The thread "next" to this one is on the same theme and discussed in reasonable detail. Have you read it?:
    http://www.dbforums.com/showthread.php?t=1618800
    Pootle, yes, I read it yesterday. There are a lot of opinions in that thread and I'm not so sure that he was asking the same thing I am; or at least that's not what I am getting out of it, although it is similar.

    Why should I have seperate addresses and phone numbers for example stored in seperate tables all over the place? Is it incorrect to have one table for all addresses, one table for all phone numbers, etc???

    Pat posted in that thread the following:
    Quote Originally Posted by Pat Phelan
    Having a "parent" table like inventory, with many potential "child" tables (workstation, server, printer, etc) is an advanced, but fairly common normalization technique called "entity decomposition" in Data Modeling. Is is directly comparable to the technique known as "sub-classing" in Object Oriented Design.

    What you are doing is both possible and more importantly it is correct!
    It seems that this is what I am trying to accomplish. No?
    Last edited by Frunkie; 06-03-07 at 06:10.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    Is it incorrect to have one table for all addresses, one table for all phone numbers, etc???
    no it isn't

    except phone numbers are usually best handled as totally dependent attributes, rather than independent entitites

    the only person who might care about a phone number as a phone number, independent of whose number it is, is the phone company

    granted, if you have a many-to-many relationship, where the phone number is shared amongst several owners, then you might want to set up a many-to-many relationship table to track it -- i wouldn't, myself, i would simply have the phone number be a dependent attribute, and allow repetition

    the same rationale can be applied to addresses, you could simply repeat an address if it is shared, although a separate table makes more sense because of the simplicity of being able to refer to a single address, which consists of multiple values (number, street, city, lat and long, etc.), using a single key value
    Last edited by r937; 06-03-07 at 08:09.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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